Hi Shinya,
> Thoughts?
I tested v1 on current master. The problem is real. pg_stat_user_tables
hides the pg_toast schema, so TOAST bloat is invisible there. The
regression suite passes with your new test.
There is one thing I would like you to consider change.
Monitoring tools read pg_stat_all_tables all the time, so I measured
what the patch costs. With 10,000 tables, which gives 15,107 rows:
before the patch 19.4 ms
v1 28.9 ms
Almost all of that is the new LEFT JOIN pg_class T, not the three
function calls. Those are strict, so a table with no TOAST table costs
nothing.
The join is also not needed. T.oid holds the same value as
C.reltoastrelid, which you already use via NULLIF(C.reltoastrelid, 0)
for the toast_relid column. Pass that same expression to the three
functions, drop the join and the T.oid in GROUP BY, and the time goes
back to 20.7 ms with the same output. I checked all four columns over
15,120 rows, including materialized views and partitioned tables.
One note on the test. Temporary tables are never autovacuumed, so
toast_last_autovacuum is always NULL there and toast_autovacuum_count
is always 0. Checking the new columns against the TOAST table's own row
would catch a wrong OID:
SELECT s.toast_n_dead_tup = t.n_dead_tup
FROM pg_stat_all_tables s
JOIN pg_stat_all_tables t ON t.relid = s.toast_relid
WHERE s.relname = 'stats_toast_test';
v1 also needs a rebase, because stats.sql and stats.out conflict.
Happy to test a v2. If it is easier, I can send the join change and the
rebase as a patch for you to take or leave.
Thanks,
Shihao
