Costin Manda wrote:
Some more info please: 1. This is this one INSERT statement per transaction, yes? If that fails, you do an UPDATE
correct.
2. Are there any foreign-keys the insert will be checking? 3. What indexes are there on the main table/foreign-key-related tables?
this is the table, the only restriction at the insert is the logid which must be unique.
Table "public.pgconnectionlog" Column | Type | Modifiers ----------------+-----------------------+----------- logid | integer | not null username | character varying(20) | logtime | integer | connecttime | integer | disconnecttime | integer | usedcredit | double precision | usedtime | integer | phonenum | character varying(30) | prephonenum | character varying(20) | pricelistname | character varying(30) | precode | character varying(20) | effectivetime | integer | callerid | character varying(30) | serialnumber | character varying(30) | prefix | character varying(20) | tara | character varying | Indexes: "pgconnectionlog_pkey" PRIMARY KEY, btree (logid) "connecttime_index" btree (connecttime) "disconnecttime_index" btree (disconnecttime) "logtime_index" btree (logtime) "prefix_index" btree (prefix) "tara_index" btree (tara) "username_index" btree (username)
Hmm - nothing unusual there. I'd be suspicious of a problem with the indexes, except you say reindexing has no effect.
Whatever the answers to these questions, perhaps look into loading your data into a temporary table, inserting any rows without matching primary keys and then deleting those and updating what's left.
You think this will be faster? It does make sense. Anyway, the problem is not optimising the script, is the speed change , dramatic I would say.
Could you monitor what's happening while this slows down. In particular, could you:
1. Run "vmstat 10" so we can see memory/cpu/disk usage while this is happening.
2. See what's happening in pg_xlog - are you creating/cycling through a lot of transaction-log files?
3. Keep an eye on the logs - are there any warnings there?
If you vacuum full, it's worth adding "verbose" to the that too, to see what it's doing.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
