Horton

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

Andrei Lepikhov <lepihov@gmail.com>

View thread
From: Andrei Lepikhov <lepihov@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>, Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>, Ivan Kush <ivan.kush@tantorlabs.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-10-04T09:05:01Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Stabilize regression test from c0962a113.

  2. Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate

  3. Extract make_SAOP_expr() function from match_orclause_to_indexcol()

Attachments

On 10/4/24 04:08, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.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