Re: generic copy options

Greg Smith wrote:
> 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.
>
>

I usually prefer to test with a table that is more varied than anything
you can make with generate_series. When I tested my ragged copy patch
the other day I copied 1,000,000 rows out of a large table with a
mixture of dates, strings, numbers and nulls.

But then, it has a (tiny) per field overhead so I wanted to make sure
that was well represented in the test.

You are certainly right about wrapping it in begin/truncate/commit (and
when you do make sure that archiving is not on).

You probably want to make sure that the file is not on the same disk as
the database, to avoid disk contention. Or, better, make sure that it is
in OS file system cache, or on a RAM disk.

cheers

andrew