Re: why is the LIMIT clause slowing down this SELECT?

On 8/1/07, Mason Hale <masonhale(at)gmail(dot)com> wrote:
> 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

Something seems wrong here. The cost of the second plan adds up to
1685, the cost of the first plan adds up to 651.69 with an
intermediate step that adds up to 21219.08. ??? I thought the outer
parts of the plan always contained the inner parts? This doesn't make
sense.

If the inner cost really is 21219 then the planner should have
switched to the cheaper plan with a limit. If it thinks it's going to
be 651 total, then how come it's ignoring the cost of 21219?