From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Reindex doesn't eliminate bloat |
Date: | 2018-03-13 23:47:37 |
Message-ID: | 9b514d34-cb60-d231-1919-f191bbe9d51e@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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.
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-03-13 23:48:58 | Re: JDBC connectivity issue |
Previous Message | chris | 2018-03-13 23:46:20 | Re: JDBC connectivity issue |