On that particular patch, as Robert mentioned, only the parsing has changed.
In the case of \copy, the parsing is much lighter than before in psql
(remains the same in the server). The bigger the COPY operation the less
you will see the impact of the parsing since it is done only once for
the entire operation.
Emmanuel
Dan Colish wrote:
On Thu, Sep 17, 2009 at 07:10:35PM -0400, Andrew Dunstan wrote:
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
If someone with a more significant setup can run tests that would ideal.
I only have my laptop which is a single disk and fairly underpowered.
That said, here are my results running the script above, it looks like
the pach improves performance. I would really interested to see results
on a larger data set and heavier iron.
--
--Dan
Without Patch:
CREATE TABLE
INSERT 0 100000
Timing is on.
COPY 100000
Time: 83.273 ms
BEGIN
Time: 0.412 ms
TRUNCATE TABLE
Time: 0.357 ms
COPY 100000
Time: 140.911 ms
COMMIT
Time: 4.909 ms
With Patch:
CREATE TABLE
INSERT 0 100000
Timing is on.
COPY 100000
Time: 80.205 ms
BEGIN
Time: 0.351 ms
TRUNCATE TABLE
Time: 0.346 ms
COPY 100000
Time: 124.303 ms
COMMIT
Time: 4.130 ms
--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
