Re: COPY and Volatile default expressions

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.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services