[PATCH] Add features to pg_stat_statements

From: Katsuragi Yuta <btkatsuragiyu(at)oss(dot)nttdata(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [PATCH] Add features to pg_stat_statements
Date: 2020-09-18 08:53:44
Message-ID: 0d9f1107772cf5c3f954e985464c7298@oss.nttdata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Regards,
Katsuragi Yuta

Attachment Content-Type Size
pg_stat_statements_ctl_v1.patch text/x-diff 12.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message btnakamichin 2020-09-18 08:55:12 Feature improvement for pg_stat_statements
Previous Message Oleksandr Shulgin 2020-09-18 08:50:19 Re: Concurrency issue in pg_rewind