From: | "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Why does not subquery pruning conditions inherit to parent query? |
Date: | 2019-05-24 07:44:18 |
Message-ID: | 25C1C6B2E7BE044889E4FE8643A58BA972624CD4@G01JPEXMBKW03 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2019-05-24 08:09:46 | Re: Why does not subquery pruning conditions inherit to parent query? |
Previous Message | didier | 2019-05-24 07:40:36 | Re: [HACKERS] Small fix: avoid passing null pointers to memcpy() |