Hi,
On Fri, Sep 18, 2020 at 10:54 AM Katsuragi Yuta
<btkatsuragiyu@oss.nttdata.com> wrote:
>
> This is a proposal to add some features to pg_stat_statements.
> Attached is the patch of this.
>
> pg_stat_statements uses a hash table to hold statistics,
> and the maximum number of its entries can be configured through
> pg_stat_statements.max.
> When the number of entries exceeds the pg_stat_statements.max,
> pg_stat_statements deallocate existing entries.
>
> Currently, it is impossible to know how many times/when this
> deallocation happened.
> But, with this information, more detailed performance analysis would be
> possible.
> So, this patch provides access to this information.
>
> This patch provides a view (pg_stat_statements_ctl) and
> a function (pg_stat_statements_ctl).
> The pg_stat_statements_ctl view is defined
> in terms of a function also named pg_stat_statements_ctl.
>
> The entry of pg_stat_statements_ctl view is removed
> when pg_stat_statements_reset(0,0,0) is called.
> Maybe, it is convenient to provide a function named
> pg_stat_statements_ctl_reset
> that removes only the entry of pg_stat_statements_ctl.
>
> The following is an example of this feature.
> Here, a deallocation is shown with the INFO message.
> pg_stat_statements_ctl tracks the number of deallocations
> and timestamp of last deallocation.
>
>
> testdb=# select * from pg_stat_statements_ctl;
> dealloc | last_dealloc
> ---------+-------------------------------
> 2 | 2020-09-18 16:55:31.128256+09
> (1 row)
>
> testdb=# select count(*) from test1;
> 2020-09-18 16:59:20.745 JST [3920] INFO: deallocate
> 2020-09-18 16:59:20.745 JST [3920] STATEMENT: select count(*) from
> test1;
> INFO: deallocate
> count
> -------
> 90
> (1 row)
>
> testdb=# select * from pg_stat_statements_ctl;
> dealloc | last_dealloc
> ---------+-------------------------------
> 3 | 2020-09-18 16:59:20.745652+09
> (1 row)
I like it, this is especially important since this can lead to quite
huge overhead. Did you consider also adding the cumulated number of
evicted entries? This could be useful to know how to configure
pg_stat_statements.max.
