Обсуждение: 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 secure email.
byme@byme.email schreef op ma 02-10-2023 om 16:19 [+0000]: > > > 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
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 othersdo 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 improvementwould 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 <wim.bertels@ucll.be> wrote: > byme@byme.email schreef op ma 02-10-2023 om 16:19 [+0000]: > > > 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
On Tue, 3 Oct 2023 at 21:07, <byme@byme.email> wrote: > P.S.: The only serious discussion I was able to find about it was from 2015 here, everyone basically stating that the improvementwould be useful. https://postgrespro.com/list/thread-id/1880012 There is some active discussion and a patch which aims to improve the situation in [1]. Perhaps, if you're in a position to, you could help review that. David [1] https://postgr.es/m/flat/CA%2Bq6zcWtUbT_Sxj0V6HY6EZ89uv5wuG5aefpe_9n0Jr3VwntFg%40mail.gmail.com
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 othersdo 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_9n0Jr3VwntFg@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
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 lowerversions right away? LJ Sent with Proton Mail secure email. ------- Original Message ------- On Tuesday, October 3rd, 2023 at 10:54 AM, Laurenz Albe <laurenz.albe@cybertec.at> 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, whatothers 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_9n0Jr3VwntFg@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
On Tue, 3 Oct 2023 at 22:03, <byme@byme.email> wrote: > 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 lowerversions right away? It wouldn't ever be put into anything earlier than 17. David