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. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers