Re: Add TOAST statistics columns to pg_stat_all_tables

Hi all,

On Thu, Oct 15, 2026 at 2:53 PM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
>
> On Tue, Sep 15, 2026 at 6:48 AM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
> > 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.
>
> Thanks for testing and measuring this. You are right, and v2 does it
> that way. Removing the join also leaves the FROM clause and GROUP BY
> list untouched, so the patch now only adds columns.
>
> > 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:
>
> Adopted. v2 compares all four columns against the TOAST table's own
> row in pg_stat_all_tables. I also changed the lookup from relname to
> 'stats_toast_test'::regclass, so the test cannot match a same-named
> table from another session under installcheck.
>
>
>

Thank you for the patch. I tested the patch on the current development
branch. The patched source builds successfully, and all 239 regression
tests passed. I also performed a manual test using a table containing
large text values. I verified that the four new columns in
pg_stat_all_tables report the expected TOAST statistics. The reported
TOAST relation OID matched the actual relation, and the dead-tuple
count matched the TOAST relation's own statistics. I also verified the
behavior for a table without a TOAST relation. Additionally, I
reviewed the regression-test changes and the view definition. The
tests cover the new statistics, including the case where a table has
no TOAST relation. The implementation uses the owning table's existing
reltoastrelid value and does not reintroduce the additional pg_class
join that caused overhead. The patch looks good to me based on my
testing.

Regards
Solai