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 <t...@sss.pgh.pa.us> wrote: > Greg Stark <st...@mit.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 >
