Thread: Why does not subquery pruning conditions inherit to parent query?
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
On Fri, 24 May 2019 at 19:44, Kato, Sho <kato-sho@jp.fujitsu.com> wrote: > I execute following query to the partitioned table, but the plan is different from my assumption, so please tell me thereason. > > 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
Friday, May 24, 2019 5:10 PM, David Rowley wrote:
> 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.
Thank you for your replay.
You are right. I should do that.
However, following query looks like the subquery qual is pushed down into the outer query.
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)
So, I think I could improve this behavior.
Why such a difference occur?
regards,
Sho Kato
> -----Original Message-----
> From: David Rowley [mailto:david.rowley@2ndquadrant.com]
> Sent: Friday, May 24, 2019 5:10 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: Why does not subquery pruning conditions inherit to parent
> query?
>
> On Fri, 24 May 2019 at 19:44, Kato, Sho <kato-sho@jp.fujitsu.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
>
>
"Kato, Sho" <kato-sho@jp.fujitsu.com> writes:
> Friday, May 24, 2019 5:10 PM, David Rowley wrote:
>> The planner can only push quals down into a subquery, it cannot pull quals
>> from a subquery into the outer query.
> However, following query looks like the subquery qual is pushed down into the outer query.
> 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)
No, what is happening there is that the subquery gets inlined into the
outer query. That can't happen in your previous example because of
the aggregation/GROUP BY --- but subqueries that are just scan/join
queries generally get merged into the parent.
regards, tom lane
Monday, May 27, 2019 7:56 PM Tom Lane wrote: > No, what is happening there is that the subquery gets inlined into the > outer query. That can't happen in your previous example because of the > aggregation/GROUP BY --- but subqueries that are just scan/join queries > generally get merged into the parent. Thank you for your replay and sorry for late response. Ok, I understand. Is it possible to improve a subquery quals to pull up into outer query? Oracle looks like do that. Regards, Kato Sho > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Monday, May 27, 2019 7:56 PM > To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com> > Cc: 'David Rowley' <david.rowley@2ndquadrant.com>; > pgsql-hackers@postgresql.org > Subject: Re: Why does not subquery pruning conditions inherit to parent > query? > > "Kato, Sho" <kato-sho@jp.fujitsu.com> writes: > > Friday, May 24, 2019 5:10 PM, David Rowley wrote: > >> The planner can only push quals down into a subquery, it cannot pull > >> quals from a subquery into the outer query. > > > However, following query looks like the subquery qual is pushed down > into the outer query. > > 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) > > No, what is happening there is that the subquery gets inlined into the > outer query. That can't happen in your previous example because of the > aggregation/GROUP BY --- but subqueries that are just scan/join queries > generally get merged into the parent. > > regards, tom lane > >
On Fri, 31 May 2019 at 03:18, Kato, Sho <kato-sho@jp.fujitsu.com> wrote: > Is it possible to improve a subquery quals to pull up into outer query? Sure, it's possible, but it would require writing code. When it can and cannot/should not be done would need to be determined. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Friday, May 31, 2019 9:33 PM, David Rowley wrote: > On Fri, 31 May 2019 at 03:18, Kato, Sho <kato-sho@jp.fujitsu.com> wrote: > > Is it possible to improve a subquery quals to pull up into outer query? > > Sure, it's possible, but it would require writing code. When it can and > cannot/should not be done would need to be determined. Is there any harmful effect by pulling up a subquery quals into outer query? Even if this feature is not be needed, it will be a problem if user execute this query to a table partitioned into a lot. So, I think it would be better to put together a query that partition pruning does not work on the wiki. Thoughts? Regards, kato sho > -----Original Message----- > From: David Rowley [mailto:david.rowley@2ndquadrant.com] > Sent: Friday, May 31, 2019 9:33 PM > To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com> > Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org > Subject: Re: Why does not subquery pruning conditions inherit to parent > query? > > On Fri, 31 May 2019 at 03:18, Kato, Sho <kato-sho@jp.fujitsu.com> wrote: > > Is it possible to improve a subquery quals to pull up into outer query? > > Sure, it's possible, but it would require writing code. When it can and > cannot/should not be done would need to be determined. > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > >
On Thu, 6 Jun 2019 at 19:47, Kato, Sho <kato-sho@jp.fujitsu.com> wrote: > > On Friday, May 31, 2019 9:33 PM, David Rowley wrote: > > Sure, it's possible, but it would require writing code. When it can and > > cannot/should not be done would need to be determined. > > Is there any harmful effect by pulling up a subquery quals into outer query? There are certainly cases where it can't be done, for example, if the subquery is LEFT or FULL joined to. There's probably no shortage of other cases too. Someone will need to do the analysis into cases where it can and can't be done. That's likely more work than writing code to make it work. > Even if this feature is not be needed, it will be a problem if user execute this query to a table partitioned into a lot. > So, I think it would be better to put together a query that partition pruning does not work on the wiki. > Thoughts? It's not really a restriction of partition pruning. Pruning done during query planning can only use the base quals of the partitioned relation. Run-time pruning goes only a little further and expands that to allow parameters from other relations to be used too. The good thing is that you can easily determine what those quals are by looking at EXPLAIN. They're the ones that make it down to the scan level. There's also a series of restrictions on top of that too, which are not very well documented outside of the code. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services