To: David Newall <postgresql@davidnewall.com>
cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump far too slow 
In-reply-to: <4B9C97E1.40509@davidnewall.com> 
References: <4B9C97E1.40509@davidnewall.com>
Comments: In-reply-to David Newall <postgresql@davidnewall.com>
	message dated "Sun, 14 Mar 2010 18:31:37 +1030"
Date: Sun, 14 Mar 2010 16:21:51 -0400
Message-ID: <24696.1268598111@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

David Newall <postgresql@davidnewall.com> writes:
> [ very slow pg_dump of table with large bytea data ]

Did you look at "vmstat 1" output to see whether the system was under
any large I/O load?

Dumping large bytea data is known to be slow for a couple of reasons:

1. The traditional text output format for bytea is a bit poorly chosen.
It's not especially cheap to generate and it interacts very badly with
COPY processing, since it tends to contain lots of backslashes which
then have to be escaped by COPY.

2. Pulling the data from the out-of-line "toast" table can be expensive
if it ends up seeking all over the disk to do it.  This will show up as
a lot of seeking and I/O wait, rather than CPU expense.

Since you mention having recently recopied the table into a new table,
I would guess that the toast table is reasonably well-ordered and so
effect #2 shouldn't be a big issue.  But it's a good idea to check.

PG 9.0 is changing the default bytea output format to hex, in part
to solve problem #1.  That doesn't help you in an 8.3 installation
of course.  If you're desperate you could consider excluding this
table from your pg_dumps and backing it up separately via COPY BINARY.
The PITA factor of that might be more than you can stand though.
Offhand I can't think of any other way to ameliorate the problem
in 8.3.

			regards, tom lane