Re: Rethinking representation of partial-aggregate steps

On 24 June 2016 at 05:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Hmm, well I guess I would have to disagree with the idea that those
>> other modes aren't useful. I seem to recall that David had some
>> performance results showing that pushing partial aggregate steps below
>> an Append node resulted in a noticeable speed-up even in the absence
>> of any parallelism, presumably because it avoids whatever projection
>> the Append might do, and also improves icache and dcache locality.
>
> I don't believe that for one second, because introducing another layer of
> intermediate aggregation implies another projection step, plus all the
> other overhead of a Plan node.

It's certainly not difficult to mock up a test to prove it is faster.

create table t1 (a int not null);
insert into t1 select generate_series(1,1000000);
create table t2 (a int not null);
insert into t2 select generate_series(1,1000000);

select sum(c) from (select count(*) c from t1 union all select
count(*) from t2) t;
sum
---------
2000000
(1 row)

Time: 82.038 ms
select count(*) from (select * from t1 union all select * from t2) t;
count
---------
2000000
(1 row)

Time: 180.540 ms

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services