Re: Add statistics refresh materialized view

On 2021/07/09 1:39, Seino Yuki wrote:
> Hi.
>
> This is a proposal for a new feature in statistics collector.
> I think we need to add statistics about refresh matview to pg_stat_all_tables view.

Why do you want to treat only REFRESH MATERIALIZED VIEW command special?
What about other utility commands like TRUNCATE, CLUSTER, etc?

It's not good design to add new columns per utility command into
pg_stat_all_tables. Otherwise pg_stat_all_tables will have to have lots of
columns to expose the stats of many utility commands at last. Which is
ugly and very user-unfriendly.

Most entries in pg_stat_all_tables are basically for tables. So the columns
about REFRESH MATERIALIZED VIEW are useless for those most entries.
This is another reason why I think the design is not good.

>
> When the "REFRESH MATERIALIZED VIEW" was executed, the number of times it was executed
> and date it took were not recorded anywhere.

pg_stat_statements and log_statement would help?

>
> "pg_stat_statements" can be used to get the number of executions and the date and time of execution,
> but this information is statement-based, not view-based.

pg_stat_statements reports different records for REFRESH MATERIALIZED VIEW
commands on different views. So ISTM that we can aggregate the information
per view, from pg_stat_statements. No?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION