pg_stat_statements IN problem
Hello,
I would like to ask about a problem that is bothering me for a while now. We
have implemented monitoring of our queries using pg_stat_statements. The only
problem we have with it is that expressions with
IN ('first', 'second', 'third')
get translated into a query as
IN ($1, $2, $3)
and not as
IN $1
This obfuscates our monitoring because the same query with different amount of
arguments get translated into this:
IN ('first', 'second')
and so on.
I was trying to find some discussions about it, but could not find much. One
suggestion was to use
= ANY
but that is a problem when using some third party framework for db operations,
in our case for python, for example web2py's DAL or sqlalchemy.
Is there a possibility the pg_stat_statements will be improved with handling
IN? This problem makes it so much less useful right now.
Or is there something I could do to have the statistics for such queries
aggreageted? Or is there something else I am missing?
Best regards
Lukas Jerabek
Sent with [Proton Mail](https://proton.me/) secure email.
Re: pg_stat_statements IN problem
Thank you for response. Unfortunately, I have to update one section which I wrote wrong, it should have been this way: "This obfuscates our monitoring because the same query with different amount of arguments gets translated into this: IN ($1, $2) and so on." The questions are: 1. Shouldnt IN behave so that the query in pg_stat_statements would look like this: IN $1 2. Shouldnt there be at least some flag to aggregate such queries into one? 3. Is there any workaround how to aggregate those queries except the "= ANY"? 4. How come no one is bothered by this if this makes pg_stat_statements unusable with lots of queries using IN, what others do with this problem? 5. what do you mean by changing pg_stat_statements with another view/table? LJ P.S.: The only serious discussion I was able to find about it was from 2015 here, everyone basically stating that the improvement would be useful. https://postgrespro.com/list/thread-id/1880012 Sent with Proton Mail secure email. --- Original Message --- On Monday, October 2nd, 2023 at 8:50 PM, Wim Bertels wrote: > byme@byme.email schreef op ma 02-10-2023 om 16:19 [+]: > > > Is there a possibility the pg_stat_statements will be improved with > > handling IN? This problem makes it so much less useful right now. > > > not sure what the question is, > but if you change pg_stat_statements with another view/table, > the problem/answer would be the same > > https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR
Re: pg_stat_statements IN problem
Thank you very much Laurenz and David. Looking forward to it and unfortunatelly no, I am not in position to review that... So one last question, should I expect the patch to land in version 17 only or is there chance that it will also be in lower versions right away? LJ Sent with Proton Mail secure email. --- Original Message --- On Tuesday, October 3rd, 2023 at 10:54 AM, Laurenz Albe wrote: > On Tue, 2023-10-03 at 08:05 +0000, byme@byme.email wrote: > > > "This obfuscates our monitoring because the same query with different > > amount of arguments gets translated into this: > > IN ($1, $2) > > and so on." > > > > The questions are: > > 1. Shouldnt IN behave so that the query in pg_stat_statements would look > > like this: > > IN $1 > > 2. Shouldnt there be at least some flag to aggregate such queries into one? > > 3. Is there any workaround how to aggregate those queries except the "= > > ANY"? > > 4. How come no one is bothered by this if this makes pg_stat_statements > > unusable with lots of queries using IN, what others do with this problem? > > 5. what do you mean by changing pg_stat_statements with another view/table? > > > There is currently a patch for this very problem under review: > > https://commitfest.postgresql.org/44/2837/ > > The discussion is here: > > https://www.postgresql.org/message-id/flat/ca+q6zcwtubt_sxj0v6hy6ez89uv5wug5aefpe_9n0jr3vwn...@mail.gmail.com > > You could comment on that patch or review it. Useful reviews and supporting > comments help move the patch forward. That would best serve your interests. > > Yours, > Laurenz Albe
