On Mon, Apr 15, 2013 at 06:30:55PM +0100, Simon Riggs wrote: > On 15 April 2013 17:04, Simon Riggs <si...@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 <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers