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.
--Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services