From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Why does not subquery pruning conditions inherit to parent query? |
Date: | 2019-05-24 08:09:46 |
Message-ID: | CAKJS1f9Z6Uvq7jJi8VD9tZCUh7ZDznNJZzuvx2Ytp_0fdiWV0A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, 24 May 2019 at 19:44, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
> 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?
The planner can only push quals down into a subquery, it cannot pull
quals from a subquery into the outer query.
If you write the query like:
explain select * from jta, (select a, max(b) from jtb group by a ) c1
where jta.a = c1.a and c1.a = 1;
you should get the plan that you want.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2019-05-24 08:30:22 | Re: Read-only access to temp tables for 2PC transactions |
Previous Message | Kato, Sho | 2019-05-24 07:44:18 | Why does not subquery pruning conditions inherit to parent query? |