search

ioguix / pgsql-bloat-estimation

Queries to mesure statistical bloat in indexes and tables for PostgreSQL
BSD 2-Clause "Simplified" License
515 stars 94 forks source link

Use live_tuples instead of reltuples #4

Open ioguix opened 7 years ago

ioguix commented 7 years ago

See discussion: https://www.postgresql.org/message-id/flat/16db4468-edfa-830a-f921-39a50498e77e%402ndquadrant.com#16db4468-edfa-830a-f921-39a50498e77e@2ndquadrant.com

anayrat commented 6 years ago

Maybe this commit will change query's result : https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7c91a0364fcf5d739a09cc87e7adb1d4a33ed112

MichaelDBA commented 2 years ago

So based on the commits, we can assume that the current versions of PG v10+ are OK now, right?

ioguix commented 2 years ago

Hi,

As I understand the discussion on pgsql-hackers, this patch hasn't been back-patched to v10. So it's OK for v11 and after.

MichaelDBA commented 2 years ago

Thanks!

shangzixie commented 2 years ago

hi ioguix, after I REINDEX an index, the output bloat_size about the index doesn't change. I think the reltuples doesn't update after REINDEX. My question is what columns could I use instead of reltuples? which table the live_tuples is in you said?

ioguix commented 2 years ago

Hi @shangzixie,

It comes from pg_stat_all_tables.n_live_tup.

after I REINDEX an index, the output bloat_size about the index doesn't change.

On what version are you testing? Could you share your scenario?

shangzixie commented 2 years ago

Hi @shangzixie,

It comes from pg_stat_all_tables.n_live_tup.

after I REINDEX an index, the output bloat_size about the index doesn't change.

On what version are you testing? Could you share your scenario?

just, run your sql and reindex some table. Oh, I found need to ANALYZE, so as to update reltuples.