bloat_btree is incorrect with PG13

[mrechte]

It seems PG13 btree enhancements break the bloat_btree service: one gets negative figures on bloat. Actually columns est_page and est_pages_ff may bring higher figures than relpages !

[gleu]

Quick way to reproduce the issue on v13.0:

create table t2(id integer);
insert into t2 select 1 from generate_series(1, 1000000);
create index t2_id_deduplicate_on on t2(id);
create index t2_id_deduplicate_off on t2(id) with (deduplicate_items = OFF);
vacuum analyze t2;

Which gives this as a result of the bloat query:

┌─[ RECORD 1 ]─────┬───────────────────────┐
│ current_database │ postgres              │
│ schemaname       │ public                │
│ tblname          │ t2                    │
│ idxname          │ t2_id_deduplicate_off │
│ real_size        │ 22519808              │
│ extra_size       │ 22511616              │
│ extra_ratio      │ 99.96362313568571     │
│ fillfactor       │ 90                    │
│ bloat_size       │ 22511616              │
│ bloat_ratio      │ 99.96362313568571     │
│ is_na            │ f                     │
├─[ RECORD 2 ]─────┼───────────────────────┤
│ current_database │ postgres              │
│ schemaname       │ public                │
│ tblname          │ t2                    │
│ idxname          │ t2_id_deduplicate_on  │
│ real_size        │ 6955008               │
│ extra_size       │ 6946816               │
│ extra_ratio      │ 99.88221436984688     │
│ fillfactor       │ 90                    │
│ bloat_size       │ 6946816               │
│ bloat_ratio      │ 99.88221436984688     │
│ is_na            │ f                     │
└──────────────────┴───────────────────────┘

So, even with deduplicate at off, there's still a huge overestimation of the extra size and bloat.

For the record, this is what release 12 gives as a result:

┌─[ RECORD 1 ]─────┬────────────────────┐
│ current_database │ postgres           │
│ schemaname       │ public             │
│ tblname          │ t2                 │
│ idxname          │ t2_id              │
│ real_size        │ 22519808           │
│ extra_size       │ 2375680            │
│ extra_ratio      │ 10.54929065114587  │
│ fillfactor       │ 90                 │
│ bloat_size       │ 122880             │
│ bloat_ratio      │ 0.5456529647144416 │
│ is_na            │ f                  │
└──────────────────┴────────────────────┘

Which looks definitely better.

[ioguix]

Hi,

I spend some time on your demo. The behavior with t2_id_deduplicate_off was quite surprising. I did a report on pgsql-hackers. See: https://www.postgresql.org/message-id/flat/20201023174451.69e358f1%40firost

I'll try to find a solution for t2_id_deduplicate_on. Using n_distinct might be a solution. However, I'm a bit skeptical...

[ioguix]

Hi,

Just for the record, with correct reltuples in pg_class for t2_id_deduplicate_off, the boat query is correct:

-[ RECORD 1 ]------+-------------------
current_database   | postgres
schemaname         | public
tblname            | t
idxname            | t_id
real_size          | 22519808
extra_size         | 2375680
extra_ratio        | 10.54929065114587
fillfactor         | 90
bloat_size         | 122880
bloat_ratio        | 0.5456529647144416
is_na              | f

[ioguix]

Well,

After studying the deduplication mechanism, it seems it:

1. applies lazily during insert...(low dedup)
2. ...and aggressively during create index/reindex (high dedup)
3. do not apply on various types (non deterministic text, numeric, floats, jsonb, composite types)
4. do not apply on covering index

Even if we could produce a close-to-accurate statisticaly expected deduplicated btree size, the size difference between 1 and 2 can no be considered bloat. In extreme scenario, a close-to-none deduplicated index will just have the same size than build with deduplicate_items = OFF. Which is not bloat.

Considering 3 and 4, all these constraints could be integrated as condition in the query I suppose... but it will make it even more extremely hard to read and hacky.

Even the result of the query itself might become harder to read:

• should we add a boolean field to flag deduplicated index?
• should we add an deduplicated-expected size?
• should we change the threshold policy to apply on deduplicated but oversized index as well?

On deduplicated index with correct reltuples, the current query reports negative bloat...which is "normal" considering the index is smaller than expected:

current_database   | postgres
schemaname         | public
tblname            | t
idxname            | t_id_on
real_size          | 6955008
extra_size         | -13189120
extra_ratio        | -189.63486454652534
fillfactor         | 90
bloat_size         | 0
bloat_ratio        | -222.02591283863367
is_na              | f

I suppose this is fine for alert reporting based on large bloat.

In conclusion, I think I'll give up integrating dedup in current query. The best thing I could think of is to flag is_na on deduplicate-ready indexes to ignore them. Maybe a second query dedicated to dedup index might worth a try?

Thoughts?

[mrechte]

Hello,

Have you checked pgstattuple extension, may be querying from the suppleid views would help ?

Thanks

Le 29/10/2020 à 10:35, Jehan-Guillaume (ioguix) de Rorthais a écrit :

Well,

After studying the deduplication mechanism, it seems it:

1. applies lazily during insert...(low dedup)
2. ...and aggressively during create index/reindex (high dedup)
3. do not apply on various types (non deterministic text, numeric, floats, jsonb, composite types)
4. do not apply on covering index

Even if we could produce a close-to-accurate statisticaly expected deduplicated btree size, the size difference between 1 and 2 can no be considered bloat. In extreme scenario, a close-to-none deduplicated index will just have the same size than build with |deduplicate_items = OFF|. Which is not bloat.

Considering 3 and 4, all these constraints could be integrated as condition in the query I suppose... but it will make it even more extremely hard to read and hacky.

Even the result of the query itself might become harder to read:

• should we add a boolean field to flag deduplicated index?
• should we add an deduplicated-expected size?
• should we change the threshold policy to apply on deduplicated but oversized index as well?

On deduplicated index with correct reltuples, the current query reports negative bloat...which is "normal" considering the index is smaller than expected:

|current_database | postgres schemaname | public tblname | t idxname | t_id_on real_size | 6955008 extra_size | -13189120 extra_ratio | -189.63486454652534 fillfactor | 90 bloat_size | 0 bloat_ratio | -222.02591283863367 is_na | f |

I suppose this is fine for alert reporting based on large bloat.

In conclusion, I think I'll give up integrating dedup in current query. The best thing I could think of is to flag |is_na| on deduplicate-ready indexes to ignore them. Maybe a second query dedicated to dedup index might worth a try?

Thoughts?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/OPMDG/check_pgactivity/issues/264#issuecomment-718550349, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABNA73JPTLTACD47JPYL7WDSNEZONANCNFSM4SEWQS7Q.

[gleu]

pgstattuple and ioguix's bloat query have different approach to this issue. pgstattuple gives an exact result at the expense of reading the whole table/index, meaning it is quite slow. ioguix's bloat query relies on statistics to give an approximate but good enough answer without reading the table/index.

[Krysztophe]

pgstattuple_approx could be a middle ground, but, to be quick, it assumes that the vacuums is functioning well, which is one of the problems that the service aims to detect...

There is another big problem: the extension is usually NOT installed. And the project seems to have a policy to never use an extension, but why not? Was it ever discussed?

[gleu]

pgstattuple_approx works for tables, not for indexes. And this ticket is about bloat_btree.