Skip site navigation (1) Skip section navigation (2)

Re: Inefficient bytea escaping?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Inefficient bytea escaping?
Date: 2006-05-26 17:30:23
Message-ID: 27343.1148664623@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2006-05-26 18:04:51
Subject: Re: Updatable views/with check option parsing
Previous:From: Martijn van OosterhoutDate: 2006-05-26 17:18:19
Subject: Re: LIKE, leading percent, bind parameters and indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group