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: Alexander Korotkov <aekorotkov@gmail.com>
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-03T14:18:46Z
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 02.04.2025 22:00, Alexander Korotkov wrote:
> Hi, Alena!
>
> On Wed, Apr 2, 2025 at 5:33 PM Alena Rybakina 
> <a.rybakina@postgrespro.ru> wrote:
>
>     I prepared a patch according to my suggestions, it just checks
>     that the transformation is not carried out if there is a var
>     element, there are changes only in one test, but I think it is
>     correct.
>
>     diff -U3
>     /home/alena/postgrespro_or3/src/test/regress/expected/subselect.out
>     /home/alena/postgrespro_or3/src/test/regress/results/subselect.out
>     ---
>     /home/alena/postgrespro_or3/src/test/regress/expected/subselect.out
>     2025-04-02 02:50:07.018329864 +0300
>     +++
>     /home/alena/postgrespro_or3/src/test/regress/results/subselect.out
>     2025-04-02 17:27:09.845104001 +0300
>     @@ -3027,18 +3027,15 @@
>      SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN
>        (SELECT (3)))::integer)
>      );
>     -                     QUERY PLAN
>     -----------------------------------------------------
>     - Nested Loop
>     -   ->  Unique
>     -         ->  Sort
>     -               Sort Key: "*VALUES*".column1
>     -               ->  Values Scan on "*VALUES*"
>     -                     SubPlan 1
>     -                       ->  Result
>     -   ->  Index Scan using onek_unique1 on onek t
>     -         Index Cond: (unique1 = "*VALUES*".column1)
>     -(9 rows)
>     +                                           QUERY PLAN
>     +------------------------------------------------------------------------------------------------
>     + Bitmap Heap Scan on onek t
>     +   Recheck Cond: (unique1 = ANY (ARRAY[0, ((ANY (2 = (hashed
>     SubPlan 1).col1)))::integer]))
>     +   ->  Bitmap Index Scan on onek_unique1
>     +         Index Cond: (unique1 = ANY (ARRAY[0, ((ANY (2 = (hashed
>     SubPlan 1).col1)))::integer]))
>     +   SubPlan 1
>     +     ->  Result
>     +(6 rows)
>
>      -- Alow to transformation and hold conversion between types of
>     colemns and
>      -- declared type of column pointed in RTE
>
> Your patch turns back performance degradation issue that I mention in [1].
>
> With your patch.
> # explain analyze select * from test where val in (VALUES (5000), 
> (4000), (9000), (2000), (1000), (140050), ((select 1)));
>             QUERY PLAN
> -------------------------------------------------------------------------------------------------------
>  Seq Scan on test  (cost=0.01..23175.01 rows=7 width=4) (actual 
> time=0.455..417.929 rows=7.00 loops=1)
>    Filter: (val = ANY (ARRAY[5000, 4000, 9000, 2000, 1000, 140050, 
> (InitPlan 1).col1]))
>    Rows Removed by Filter: 999993
>    Buffers: shared hit=2362 read=2063
>    InitPlan 1
>      ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
> time=0.002..0.002 rows=1.00 loops=1)
>  Planning Time: 0.211 ms
>  Execution Time: 417.984 ms
> (8 rows)
>
> Without your patch.
> # explain analyze select * from test where val in (VALUES (5000), 
> (4000), (9000), (2000), (1000), (140050), ((select 1)));
>  QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------
>  Hash Semi Join  (cost=0.19..17050.26 rows=7 width=4) (actual 
> time=0.289..210.335 rows=7.00 loops=1)
>    Hash Cond: (test.val = "*VALUES*".column1)
>    Buffers: shared hit=2211 read=2214
>    InitPlan 1
>      ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
> time=0.002..0.002 rows=1.00 loops=1)
>    ->  Seq Scan on test  (cost=0.00..14425.00 rows=1000000 width=4) 
> (actual time=0.203..85.925 rows=1000000.00 loops=1)
>          Buffers: shared hit=2211 read=2214
>    ->  Hash  (cost=0.09..0.09 rows=7 width=4) (actual 
> time=0.067..0.068 rows=7.00 loops=1)
>          Buckets: 1024  Batches: 1  Memory Usage: 9kB
>          ->  Values Scan on "*VALUES*"  (cost=0.00..0.09 rows=7 
> width=4) (actual time=0.004..0.023 rows=7.00 loops=1)
>  Planning Time: 0.321 ms
>  Execution Time: 210.409 ms
> (12 rows)
>
> Only when we have all consts in the array, we can have SAOP hashing 
> which is competitive with hashing of VALUES node.  So, I'm continuing 
> with my version of patch.
>
> Links.
> 1. 
> https://www.postgresql.org/message-id/CAPpHfds-7eJ3ZMgyeVrMfC5E1nTHD4Bp0ch5MZhrYSoiCfERXw%40mail.gmail.com
>
>
Okay, I agree with you.

-- 
Regards,
Alena Rybakina
Postgres Professional