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. 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. HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Attachment:
signature.asc
Description: OpenPGP digital signature
