From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Brian Hurt <bhurt@janestcapital.com>
Subject: Re: Some performance numbers, with thoughts
Date: Wed, 28 Jun 2006 08:41:41 -0700
Message-ID: <44A2A335.50601@cheapcomplexdevices.com> (raw)
In-Reply-To: <44973CC6.20309@janestcapital.com>
References: <44973CC6.20309@janestcapital.com>
Combining the "insert" statements in a big concatenated string
joined by semicolons - rather than sending each individually
can drastically speed up your inserts; making them much closer
to the speed of copy.
For example, instead of sending them separately, it's much faster
to send a single string like this
"insert into tbl (c1,c2) values (v1,v2);insert into tbl (c1,c2) values (v3,v4);..."
presumably due to the round-trip packets sending each insert takes.
Brian Hurt wrote:
>
> Inserts, 1,000 per transaction ~5,400 inserts/second
> Copy, 1,000 element blocks ~20,000 inserts/second
>
When I last measured it it was about a factor of 4 speedup
(3 seconds vs 0.7 seconds) by concatenating the inserts with
sample code shown her [1].
If the same ratio holds for your test case, these concatenated
inserts would be almost the exact same speed as a copy.
Ron M
[1] http://archives.postgresql.org/pgsql-performance/2005-09/msg00327.php
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: rm_pg@cheapcomplexdevices.com, bhurt@janestcapital.com
Subject: Re: Some performance numbers, with thoughts
In-Reply-To: <44A2A335.50601@cheapcomplexdevices.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