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@gregsmith.com http://www.gregsmith.com Baltimore, MD