Re: Improve handling of pg_stat_statements handling of bind "IN" variables

Thanks for your input.

As for real-world applications – being a founder of a server monitoring
saas (okmeter) I have access to stats on hundreds of postgres installations.

It shows that IN with a variable number of params is ~7 times more used
than ANY(array).

On Wed, Jun 26, 2019 at 11:10 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Greg Stark <stark(at)mit(dot)edu> writes:
> > Actually thinking about this for two more seconds the question is what it
> > would do with a query like
> > WHERE col = ANY '1,2,3'::integer[]
> > Or
> > WHERE col = ANY ARRAY[1,2,3]
> > Whichever the language binding that is failing to do parameterized
> queries
> > is doing to emulate them.
>
> Yeah, one interesting question is whether this is actually modeling
> what happens with real-world applications --- are they sending Consts,
> or Params?
>
> I resolutely dislike the idea of marking arrays that came from IN
> differently from other ones; that's just a hack and it's going to give
> rise to unexplainable behavioral differences for logically-equivalent
> queries.
>
> One idea that comes to me after looking at the code involved is that
> it might be an improvement across-the-board if transformAExprIn were to
> reduce the generated ArrayExpr to an array Const immediately, in cases
> where all the inputs are Consts. That is going to happen anyway come
> plan time, so it'd have zero impact on semantics or query performance.
> Doing it earlier would cost nothing, and could even be a net win, by
> reducing per-parse-node overhead in places like the rewriter.
>
> The advantage for the problem at hand is that a Const that's an array
> of 2 elements is going to look the same as a Const that's any other
> number of elements so far as pg_stat_statements is concerned.
>
> This doesn't help of course in cases where the values aren't all
> Consts. Since we eliminated Vars already, the main practical case
> would be that they're Params, leading us back to the previous
> question of whether apps are binding queries with different numbers
> of parameter markers in an IN, and how hard pg_stat_statements should
> try to fuzz that if they are.
>
> Then, to Greg's point, there's a question of whether transformArrayExpr
> should do likewise, ie try to produce an array Const immediately.
> I'm a bit less excited about that, but consistency suggests that
> we should have it act the same as the IN case.
>
> regards, tom lane
>