Subject: How to use indexes for GROUP BY
From: Dimi Paun <dimi@lattica.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Organization: Lattica, Inc.
Date: Mon, 24 Jan 2011 13:29:01 -0500
Message-ID: <1295893741.9380.984.camel@dimi.lattica.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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


Now I have the idx_tagdata_loc_creationTS, and it seemed to me that
it should be able to use it to quickly figure out the max creationTS
for each location.

Any way I can make this more efficient?

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

-- 
Dimi Paun <dimi@lattica.com>
Lattica, Inc.