why is the LIMIT clause slowing down this SELECT?
От | Mason Hale |
---|---|
Тема | why is the LIMIT clause slowing down this SELECT? |
Дата | |
Msg-id | 8bca3aa10708011356j39f2330hfa0fb0aa4bfc80c0@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: why is the LIMIT clause slowing down this SELECT?
Re: why is the LIMIT clause slowing down this SELECT? |
Список | pgsql-general |
On a 8.1.9 version database that has been recently vacuumed and analyzed, I'm seeing some dramatic performance degradation if a limit clause is included in the query. This seems counter-intuitive to me. Here's the query and explain plan WITH the LIMIT clause: SELECT * FROM topic_feed WHERE topic_id = 106947234 ORDER BY score DESC LIMIT 25 Limit (cost=0.00..651.69 rows=25 width=29) (actual time=72644.652..72655.029 rows=25 loops=1) -> Index Scan Backward using topic_feed_score_index on topic_feed (cost=0.00..21219.08 rows=814 width=29) (actual time=72644.644..72654.855 rows=25 loops=1) Filter: (topic_id = 106947234) Total runtime: 72655.733 ms ============== and now WITHOUT the LIMIT clause: SELECT * FROM topic_feed WHERE topic_id = 106947234 ORDER BY score DESC Sort (cost=1683.75..1685.78 rows=814 width=29) (actual time=900.553..902.267 rows=492 loops=1) Sort Key: score -> Bitmap Heap Scan on topic_feed (cost=7.85..1644.40 rows=814 width=29) (actual time=307.900..897.993 rows=492 loops=1) Recheck Cond: (topic_id = 106947234) -> Bitmap Index Scan on index_topic_feed_on_topic_id_and_feed_id (cost=0.00..7.85 rows=814 width=0) (actual time=213.205..213.205 rows=2460 loops=1) Index Cond: (topic_id = 106947234) Total runtime: 904.049 ms ----------------------------------------- That's a pretty big delta (72.6 sec vs. 0.9 sec), and I would expect the query with the limit to be faster. Can anyone explain why this happens and what I can do about it? thanks in advance, Mason
В списке pgsql-general по дате отправления: