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

Re: How to use indexes for GROUP BY

From: hubert depesz lubaczewski <depesz(at)depesz(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 20:07:36
Message-ID: 20110124200735.GA23732@depesz.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Jan 24, 2011 at 01:29:01PM -0500, Dimi Paun wrote:
> Hi folks,
> 
> I have a table like so:
> 
> create table tagRecord (
>     uid             varchar(60) primary key,
>     [bunch of other fields]
>     location      varchar(32),
>     creationTS    timestamp
> );
> create index idx_tagdata_loc_creationTS on tagRecord(location, creationTS);
> 
> The number of individual values in location is small (e.g. 2).
> 
> I want to simply get the latest "creationTS" for each location,
> but that seems to result in a full table scan:
> 
> 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

you can use technique described in here:
http://www.depesz.com/index.php/2009/07/10/getting-list-of-unique-elements/

Best regards,

depesz


In response to

pgsql-performance by date

Next:From: Jeremy PalmerDate: 2011-01-24 21:55:07
Subject: Re: Possible to improve query plan?
Previous:From: Scott MarloweDate: 2011-01-24 19:33:05
Subject: Re: How to use indexes for GROUP BY

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