Re: Inefficient bytea escaping?

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)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