Way to determine index bloat stats?

From: Jeff Amiel <jamiel(at)istreamimaging(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Way to determine index bloat stats?
Date: 2007-07-05 17:43:46
Message-ID: 468D2DD2.3050200@istreamimaging.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A.M. 2007-07-05 17:43:59 Re: [pgsql-general] In memory tables/databases
Previous Message Kenji Morishige 2007-07-05 17:39:31 simple query question to use with DBI selectall_hashref