Re: Exists pull-up application with JoinExpr

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.