Horton

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

Alena Rybakina <a.rybakina@postgrespro.ru>

View thread
From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Ivan Kush <ivan.kush@tantorlabs.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Laurenz Albe <laurenz.albe@cybertec.at>
Date: 2025-01-09T13:10:51Z
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

Hi!

On 04.10.2024 12:05, Andrei Lepikhov wrote:
> 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. 

I updated the patch due to the problem with the coercion types for both 
sides of the expression.

We must find a common type for both leftop of the expression and rightop 
including constants for correct transformation, and at the same time 
check that the resulting types are compatible.

To do this we find an operator for the two input types if it is 
possible, and also remember the target types for the left and right 
sides, and after that make a coercion.

This processing is only needed in cases where we are not working with 
parameters since the final type is not specified for the parameters.

-- 
Regards,
Alena Rybakina
Postgres Professional