Re: bytea vs. pg_dump

--On Montag, August 03, 2009 15:11:08 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> I'm starting to look at this patch. I observe that it's setting the
> default output format to HEX. If changing the default behavior was
> agreed to, or even discussed, I do not remember where. Shouldn't the
> default stay the same?

I would prefer it to be the default at least for pg_dump, if we can get
some significant performance improvement for both, dump and restore from
it. However, here are some current performance numbers (taken from today,
since yesterday i had some trouble to get on the machine):

I did some restore testing based on the following flow:

BEGIN;
TRUNCATE ... ;
COPY testtable FROM ... ;
ROLLBACK;

with bytea_output = 'escape' i get

Time: 1478801,770 ms

where bytea_output = 'hex' gives:

Time: 1448871,566 ms

So 'hex' is slightly faster on this machine, but not in the numbers i would
have expected. The hex-based restore gives the following profile:

Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls s/call s/call name
37.81 157.22 157.22 97847 0.00 0.00 pglz_compress
20.25 241.43 84.21 141398 0.00 0.00 CopyReadLine
14.44 301.48 60.05 3605691992 0.00 0.00 get_hex
8.29 335.96 34.48 141397 0.00 0.00 hex_decode
7.99 369.20 33.24 1 33.24 398.14 DoCopy
3.95 385.63 16.43 esc_enc_len
0.71 388.58 2.95 137268286 0.00 0.00 _bt_compare
0.54 390.81 2.23 7209863 0.00 0.00 XLogInsert
0.48 392.81 2.00 49329221 0.00 0.00
hash_search_with_hash_value
0.43 394.59 1.78 91132579 0.00 0.00 LWLockAcquire
0.42 396.34 1.75 92250421 0.00 0.00 LWLockRelease
0.42 398.08 1.75 30477526 0.00 0.00 ReadBuffer_common
0.20 398.93 0.85 28686690 0.00 0.00 PinBuffer
0.18 399.67 0.74 21541372 0.00 0.00 _bt_binsrch
0.16 400.34 0.67 39278753 0.00 0.00 AllocSetAlloc

--
Thanks

Bernd