Why does not subquery pruning conditions inherit to parent query? - Mailing list pgsql-hackers

From Kato, Sho
Subject Why does not subquery pruning conditions inherit to parent query?
Date
Msg-id 25C1C6B2E7BE044889E4FE8643A58BA972624CD4@G01JPEXMBKW03
Whole thread Raw
Responses Re: Why does not subquery pruning conditions inherit to parent query?
List pgsql-hackers
Hello

I execute following query to the partitioned table, but the plan is different from my assumption, so please tell me the
reason.

postgres=# explain select * from jta, (select a, max(b)  from jtb where a = 1 group by a ) c1 where jta.a = c1.a;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Hash Join  (cost=38.66..589.52 rows=1402 width=12)
   Hash Cond: (jta0.a = jtb0.a)
   ->  Append  (cost=0.00..482.50 rows=25500 width=4)
         ->  Seq Scan on jta0  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta1  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta2  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta3  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta4  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta5  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta6  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta7  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta8  (cost=0.00..35.50 rows=2550 width=4)
         ->  Seq Scan on jta9  (cost=0.00..35.50 rows=2550 width=4)
   ->  Hash  (cost=38.53..38.53 rows=11 width=8)
         ->  GroupAggregate  (cost=0.00..38.42 rows=11 width=8)
               Group Key: jtb0.a
               ->  Seq Scan on jtb0  (cost=0.00..38.25 rows=11 width=8)
                     Filter: (a = 1)
(18 rows)

I assume that subquery aggregate only pruned table and parent query joins pruned table and subquery results.
However, parent query scan all partitions and join.
In my investigation, because is_simple_query() returns false if subquery contains GROUP BY, parent query does not
prune.
Is it possible to improve this?
If subquery has a WHERE clause only, parent query does not scan all partitions.

postgres=# explain select * from jta, (select a from jtb where a = 1) c1 where jta.a = c1.a;
                            QUERY PLAN                            
------------------------------------------------------------------
 Nested Loop  (cost=0.00..81.94 rows=143 width=8)
   ->  Seq Scan on jta0  (cost=0.00..41.88 rows=13 width=4)
         Filter: (a = 1)
   ->  Materialize  (cost=0.00..38.30 rows=11 width=4)
         ->  Seq Scan on jtb0  (cost=0.00..38.25 rows=11 width=4)
               Filter: (a = 1)
(6 rows)

regards,

Sho Kato




pgsql-hackers by date:

Previous
From: didier
Date:
Subject: Re: [HACKERS] Small fix: avoid passing null pointers to memcpy()
Next
From: David Rowley
Date:
Subject: Re: Why does not subquery pruning conditions inherit to parent query?