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

Re: Some performance numbers, with thoughts

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Brian Hurt <bhurt(at)janestcapital(dot)com>
Subject: Re: Some performance numbers, with thoughts
Date: 2006-06-28 15:41:41
Message-ID: 44A2A335.50601@cheapcomplexdevices.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: satishchandra999Date: 2006-06-29 17:00:35
Subject: Calling a SP from Curosor loop
Previous:From: Jim C. NasbyDate: 2006-06-28 15:08:15
Subject: Re: Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)

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