Re: Inefficient bytea escaping?

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> 
>>Tom Lane wrote:
>>
>>>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 ...
> 
> 
>>Hm, I'll see whether I can manage to check CVS head too, and see what's 
>>happening, not a production alternative though.
> 
> 
> OK, make sure you get the copy.c version I just committed ...

Here are the results, with the copy patch:

psql \copy 1.4 GB from table, binary:
8.0    8.1    8.2dev
36s    34s    36s

psql \copy 1.4 GB to table, binary:
8.0    8.1    8.2dev
106s    95s    98s

psql \copy 6.6 GB from table, std:
8.0    8.1    8.2dev
375s    362s    290s (second:283s)

psql \copy 6.6 GB to table, std:
8.0    8.1    8.2dev
511s    230s    238s

INSERT INTO foo SELECT * FROM bar
8.0    8.1    8.2dev
75s    75s    75s

So obviously text COPY is enhanced by 20 % now, but it's still far from 
the expected throughput. The dump disk should be capable of 60MB/s, 
limiting text COPY to about 110 seconds, but the load process is CPU 
restricted at the moment.

For comparision purposes, I included the in-server copy benchmarks as 
well (bytea STORAGE EXTENDED; EXTERNAL won't make a noticable 
difference). This still seems slower than expected to me, since the 
table's on-disk footage is relatively small (138MB).

Regards,
Andreas