On 02/23/2012 01:07 PM, Alessandro Gagliardi wrote:

The second one (a bunch of insert statements within a single connection). As I mentioned above, I was going to try the temp table thing, but that wasn't fast enough. COPY might be my next attempt.
insert into...;
insert into...;
insert into...;
... is really (ignoring statement preparation time):
begin;
insert into...;
commit;
begin;
insert into...;
commit;
begin;
insert into...;
commit;

It's possible that you might get a nice boost by wrapping the inserts into a transaction:
begin;
insert into...;
insert into...;
insert into...;
...
commit;

This only requires all that disk-intensive stuff that protects your data once at the end instead of 1000 times for you batch of 1000.

COPY is even better. I just ran a quick test by restoring a table on my desktop hacking db (untuned, few years old PC, single SATA disk, modest RAM and lots of resource competition). The 22+ million rows restored in 282 seconds which is a rate somewhat north of 78,000 records/second or about 0.13ms/record.

You may want to eliminate that trigger, which only seems to exist to silence errors from uniqueness violations, and copy the incoming data into a temp table then move the data with a variant of: INSERT INTO main_table (SELECT ... FROM incoming_table WHERE NOT EXISTS ((SELECT 1 from main_table WHERE ...))

Cheers,
Steve

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to