Re: Inefficient bytea escaping?

[Andreas Pflug <pgadmin(at)pse-consulting(dot)de>]

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