From: | Dimi Paun <dimi(at)lattica(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | How to use indexes for GROUP BY |
Date: | 2011-01-24 18:29:01 |
Message-ID: | 1295893741.9380.984.camel@dimi.lattica.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)lattica(dot)com>
Lattica, Inc.
From | Date | Subject | |
---|---|---|---|
Next Message | Shaun Thomas | 2011-01-24 19:27:38 | Re: How to use indexes for GROUP BY |
Previous Message | Kevin Grittner | 2011-01-24 17:38:14 | Re: Possible to improve query plan? |