Re: COPY and Volatile default expressions - Mailing list pgsql-hackers

From David Fetter
Subject Re: COPY and Volatile default expressions
Date
Msg-id 20130415174127.GD19333@fetter.org
Whole thread Raw
In response to Re: COPY and Volatile default expressions  (Simon Riggs <simon@2ndQuadrant.com>)
Responses Re: COPY and Volatile default expressions
List pgsql-hackers
On Mon, Apr 15, 2013 at 06:30:55PM +0100, Simon Riggs wrote:
> On 15 April 2013 17:04, Simon Riggs <simon@2ndquadrant.com> wrote:
> 
> > I will implement as a kluge, test and report the results.
> 
> Test is COPY 1 million rows on a table with 2 columns, both bigint.
> Verified no checkpoints triggered during load.
> No other work active on database, tests condicted on laptop
> Autovacuum disabled.
> Results from multiple runs, outliers excluded, rough averages
> 
> HEAD
> COPY, with sequence ~5500ms
> COPY, with sequence, cached ~5000ms
> COPY, no sequence ~1600ms
> 
> PATCH to allow sequences to use multi-insert optimisation (1 line change)
> COPY, with sequence ~1850ms
> COPY, with sequence, cached ~1750ms
> COPY, no sequence ~1600ms
> 
> This shows that
> * cacheing the sequence gives a useful improvement currently
> * use of multi-insert optimisaton is very important
> 
> Proposals
> * set CACHE 100 on automatically created SERIAL sequences
> * allow some way to use multi-insert optimisation when default expr is
> next_val on a sequence
> 
> Tests performed without indexes since this is another area of known
> performance issues that I hope to cover later. Zero indexes is not
> real, but we're trying to measure the effect and benefit of an
> isolated change, so in this case it is appropriate.

The difference between HEAD and patch in the "COPY, with sequence"
case is pretty remarkable.  What's the patch?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate



pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: COPY and Volatile default expressions
Next
From: Simon Riggs
Date:
Subject: Re: COPY and Volatile default expressions