Thread: Enable pg_stat_statements extension for limited statements only
Hi, I'd like to propose a change and get advice if I should work on it.
For example, deletes do not happen too frequently; so I'd like to be able to enable pg_stat_statements only for the DELETE statement, maybe using some flags.
Another possibility is if we can limit the tables to which pg_stat_statements logs results.
Sayyid Ali Sajjad Rizavi <sasrizavi@gmail.com> writes:
> Hi, I'd like to propose a change and get advice if I should work on it.
> The extension pg_stat_statements is very helpful, but the downside is that
> it will take up too much disk space when storing query stats if it's
> enabled for all statements like SELECT, INSERT, UPDATE, DELETE.
It will only take up a lot of disk space if you let it, by setting
the pg_stat_statements.max parameter too high.
> For example, deletes do not happen too frequently; so I'd like to be able
> to enable pg_stat_statements only for the DELETE statement, maybe using
> some flags.
I'm a little skeptical of the value of that. Why would you want stats
only for infrequent statements?
I'm not denying that there might be usefulness in filtering what
pg_stat_statements will track, but it's not clear to me that
this particular proposal will be useful to many people.
I wonder whether there would be more use in filters expressed
as regular expressions to match against the statement text.
That would allow, for example, tracking statements that mention
a particular table as well as statements with a particular
head keyword. I could see usefulness in both a positive filter
(must match this to get tracked) and a negative one (must not
match this to get tracked).
regards, tom lane
Re: Enable pg_stat_statements extension for limited statements only
From
Sayyid Ali Sajjad Rizavi
Date:
Yes, I agree that infrequent statements don't need stats. Actually I was distracted with the use case that I had in mind other than stats, maybe bringing that up will help.
If someone's interested how frequent are deletes being run on a particular table, or what was the exact query that ran. Basically keeping track of queries. Although now I'm less convinced if a considerable amount of people will be interested in this, but let me know what you think.
On Wed, Nov 30, 2022 at 10:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Sayyid Ali Sajjad Rizavi <sasrizavi@gmail.com> writes:
> Hi, I'd like to propose a change and get advice if I should work on it.
> The extension pg_stat_statements is very helpful, but the downside is that
> it will take up too much disk space when storing query stats if it's
> enabled for all statements like SELECT, INSERT, UPDATE, DELETE.
It will only take up a lot of disk space if you let it, by setting
the pg_stat_statements.max parameter too high.
> For example, deletes do not happen too frequently; so I'd like to be able
> to enable pg_stat_statements only for the DELETE statement, maybe using
> some flags.
I'm a little skeptical of the value of that. Why would you want stats
only for infrequent statements?
I'm not denying that there might be usefulness in filtering what
pg_stat_statements will track, but it's not clear to me that
this particular proposal will be useful to many people.
I wonder whether there would be more use in filters expressed
as regular expressions to match against the statement text.
That would allow, for example, tracking statements that mention
a particular table as well as statements with a particular
head keyword. I could see usefulness in both a positive filter
(must match this to get tracked) and a negative one (must not
match this to get tracked).
regards, tom lane