From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Claudio Freire <klaussfreire(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: possible optimization: push down aggregates |
Date: | 2014-08-27 20:56:50 |
Message-ID: | CAFj8pRDLp5du05xja=0=toi2H55oe-9bkosSE6AjH6TJnF9hAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2014-08-27 21:03:04 | Re: delta relations in AFTER triggers |
Previous Message | Pavel Stehule | 2014-08-27 20:48:54 | Re: [Fwd: Re: proposal: new long psql parameter --on-error-stop] |