Hi,
I have a fairly large table (1 million records) with the following
structure...
sampleid int4
unitid int4
datetimestamp timestamp
data1 float8
data2 float8
btree indexes on sampleid, unitid, and datetimestamp.
I want to be able to pull out the most recent record for a certain unit.
the query that seemed most reasonable was
SELECT * FROM MYTABLE WHERE UNITID = unit_id ORDER BY DATETIMESTAMP DESC
LIMIT 1;
some strange results...
1) for units that have a lot of records (100K), the query is reasonably fast
(~ 0.5 seconds) but for units with not too many records (100) the query is
REALLY SLOW, like 15 seconds. the explain plan says INDEX SCAN BACKWARDS so
I guess it is using the index.
2) when I take away the LIMIT 1 and run the query it returns the results
almost immediately no matter how many records the unit has.
It looks like the indexes are not being used properly when the LIMIT clause
is inserted. I didn't do anything fancy when I created the indexes, just
the standard CREATE INDEX statement. Ran VACUUM ANALYZE and everything.
Is this common when you use the LIMIT clause? Is there a work around?
Thanks,
Simon
