Re: Replace IN VALUES with ANY in WHERE clauses during optimization

On 10/4/24 04:08, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
>> I wonder if it is worth the extra planning time to detect and improve
>> such queries.
>
> I'm skeptical too. I'm *very* skeptical of implementing it in the
> grammar as shown here --- I'd go so far as to say that that approach
> cannot be accepted. That's far too early, and it risks all sorts
> of problems. An example is that the code as given seems to assume
> that all the sublists are the same length ... but we haven't checked
> that yet. I also suspect that this does not behave the same as the
> original construct for purposes like resolving dissimilar types in
> the VALUES list. (In an ideal world, perhaps it'd behave the same,
> but that ship sailed a couple decades ago.)
We also have an implementation of VALUES -> ARRAY transformation.
Because enterprises must deal with users' problems, many of these users
employ automatically generated queries.
Being informed very well of the consensus about that stuff, we've
designed it as a library. But, looking into the code now, I see that it
only needs a few cycles if no one 'x IN VALUES' expression is presented
in the query. Who knows? It may be OK for the core.
So, I've rewritten the code into the patch - see it in the attachment.

The idea is quite simple - at the same place as
convert_ANY_sublink_to_join, we can test the SubLink on proper VALUES
RTE and perform the transformation if it's convertible.

--
regards, Andrei Lepikhov