Skip site navigation (1) Skip section navigation (2)

Re: How to use indexes for GROUP BY

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Dimi Paun <dimi(at)lattica(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How to use indexes for GROUP BY
Date: 2011-01-24 19:33:05
Message-ID: AANLkTimcRtF6TmeFJO2OWByB_uaqRDoo5HTx1oVRF5X4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: hubert depesz lubaczewskiDate: 2011-01-24 20:07:36
Subject: Re: How to use indexes for GROUP BY
Previous:From: Shaun ThomasDate: 2011-01-24 19:27:38
Subject: Re: How to use indexes for GROUP BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group