From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Partial Aggregation / GROUP BY before JOIN |
Date: | 2015-09-28 11:58:19 |
Message-ID: | CAKJS1f8cw_Sw1WKa7FwC8T7C8FK4=jusYy4038yfwtATA3xg1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 28 September 2015 at 23:17, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:
> On 2015/09/28 17:04, David Rowley wrote:
> > On 28 September 2015 at 20:36, Amit Langote <
> Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
> > wrote:
> >
> >>
> >> Did you perhaps attach a version of the patch you didn't intend to?
> >>
> >
> > Oops. It seems so.
> >
> > Please find the correct version attached.
>
> Thanks, this one works fine.
>
> By the way, you may have noticed that the append_rel_list would be broken
> if the proposed optimization is applied to a appendrel parent.
>
> CREATE TABLE sale_1() INHERITS(sale);
> CREATE TABLE sale_2() INHERITS(sale);
>
> EXPLAIN SELECT count(*) FROM sale;
> QUERY PLAN
> ------------------------------------------------------
> Finalize Aggregate (cost=0.01..0.02 rows=1 width=0)
> -> Result (cost=0.00..0.01 rows=1 width=0)
> One-Time Filter: false
> (3 rows)
>
>
Thanks. I've changed this locally to disable the optimisation in this case.
> Moreover, would partial aggregation work below Append?
>
Do you mean for cases like:
create table a as select x.x a from generate_series(1,1000000) x(x);
select sum(a) from (select a from a union all select a from a) a;
to allow the aggregation to happen before the append?
On testing this I do see that writing the query as:
select sum(a) from (select sum(a) a from a union all select sum(a) from a)
a;
causes it to execute marginally faster. 174.280 ms vs 153.498 ms on my
laptop.
However pushing aggregation below Append nodes is not something I'm aiming
to do for this patch.
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | YUriy Zhuravlev | 2015-09-28 12:50:29 | Re: No Issue Tracker - Say it Ain't So! |
Previous Message | Shulgin, Oleksandr | 2015-09-28 10:37:42 | Re: On-demand running query plans using auto_explain and signals |