Re: Reindex doesn't eliminate bloat

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(dot)fsf%40news-spur(dot)riddles(dot)org(dot)uk#87oa4xmss7(dot)fsf(at)news-spur(dot)riddles(dot)org(dot)uk

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