From: Steve Crawford <scrawford@pinpointresearch.com>
To: Alessandro Gagliardi <alessandro@path.com>
Cc: Peter van Hardenberg <pvh@heroku.com>
Cc: Andy Colson <andy@squeakycode.net>
Cc: Thom Brown <thom@linux.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
Date: Thu, 23 Feb 2012 13:37:54 -0800
Message-ID: <4F46B1B2.3050105@pinpointresearch.com> (raw)
In-Reply-To: <CAAB3BBJ9_4g3pk_M9DRcO_zS8_DHx0eZDSRgAZmXJ4qz9e5fXw@mail.gmail.com>
References: <CAAB3BBLsZ94tTzaiTZrdpK+2m1waj8=VL31aPvOGFJ7oAGCHiQ@mail.gmail.com>
<CAA-aLv4KL4atuaqJFi3BBWiRsWiJOVQZMk65JYw=H8ezZV6O8g@mail.gmail.com>
<4F4658CB.3030505@squeakycode.net>
<CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com>
<4F467EDF.5090708@pinpointresearch.com>
<CAAB3BBKU4f3QqNMmjYeuZFkOhp6iP+2aMhLWODQBEMQt-+yCnA@mail.gmail.com>
<4F4692FF.3080703@pinpointresearch.com>
<CAAB3BBJ9_4g3pk_M9DRcO_zS8_DHx0eZDSRgAZmXJ4qz9e5fXw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, alessandro@path.com, pvh@heroku.com, andy@squeakycode.net, thom@linux.com
Subject: Re: set autovacuum=off
In-Reply-To: <4F46B1B2.3050105@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox