Andreas Pflug <pgadmin@pse-consulting.de> writes:
> When dumping the table with psql \copy (non-binary), the resulting file
> would be 6.6GB of size, taking about 5.5 minutes. Using psql \copy WITH
> BINARY (modified psql as posted to -patches), the time was cut down to
> 21-22 seconds (filesize 1.4GB as expected), which is near the physical
> throughput of the target disk. If server based COPY to file is used, The
> same factor 12 can be observed, CPU is up to 100 % (single P4 3GHz 2MB
> Cache HT disabled, 1GB main mem).
This is with an 8.0.x server, right?
Testing a similar case with CVS HEAD, I see about a 5x speed difference,
which is right in line with the difference in the physical amount of
data written. (I was testing a case where all the bytes were emitted as
'\nnn', so it's the worst case.) oprofile says the time is being spent
in CopyAttributeOutText() and fwrite(). So I don't think there's
anything to be optimized here, as far as bytea goes: its binary
representation is just inherently a lot smaller.
Looking at CopySendData, I wonder whether any traction could be gained
by trying not to call fwrite() once per character. I'm not sure how
much per-call overhead there is in that function. We've done a lot of
work trying to optimize the COPY IN path since 8.0, but nothing much
on COPY OUT ...
regards, tom lane