Re: [PATCH] pg_statio_all_tables: several rows per table due to invalid TOAST index

Andrei Zubkov <zubkov(at)moonset(dot)ru> writes:
> On Tue, 2021-11-30 at 17:29 +0900, Michael Paquier wrote:
>> Hmm.  Why should we care about invalid indexes at all, including
>> pg_statio_all_indexes?

> I think we should care about them at least because they are exists and
> can consume resources. For example, invalid index is to be updated by
> DML operations.
> Of course we can exclude such indexes from a view using isvalid,
> isready, islive fields. But in such case we should mention this in the
> docs, and more important is that the new such states of indexes can
> appear in the future causing change in a view definition. Counting all
> indexes regardless of states seems more reasonable to me.

Yeah, I agree, especially since we do it like that for the table's
own indexes. I have a couple of comments though:

1. There's a silly typo in the view definition (it outputs tidx_blks_read
twice). Fixed in the attached v2.

2. Historically, if you put any constraints on the view output, like
select * from pg_statio_all_tables where relname like 'foo%';
you'd get a commensurate reduction in the amount of work done. With
this version, you don't: the CTE will get computed in its entirety
even if we just need one row of its result. This seems pretty bad,
especially for installations with many tables --- I suspect many
users would think this cure is worse than the disease.

I'm not quite sure what to do about #2. I thought of just removing
X.indexrelid from the GROUP BY clause and summing over the toast
index(es) as we do for the table's index(es). But that doesn't
work: if there are N > 1 table indexes, then the counts for
the toast index(es) will be multiplied by N, and conversely if
there are multiple toast indexes then the counts for the table
indexes will be scaled up. We need to sum separately over the
table indexes and toast indexes, and I don't immediately see how
to do that without creating an optimization fence.

regards, tom lane