Re: possible optimization: push down aggregates - Mailing list pgsql-hackers

From Tom Lane
Subject Re: possible optimization: push down aggregates
Date
Msg-id 4887.1409171252@sss.pgh.pa.us
Whole thread Raw
In response to Re: possible optimization: push down aggregates  (Merlin Moncure <mmoncure@gmail.com>)
Responses Re: possible optimization: push down aggregates
Re: possible optimization: push down aggregates
List pgsql-hackers
Merlin Moncure <mmoncure@gmail.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.52rows=1 width=0)  InitPlan 1 (returns $0)    ->  Limit  (cost=0.46..0.51 rows=1 width=4)          ->
MergeAppend  (cost=0.46..267.71 rows=4777 width=4)                Sort Key: pp.f1                ->  Index Only Scan
Backwardusing 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)
IndexCond: (f1 IS NOT NULL)                ->  Index Only Scan Backward using cc2_f1_key on cc2  (cost=0.15..85.94
rows=2388width=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



pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: SKIP LOCKED DATA (work in progress)
Next
From: Pavel Stehule
Date:
Subject: Re: [Fwd: Re: proposal: new long psql parameter --on-error-stop]