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