Re: bytea vs. pg_dump

--On Montag, August 03, 2009 15:11:08 -0400 Tom Lane <tgl@sss.pgh.pa.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     totaltime   seconds   seconds    calls
s/call   s/call  name37.81    157.22   157.22    97847     0.00     0.00  pglz_compress20.25    241.43    84.21
141398    0.00     0.00  CopyReadLine14.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