possible optimization: push down aggregates

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

Responses

Browse pgsql-hackers by date

  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