Message-ID: <9b041978-06e3-4a50-8a5d-dacbb054f23e@tantorlabs.com>
Date: Fri, 27 Dec 2024 15:53:35 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Exists pull-up application with JoinExpr
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Ranier Vilela <ranier.vf@gmail.com>
References: <0b1f670d-b39d-4966-bf32-f0d502ebc564@postgrespro.ru>
 <CAEudQAoD707uh5Pjpg5NMyF-QO=fzajA+BmtcoqQAeXN1C+TkQ@mail.gmail.com>
 <46bc4eaf-58c5-42ab-8041-d3380a0768de@postgrespro.ru>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <46bc4eaf-58c5-42ab-8041-d3380a0768de@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/9b041978-06e3-4a50-8a5d-dacbb054f23e%40tantorlabs.com>
Precedence: bulk

Hi Alena,

Thank you for your work on subqueries with JOIN.

Have you considered the scenario where in subquery includes a qual like 
(tc.aid = 1)? When I tried executing those queries I receive different 
results. In my opinion, to prevent this, we should add filters for such 
quals within the loop 'foreach (lc, all_clauses)'

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM ta
WHERE EXISTS (SELECT * FROM tb, tc WHERE ta.id = tb.id AND tc.aid = 1);
                               QUERY PLAN
----------------------------------------------------------------------
  Hash Join (actual rows=1 loops=1)
    Hash Cond: (ta.id = tb.id)
    Buffers: local hit=3
    ->  Seq Scan on ta (actual rows=3 loops=1)
          Buffers: local hit=1
    ->  Hash (actual rows=3 loops=1)
          Buckets: 4096  Batches: 1  Memory Usage: 33kB
          Buffers: local hit=2
          ->  HashAggregate (actual rows=3 loops=1)
                Group Key: tb.id
                Batches: 1  Memory Usage: 121kB
                Buffers: local hit=2
                ->  Nested Loop (actual rows=3 loops=1)
                      Buffers: local hit=2
                      ->  Seq Scan on tb (actual rows=3 loops=1)
                            Buffers: local hit=1
                      ->  Materialize (actual rows=1 loops=3)
                            Storage: Memory  Maximum Storage: 17kB
                            Buffers: local hit=1
                            ->  Seq Scan on tc (actual rows=1 loops=1)
                                  Filter: (aid = 1)
                                  Rows Removed by Filter: 1
                                  Buffers: local hit=1
(23 rows)

============================

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
SELECT * FROM ta WHERE EXISTS (SELECT * FROM tb JOIN tc ON ta.id =
tb.id WHERE tc.aid = 1);
                                 QUERY PLAN
---------------------------------------------------------------------------
  Seq Scan on ta (actual rows=1 loops=1)
    Filter: EXISTS(SubPlan 1)
    Rows Removed by Filter: 2
    Buffers: local hit=6
    SubPlan 1
      ->  Nested Loop (actual rows=0 loops=3)
            Buffers: local hit=5
            ->  Index Only Scan using tb_pkey on tb (actual rows=0 loops=3)
                  Index Cond: (id = ta.id)
                  Heap Fetches: 1
                  Buffers: local hit=4
            ->  Seq Scan on tc (actual rows=1 loops=1)
                  Filter: (aid = 1)
                  Buffers: local hit=1
(14 rows)

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.