Horton

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

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Andrei Lepikhov <lepihov@gmail.com>, 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-02-09T15:38:03Z
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()

On Sun, Feb 9, 2025 at 1:58 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> On Thu, Jan 9, 2025 at 3:11 PM Alena Rybakina <a.rybakina@postgrespro.ru> wrote:
> > 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.
>
> I took a look at this patch.
>
> +    /* TODO: remember parameters */
>
> What was intended to do here?
>
> Also, aren't we too restrictive while requiring is_simple_values_sequence()?
> For instance, I believe cases like this (containing Var) could be transformed too.
>
> select * from t t1, lateral (select * from t t2 where t2.i in (values (t1.i), (1)));

Also, I think there is quite a code duplication about construction of
SAOP between match_orclause_to_indexcol() and convert_VALUES_to_ANY()
functions.  I would like to see a refactoring as a separate first
patch, which extracts the common part into a function.

------
Regards,
Alexander Korotkov
Supabase