Postgres Pro
Downloads
Home   >   mailing lists

Re: Partial Aggregation / GROUP BY before JOIN - Mailing list pgsql-hackers

From David Rowley
Subject Re: Partial Aggregation / GROUP BY before JOIN
Date
Msg-id CAKJS1f8cw_Sw1WKa7FwC8T7C8FK4=jusYy4038yfwtATA3xg1w@mail.gmail.com
Whole thread Raw
In response to Re: Partial Aggregation / GROUP BY before JOIN  (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
Responses Re: Partial Aggregation / GROUP BY before JOIN  (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
List pgsql-hackers
Tree view
On 28 September 2015 at 23:17, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
On 2015/09/28 17:04, David Rowley wrote:
> On 28 September 2015 at 20:36, Amit Langote <Langote_Amit_f8@lab.ntt.co.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/
 PostgreSQL Development, 24x7 Support, Training & Services
 

pgsql-hackers by date:

Previous
From: "Shulgin, Oleksandr"
Date:
Subject: Re: On-demand running query plans using auto_explain and signals
Next
From: YUriy Zhuravlev
Date:
Subject: Re: No Issue Tracker - Say it Ain't So!