Re: COPY and Volatile default expressions

On Mon, Apr 15, 2013 at 06:30:55PM +0100, Simon Riggs wrote:
> On 15 April 2013 17:04, Simon Riggs <simon(at)2ndquadrant(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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