Thread: pg_stat_statements IN problem

pg_stat_statements IN problem

From
byme@byme.email
Date:
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.

Re: pg_stat_statements IN problem

From
Wim Bertels
Date:
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



Re: pg_stat_statements IN problem

From
byme@byme.email
Date:
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



Re: pg_stat_statements IN problem

From
David Rowley
Date:
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



Re: pg_stat_statements IN problem

From
Laurenz Albe
Date:
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



Re: pg_stat_statements IN problem

From
byme@byme.email
Date:
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



Re: pg_stat_statements IN problem

From
David Rowley
Date:
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