Re: possible optimization: push down aggregates

2014-08-27 22:27 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> 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.
>

I expect a remove a hashing or sorting part of aggregation. It can reduce
aggregation to seq scan only.

Pavel

>
> 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.
>
> regards, tom lane
>