Horton

Thread

  1. Reindex doesn't eliminate bloat

    Ron Johnson <ron.l.johnson@cox.net> — 2018-03-12T22:05:03Z

    v8.4.12

According to this (https://pastebin.com/TJB32n5M) query, which I thought I 
got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
indexes and their bloat is generated.

After reindexing a table with a large amount of reported bloat (column 
bloat_pct says 29%), re-running the query shows no change in the amount of 
bloat.  This is a historical table, and VACUUM VERBOSE shows that there's 
nothing to free up.

Is this something that I must live with, or am I misinterpreting the query?

Thanks,

-- 
Angular momentum makes the world go 'round.

  2. Re: Reindex doesn't eliminate bloat

    Nikolay Samokhvalov <samokhvalov@gmail.com> — 2018-03-12T22:20:55Z

    On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:

> v8.4.12
>

This is *very* old version, not supported by the community for many years.
Check https://www.postgresql.org/ to see currently supported versions.
You need to upgrade it.

  3. Re: Reindex doesn't eliminate bloat

    Ron Johnson <ron.l.johnson@cox.net> — 2018-03-12T22:28:19Z

    On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
> On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.johnson@cox.net 
> <mailto:ron.l.johnson@cox.net>> wrote:
>
>     v8.4.12
>
> This is *very* old version, not supported by the community for many years. 
> Check https://www.postgresql.org/ to seecurrentlysupported versions.
> You need to upgrade it.

Don't even think I'm in control of when -- or even if -- the customer 
decides to upgrade.

That being the case, do you have an answer to the question?


-- 
Angular momentum makes the world go 'round.

  4. Re: Reindex doesn't eliminate bloat

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-03-12T22:55:39Z

    On 03/12/2018 03:05 PM, Ron Johnson wrote:
> v8.4.12
> 
> According to this (https://pastebin.com/TJB32n5M) query, which I thought 
> I got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
> indexes and their bloat is generated.
> 
> After reindexing a table with a large amount of reported bloat (column 
> bloat_pct says 29%), re-running the query shows no change in the amount 

First I am not seeing a column bloat_pct in the query you linked to, so 
are you sure that is the actual query you used?

> of bloat.  This is a historical table, and VACUUM VERBOSE shows that 
> there's nothing to free up.
> 
> Is this something that I must live with, or am I misinterpreting the query?

Honestly I have not worked my way in depth through the query you show, 
though I did notice it uses pg_stats. What happens if run ANALYZE 
(https://www.postgresql.org/docs/8.4/static/sql-analyze.html) to update 
the stats?

> 
> Thanks,
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

  5. Re: Reindex doesn't eliminate bloat

    Ron Johnson <ron.l.johnson@cox.net> — 2018-03-12T23:00:29Z

    On 03/12/2018 05:55 PM, Adrian Klaver wrote:
> On 03/12/2018 03:05 PM, Ron Johnson wrote:
>> v8.4.12
>>
>> According to this (https://pastebin.com/TJB32n5M) query, which I thought 
>> I got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
>> indexes and their bloat is generated.
>>
>> After reindexing a table with a large amount of reported bloat (column 
>> bloat_pct says 29%), re-running the query shows no change in the amount 
>
> First I am not seeing a column bloat_pct in the query you linked to, so 
> are you sure that is the actual query you used?

Sorry.  bloat_pct is renamed bloat_ratio.

>
>> of bloat.  This is a historical table, and VACUUM VERBOSE shows that 
>> there's nothing to free up.
>>
>> Is this something that I must live with, or am I misinterpreting the query?
>
> Honestly I have not worked my way in depth through the query you show, 
> though I did notice it uses pg_stats. What happens if run ANALYZE 
> (https://www.postgresql.org/docs/8.4/static/sql-analyze.html) to update 
> the stats?


I did ANALYZE VERBOSE on the underlying table.  No change.


-- 
Angular momentum makes the world go 'round.

  6. Re: Reindex doesn't eliminate bloat

    Nikolay Samokhvalov <samokhvalov@gmail.com> — 2018-03-13T03:48:43Z

    On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:

> On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
>
> On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.johnson@cox.net>
> wrote:
>
>> v8.4.12
>>
>
> This is *very* old version, not supported by the community for many years.
> Check https://www.postgresql.org/ to see currently supported versions.
> You need to upgrade it.
>
>
> Don't even think I'm in control of when -- or even if -- the customer
> decides to upgrade.
>
> That being the case, do you have an answer to the question?
>

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?

  7. Re: Reindex doesn't eliminate bloat

    Ron Johnson <ron.l.johnson@cox.net> — 2018-03-13T04:16:58Z

    On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
> On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson <ron.l.johnson@cox.net 
> <mailto:ron.l.johnson@cox.net>> wrote:
>
>     On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
>>     On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.johnson@cox.net
>>     <mailto:ron.l.johnson@cox.net>> wrote:
>>
>>         v8.4.12
>>
>>     This is *very* old version, not supported by the community for many
>>     years. Check https://www.postgresql.org/ to seecurrentlysupported
>>     versions.
>>     You need to upgrade it.
>
>     Don't even think I'm in control of when -- or even if -- the customer
>     decides to upgrade.
>
>     That being the case, do you have an answer to the question?
>
>
> 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.

Thanks.

-- 
Angular momentum makes the world go 'round.

  8. Re: Reindex doesn't eliminate bloat

    Joe Conway <mail@joeconway.com> — 2018-03-13T23:10:18Z

    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@news-spur.riddles.org.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

  9. Re: Reindex doesn't eliminate bloat

    Ron Johnson <ron.l.johnson@cox.net> — 2018-03-13T23:47:37Z

    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@news-spur.riddles.org.uk
>
> So what is being reported at 40% bloat is probably actually not really
> bloat.


-- 
Angular momentum makes the world go 'round.