Re: Reindex doesn't eliminate bloat

[Ron Johnson]

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@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.

Re: Reindex doesn't eliminate bloat

[Joe Conway]

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@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



signature.asc
Description: OpenPGP digital signature

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson > wrote:


On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:

On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson > 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.
TAPd=# \d tms.item_mapping_rp7_y2013m03
   Table 
"tms.item_mapping_rp7_y2013m03"
 Column |Type | 
Modifiers  
+-+
 item_mapping_id| integer | not null default 
nextval('tms.item_mapping_item_mapping_id_seq'::regclass)
 item_seq_no| numeric(10,0)   | 
 account_no | character varying(255)  | 
 amount | numeric(21,2)   | 
 gross_amount   | numeric(21,2)   | 
 net_amount | numeric(21,2)   | 
 amount_3   | numeric(21,2)   | 
 custom_userfield_401   | character varying(255)  | 
 custom_userfield_402   | character varying(255)  | 
 custom_userfield_403   | character varying(255)  | 
 custom_userfield_404   | character varying(255)  | 
 custom_userfield_405   | character varying(255)  | 
 custom_userfield_416   | character varying(16)   | 
 custom_userfield_417   | character varying(16)   | 
 custom_userfield_418   | character varying(16)   | 
 custom_userfield_419   | character varying(16)   | 
 custom_userfield_420   | numeric(10,0)   | 
 custom_userfield_421   | numeric(10,0)   | 
 custom_userfield_422   | numeric(10,0)   | 
 custom_userfield_423   | numeric(21,2)   | 
 custom_userfield_424   | numeric(21,2)   | 
 custom_userfield_425   | numeric(21,2)   | 
 custom_userfield_426   | numeric(10,0)   | 
 custom_userfield_431   | character varying(16)   | 
 custom_userfield_432   | character varying(16)   | 
 custom_userfield_433   | character varying(16)   | 
 custom_userfield_434   | character varying(16)   | 
 custom_userfield_436   | character varying(16)   | 
 custom_userfield_437   | numeric(10,0)   | 
 custom_userfield_438   | numeric(10,0)   | 
 custom_userfield_439   | numeric(10,0)   | 
 custom_userfield_440   | numeric(10,0)   | 
 custom_userfield_441   | numeric(10,0)   | 
 custom_userfield_442   | numeric(10,0)   | 
 custom_userfield_443   | numeric(10,0)   | 
 custom_userfield_444   | character varying(1)| 
 custom_userfield_445   | character varying(1)| 
 custom_userfield_446   | character varying(1)| 
 custom_userfield_447   | character varying(1)| 
 custom_userfield_448   | character varying(1)| 
 custom_userfield_449   | character varying(1)| 
 custom_userfield_450   | character varying(1)| 
 custom_userfield_451   | character varying(1)| 
 custom_userfield_452   | character varying(1)| 
 custom_userfield_453   | numeric(21,2)   | 
 custom_userfield_454   | numeric(21,2)   | 
 custom_userfield_455   | numeric(21,2)   | 
 custom_userfield_456   | numeric(21,2)   | 
 custom_userfield_457   | numeric(21,2)   | 
 custom_userfield_458   | numeric(21,2)   | 
 custom_userfield_459   | numeric(21,2)   | 
 custom_userfield_460   | numeric(21,2)   | 
 cds_invoice_seq_number | numeric(10,0)   | 
 created_on | timestamp without time zone | not null
 created_by | integer | 
 modified_on| timestamp without time zone | 
 modified_by| integer | 
 type_id| numeric(8,0)

Re: Reindex doesn't eliminate bloat

[Nikolay Samokhvalov]

On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson  wrote:

> On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
>
> On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson 
> 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?

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

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.

Re: Reindex doesn't eliminate bloat

[Adrian Klaver]

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.kla...@aklaver.com

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson > 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.

Re: Reindex doesn't eliminate bloat

[Nikolay Samokhvalov]

On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson  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.

Reindex doesn't eliminate bloat

[Ron Johnson]

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.