Skip site navigation (1) Skip section navigation (2)

Re: set autovacuum=off

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: Peter van Hardenberg <pvh(at)heroku(dot)com>, Andy Colson <andy(at)squeakycode(dot)net>, Thom Brown <thom(at)linux(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 21:37:54
Message-ID: 4F46B1B2.3050105@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Alessandro GagliardiDate: 2012-02-23 22:30:37
Subject: Re: set autovacuum=off
Previous:From: Peter van HardenbergDate: 2012-02-23 21:11:14
Subject: Re: set autovacuum=off

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group