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