Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 3D7CC1EB83EA
	for <pgsql-performance@postgresql.org>;
	Thu, 23 Feb 2012 17:38:11 -0400 (AST)
Received: from cerberus.pinpointresearch.com ([66.7.238.130]
	helo=polaris.pinpointresearch.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scrawford@pinpointresearch.com>) id 1S0gMb-0005Ok-Iw
	for pgsql-performance@postgresql.org; Thu, 23 Feb 2012 21:38:10 +0000
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id 49E9DE00EC83;
	Thu, 23 Feb 2012 13:37:56 -0800 (PST)
Message-ID: <4F46B1B2.3050105@pinpointresearch.com>
Date: Thu, 23 Feb 2012 13:37:54 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.26) Gecko/20120131 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: Alessandro Gagliardi <alessandro@path.com>
CC: Peter van Hardenberg <pvh@heroku.com>, Andy Colson <andy@squeakycode.net>,
	Thom Brown <thom@linux.com>, pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
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>
In-Reply-To: 
 <CAAB3BBJ9_4g3pk_M9DRcO_zS8_DHx0eZDSRgAZmXJ4qz9e5fXw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201202/189
X-Sequence-Number: 46361

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