Re: bytea vs. pg_dump

--On Samstag, Juli 11, 2009 13:40:44 +0300 Peter Eisentraut
<peter_e(at)gmx(dot)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