On 6 February 2013 11:04, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:Have you tried using pg_filedump
(http://pgfoundry.org/frs/?group_id=1000541)
to dump a page or two of your table and figure
out what is where and where the space went?I haven't; I will do for interest's sake, thanks for the suggestion.
A simple hexdump output shows that I can see each row taking an extra 28 to 31 (depending on the number of bytes padding to align after the varchar) bytes inline, which is about we thought. There's also a variable amount of wasted space in each page where the next row won't fit in the page, varying between 0 and (I guess) the largest row size + 30.
pg_filedump -a 22212| grep -i 'Free space' | cut -c46- | perl -nle '$sum += $_ } END { print $sum'
gives us a total of the "free space" values for all blocks in the 22212 table at 1875964 bytes.
Given that there are 11367253 rows and we accept (being overly generous) an extra 35 bytes per row (379MB) plus 178MB real data plus just under 2MB free space plus 78494 block headers of 60 bytes each (ignoring the 4-bytes-per-row in the block header because we've already included that in the "35" bytes-per-row value) of 4MB gives a total of 563MB. I'm still 65MB short of the 618MB space taken.
Not that that's going to make any significant difference but I am now intrigued as to where it's gone. :)
Geoff
