Re: generic copy options

On Thu, 17 Sep 2009, Dan Colish wrote:

> - Performance appears to be the same although I don't have a good way for
> testing this at the moment

Here's what I do to generate simple COPY performance test cases:

CREATE TABLE t (i integer);
INSERT INTO t SELECT x FROM generate_series(1,100000) AS x;
\timing
COPY t TO '/some/file' WITH [options];
BEGIN;
TRUNCATE TABLE t;
COPY t FROM '/some/file' WITH [options];
COMMIT;

You can adjust the size of the generated table based on whether you want
to minimize (small number) or maximize (big number) the impact of the
setup overhead relative to actual processing time. Big numbers make sense
if there's a per-row change, small ones if it's mainly COPY setup that's
been changed if you want a small bit of data to test against.

An example with one column in it is a good test case for seeing whether
per-row impact has gone up. You'd want something with a wider row for
other types of performance tests.

The reason for the BEGIN/COMMIT there is that form utilizes an
optimization that lowers WAL volume when doing the COPY insertion, which
makes it more likely you'll be testing performance of the right thing.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD