Re: bytea vs. pg_dump

--On Samstag, Juli 11, 2009 13:40:44 +0300 Peter Eisentraut
<peter_e@gmx.net> wrote:

> OK, here is an updated patch.  It has the setting as enum, completed
> documentation, and libpq support.  I'll add it to the commit fest in the
> hope  that someone else can look it over in detail.

I've started looking at this and did some profiling with large bytea data
again. For those interested, here are the numbers:

Dumping with bytea_output=hex (COPY to file):

real    20m38.699s
user    0m11.265s
sys     1m0.560s

Dumping with bytea_output=escape (COPY to file):

real    39m52.399s
user    0m22.085s
sys     1m50.131s

So the time needed dropped about 50%. The dump file dropped from around 48
GB to 28 GB with the new format. I have some profiler data for this, but
the restore seems much more interesting: the time to restore for both
formats is quite the same:

Restore bytea_output=hex

real    32m11.028s
user    0m0.000s
sys     0m0.008s

Restore bytea_output=escape

real    31m35.378s
user    0m0.000s
sys     0m0.000s

The profile for restoring the hex format looks like this:

  %   cumulative   self              self     total
 time   seconds   seconds    calls   s/call   s/call  name
 34.54    156.79   156.79    97836     0.00     0.00  pglz_compress
 18.64    241.38    84.59   141374     0.00     0.00  CopyReadLine
 12.83    299.62    58.24 3604740854     0.00     0.00  get_hex
  8.44    337.95    38.33 14257432     0.00     0.00  XLogInsert
  7.39    371.48    33.53   141373     0.00     0.00  hex_decode
  7.23    404.31    32.83        1    32.83   436.67  DoCopy
  3.48    420.12    15.81                             esc_enc_len
  0.61    422.89     2.77 134943749     0.00     0.00  _bt_compare
  0.54    425.36     2.47 33682172     0.00     0.00  ReadBuffer_common
  0.54    427.83     2.47 52166324     0.00     0.00
hash_search_with_hash_value
  0.45    429.89     2.06 104798203     0.00     0.00  LWLockAcquire
  0.36    431.53     1.64 105234314     0.00     0.00  LWLockRelease

I've attached a slightly edited patch which fixes a compiler warning in
encode.c, too.

--
  Thanks

                    Bernd