On 03/13/2018 06:10 PM, Joe Conway wrote:
On 03/12/2018 09:16 PM, Ron Johnson wrote:
On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
Those queries from wiki for table and index bloat estimation are for
estimation only. In many cases they show very wrong results. Better
(yet not ideal) approach is using pgstattuple extension (though I'm
not sure it existed back in 2009).
Can you provide table and index definition and, if you can, some
sample data?
Sadly, no sample data. (It's all PCI controlled.)
Index idx_item_mapping_rp7_y2016m03itemmapping_custom_userfield_801 has
40% bloat.
Assuming the data in the indexed column(s) is not highly correlated with
the physical table order (i.e. it is roughly random), about 50% density
is theoretically expected.
What does physical table order have to do with b-tree organization,
especially in a freshly reindexed table using the default 90% fill factor?
In fact, in some empirical testing, I have
seen a long term steady state value of closer to 44% if I remember
correctly (but perhaps that was related to the way I was testing). For a
discussion on why this is the case, see for example:
https://www.postgresql.org/message-id/flat/87oa4xmss7.fsf%40news-spur.riddles.org.uk#87oa4xmss7.fsf@xxxxxxxxxxxxxxxxxxxxxxxx
So what is being reported at 40% bloat is probably actually not really
bloat.
--
Angular momentum makes the world go 'round.
