Re: possible optimization: push down aggregates

On Wed, Aug 27, 2014 at 3:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> associative bit just makes it easier (which is important of course!).
>> mean for example can be pushed down if the 'pushed down' aggregates
>> return to the count to the "reaggregator" so that you can weight the
>> final average. that's a lot more complicated though.
>
> The real question is what you're expecting to get out of such an
> "optimization". If the aggregate has to visit all rows then it's
> not apparent to me that any win emerges from the extra complication.
>
> We do already have optimization of min/max across inheritance trees,
> and that's certainly a win because you don't have to visit all rows.
>
> regression=# create table pp(f1 int unique);
> CREATE TABLE
> regression=# create table cc(unique(f1)) inherits(pp);
> CREATE TABLE
> regression=# create table cc2(unique(f1)) inherits(pp);
> CREATE TABLE
> regression=# explain select max(f1) from pp;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------
> Result (cost=0.51..0.52 rows=1 width=0)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.46..0.51 rows=1 width=4)
> -> Merge Append (cost=0.46..267.71 rows=4777 width=4)
> Sort Key: pp.f1
> -> Index Only Scan Backward using pp_f1_key on pp (cost=0.12..8.14 rows=1 width=4)
> Index Cond: (f1 IS NOT NULL)
> -> Index Only Scan Backward using cc_f1_key on cc (cost=0.15..85.94 rows=2388 width=4)
> Index Cond: (f1 IS NOT NULL)
> -> Index Only Scan Backward using cc2_f1_key on cc2 (cost=0.15..85.94 rows=2388 width=4)
> Index Cond: (f1 IS NOT NULL)
> Planning time: 0.392 ms
> (12 rows)
>
> That doesn't currently extend to the GROUP BY case unfortunately.

Yeah: I was overthinking it. My mind was on parallel processing of
the aggregate (which is not what Pavel was proposing) because that
just happens to be what I'm working on currently -- using dblink to
decompose various aggregates and distribute the calculation across
servers. "Woudn't it nice to have to the server to that itself", I
impulsively thought.

merlin