Re: WHERE with ORDER not using the best index

jugnooken <ken(at)jugnoo(dot)com> writes:
> Here's the query:

> db=> EXPLAIN ANALYSE SELECT social_feed_feed_items.social_message_id FROM
> social_feed_feed_items WHERE social_feed_feed_items.social_feed_id = 480
> ORDER BY posted_at DESC NULLS LAST LIMIT 1200;

> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=126.83..127.43 rows=1200 width=12) (actual time=10.321..13.694
> rows=1200 loops=1)
> -> Sort (cost=126.83..129.08 rows=4498 width=12) (actual
> time=10.318..11.485 rows=1200 loops=1)
> Sort Key: posted_at
> Sort Method: top-N heapsort Memory: 153kB
> -> Index Scan using index_social_feed_feed_items_on_social_feed_id
> on social_feed_feed_items (cost=0.09..76.33 rows=4498 width=12) (actual
> time=0.037..5.317 rows=4249 loops=1)
> Index Cond: (social_feed_id = 480)
> Total runtime: 14.913 ms
> (7 rows)

> I was hoping that they planner would use
> index_social_feed_feed_items_on_social_feed_id_and_posted_at, but it never
> does. If I manually remove the index that it currently uses then magic
> happens:

> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.09..998.63 rows=1200 width=12) (actual time=0.027..3.792
> rows=1200 loops=1)
> -> Index Scan using
> index_social_feed_feed_items_on_social_feed_id_and_posted_at on
> social_feed_feed_items (cost=0.09..3742.95 rows=4498 width=12) (actual
> time=0.023..1.536 rows=1200 loops=1)
> Index Cond: (social_feed_id = 480)
> Total runtime: 4.966 ms
> (4 rows)

Well, it likes the first plan because it's estimating that one as cheaper
;-). The question is why the indexscan cost is estimated so remarkably
high for the second index --- nearly two orders of magnitude more to
retrieve the same number of index entries. The most obvious explanation
is that that index is horribly bloated for some reason. Have you checked
the physical index sizes? If the second index is many times bigger,
REINDEX ought to help, though it's unclear whether the bloat will recur.

regards, tom lane