| 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: | Whole Thread | Raw Message | 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.
| 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 |