Got a bit of a rude surprise when I rebuilt a slony subscriber node from
scratch and noticed the indexes for some tables using significantly less
space than on their master-node counterpart.
In once case, it was 2 gigabytes versus 9 gigabytes for the same table
indexs on the master node. I'm assuming that because of the specific
indexed fields and the various 'states' that they go through that we
ended up with many many sparsely populated pages of index data.
While I am not too concerned (took 3 years to get this 'fragmented' plus
the ability to do concurrent index creation in 8.2 may help), I am
interested in determining a way to see how 'sparsely' populated the
index file pages are at any particular point in time....
Is there some magic way of determining the number of bytes in an index
'row' and then comparing the size on disk of the index file?
Also...would another index type (hash?) might be better suited for some
of these indexes (they are all currently btree)......and would that
reduce the bloat?
(I wish I had tracked which specific indexes were 'bloated' so I could
analyze the usage pattern of the fields used.....)
Any help would be appreciated.