From: | "Mason Hale" <masonhale(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | why is the LIMIT clause slowing down this SELECT? |
Date: | 2007-08-01 20:56:44 |
Message-ID: | 8bca3aa10708011356j39f2330hfa0fb0aa4bfc80c0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrej Ricnik-Bay | 2007-08-01 21:15:40 | Re: Linux distro |
Previous Message | Barry C Dowell | 2007-08-01 20:39:48 | Re: Move database from Solaris to Windows |