Re: Column storage positions

Just as my 2 cents to the proposed idea.
I want to demonstrate that the proposed idea is very relevant for the
performance.

I recently did an migration from PG 8.1 to PG 8.2. During that time I was
dumping the 2TB database with several very wide tables (having ~ 200
columns). And I saw that on my pretty powerful server with 8Gb
RAM, Itanium2 procesor,large RAID which can do I/O at 100Mb/sec the
performance of pg_dump was CPU limited, and the read speed of the tables
was 1-1.5mb/sec (leading to 2 week dumping time).

I was very surprised by these times, and profiled postgres to check the
reason of that:
here is the top of gprof:
% cumulative self self total
time seconds seconds calls s/call s/call name
60.72 13.52 13.52 6769826 0.00 0.00 nocachegetattr
10.58 15.88 2.36 9035566 0.00 0.00 CopyAttributeOutText
7.22 17.49 1.61 65009457 0.00 0.00 CopySendData
6.34 18.90 1.41 1 1.41 22.21 CopyTo

So the main slow-down of the process was all this code recomputing the
boundaries of the columns.... I checked that by removing one tiny varchar
column and COALESCING all NULLs, and after that the performance of
pg_dumping increased by more than a factor of 2!

I should have reported that experience earlier... but I hope that my
observations can be useful in the context of the Phil's idea.

regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math(at)sai(dot)msu(dot)ru