Re: How to use indexes for GROUP BY

On Mon, Jan 24, 2011 at 11:29 AM, Dimi Paun <dimi(at)lattica(dot)com> wrote:

Two very quick points:

> tts_server_db=# explain analyze select location, max(creationTS) from tagrecord group by location;
>                                                       QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------
>  HashAggregate  (cost=5330.53..5330.55 rows=2 width=18) (actual time=286.161..286.165 rows=3 loops=1)
>   ->  Seq Scan on tagrecord  (cost=0.00..4771.35 rows=111835 width=18) (actual time=0.059..119.828 rows=111739 loops=1)
>  Total runtime: 286.222 ms

Most of your run time is the hashaggregate running, not the seq scan

> BTW, I am using postgresql-server-8.1.22-1.el5_5.1

As another poster observed, you're running an ancient version of pgsql
from a performance perspective. Upgrading to 8.4 or 9.0 would make a
huge difference in overall performance, not just with one or two
queries.