Re: Inefficient bytea escaping?

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> 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 6.6 GB from table, std:
> 8.0 8.1 8.2dev
> 375s 362s 290s (second:283s)

Hmph. There's something strange going on on your platform (what is it
anyway?) Using CVS HEAD on Fedora Core 4 x86_64, I get

bytea=# copy t to '/home/tgl/t.out';
COPY 1024
Time: 273325.666 ms
bytea=# copy binary t to '/home/tgl/t.outb';
COPY 1024
Time: 62113.355 ms

Seems \timing doesn't work on \copy (annoying), so

$ time psql -c "\\copy t to '/home/tgl/t.out2'" bytea

real 3m47.507s
user 0m3.700s
sys 0m36.406s
$ ls -l t.*
-rw-r--r-- 1 tgl tgl 5120001024 May 26 12:58 t.out
-rw-rw-r-- 1 tgl tgl 5120001024 May 26 13:14 t.out2
-rw-r--r-- 1 tgl tgl 1024006165 May 26 13:00 t.outb
$

This test case is 1024 rows each containing a 1000000-byte bytea, stored
EXTERNAL (no on-disk compression), all bytes chosen to need expansion to
\nnn form. So the ratio in runtimes is in keeping with the amount of
data sent. It's interesting (and surprising) that the runtime is
actually less for psql \copy than for server COPY. This is a dual Xeon
machine, maybe the frontend copy provides more scope to use both CPUs?

It would be interesting to see what's happening on your machine with
oprofile or equivalent.

I can't test psql binary \copy just yet, but will look at applying your
recent patch so that case can be checked.

regards, tom lane