slow SELECT ... LIMIT query
От | Simon Stanlake |
---|---|
Тема | slow SELECT ... LIMIT query |
Дата | |
Msg-id | 000801c10fb1$fe6bf300$6400a8c0@localdomain обсуждение исходный текст |
Ответы |
Re: slow SELECT ... LIMIT query
Re: slow SELECT ... LIMIT query |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: