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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
