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-04-01T04:14:02Z
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()

Hi, Alena!

On Tue, Apr 1, 2025 at 2:11 AM Alena Rybakina <a.rybakina@postgrespro.ru>
wrote:
> Yes, I agree with that - this is precisely why we need to call
IncrementVarSublevelsUp() unconditionally for all types.
>
> As you mentioned earlier, Var nodes can be nested more deeply, and
skipping this step could lead to incorrect behavior in those cases. So, now
it works fine)
>
> Thank you for an example.
>
> I analyzed this transformation with various types of values that might be
used in conditions.
>
> First, I verified whether the change would affect semantics, especially
in the presence of NULL elements. The only notable behavior I observed was
> the coercion of NULL to an integer type. However, this behavior remains
the same even without our transformation, so everything is fine.

Thank you for your experiments!  I've also rechecked we don't sacrifice
lazy evaluation.  But it appears we don't have one anyway.

CREATE FUNCTION my_func() RETURNS text AS $$
    BEGIN
        RAISE NOTICE 'notice';
        RETURN 'b';
    END;
$$ LANGUAGE 'plpgsql';

# create table test (val text);
# insert into test values ('a');
# explain analyze select * from test where val in (VALUES ('a'),
(my_func()));
NOTICE:  notice
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=0.05..21.26 rows=9 width=64) (actual
time=0.178..0.183 rows=1.00 loops=1)
   Hash Cond: (test.val = ("*VALUES*".column1)::text)
   Buffers: shared hit=1
   ->  Seq Scan on test  (cost=0.00..18.80 rows=880 width=64) (actual
time=0.045..0.048 rows=1.00 loops=1)
         Buffers: shared hit=1
   ->  Hash  (cost=0.03..0.03 rows=2 width=32) (actual time=0.111..0.112
rows=2.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32)
(actual time=0.004..0.065 rows=2.00 loops=1)
 Planning Time: 0.250 ms
 Execution Time: 0.267 ms
(10 rows)

------
Regards,
Alexander Korotkov
Supabase