From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | possible optimization: push down aggregates |
Date: | 2014-08-27 19:07:23 |
Message-ID: | CAFj8pRDLA9EPS4QHOkZ64vppCuUiodBLOv8c8fLW7aSJhyGEdA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
one user asked about using a partitioning for faster aggregates queries.
I found so there is not any optimization.
create table x1(a int, d date);
create table x_1 ( check(d = '2014-01-01'::date)) inherits(x1);
create table x_2 ( check(d = '2014-01-02'::date)) inherits(x1);
create table x_3 ( check(d = '2014-01-03'::date)) inherits(x1);
When I have this schema, then optimizer try to do
postgres=# explain verbose select max(a) from x1 group by d order by d;
QUERY
PLAN
--------------------------------------------------------------------------------
GroupAggregate (cost=684.79..750.99 rows=200 width=8)
Output: max(x1.a), x1.d
Group Key: x1.d
-> Sort (cost=684.79..706.19 rows=8561 width=8)
Output: x1.d, x1.a
Sort Key: x1.d
-> Append (cost=0.00..125.60 rows=8561 width=8)
-> Seq Scan on public.x1 (cost=0.00..0.00 rows=1 width=8)
Output: x1.d, x1.a
-> Seq Scan on public.x_1 (cost=0.00..31.40 rows=2140
width=8)
Output: x_1.d, x_1.a
-> Seq Scan on public.x_2 (cost=0.00..31.40 rows=2140
width=8)
Output: x_2.d, x_2.a
-> Seq Scan on public.x_3 (cost=0.00..31.40 rows=2140
width=8)
Output: x_3.d, x_3.a
-> Seq Scan on public.x_4 (cost=0.00..31.40 rows=2140
width=8)
Output: x_4.d, x_4.a
Planning time: 0.333 ms
It can be reduced to:
sort by d
Append
Aggegate (a), d
seq scan from x_1
Aggregate (a), d
seq scan from x_2
Are there some plans to use partitioning for aggregation?
Regards
Pavel
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2014-08-27 19:41:58 | Re: possible optimization: push down aggregates |
Previous Message | Arthur Silva | 2014-08-27 18:30:22 | Re: jsonb format is pessimal for toast compression |