DATE_TRUNC() and GROUP BY?

From: Dave Johansen <davejohansen(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: DATE_TRUNC() and GROUP BY?
Date: 2013-12-20 00:35:11
Message-ID: CAAcYxUesJhma7VDJ_FpT8fH-JWVgQ_KsLX8z-6hjHq-YDuU9Dw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I just ran into an interesting issue on Postgres 8.4. I have a database
with about 3 months of data and when I do following query:
SELECT DATE_TRUNC('day', time) AS time_t, COUNT(*) FROM mytable GROUP BY
time_t;

EXPLAIN shows that it's doing a sort and then a GroupAggregate. There will
only be ~90 outputs, so is there a way I can hint/force the planner to just
do a HashAggregate?

Just to see if it would change the plan, I tried increasing the work_mem up
to 1GB and it still did the same plan.

Thanks,
Dave

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2013-12-20 00:37:19 Re: Regarding Hardware Tuning
Previous Message Steve Crawford 2013-12-19 23:40:25 Re: Regarding Hardware Tuning