WHERE with ORDER not using the best index

Hello everyone,

I've a query that runs on a table with a matching index to its WHERE and
ORDER clause. However the planner never uses that index. Is there any reason
why it doesn't?

Here's the table:

db=> \d social_feed_feed_items;
                                         Table
"public.social_feed_feed_items"
      Column       |            Type             |
Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
 id                | integer                     | not null default
nextval('social_feed_feed_items_id_seq'::regclass)
 social_feed_id    | integer                     |
 social_message_id | integer                     |
 posted_at         | timestamp without time zone |
Indexes:
    "social_message_feed_feed_items_pkey" PRIMARY KEY, btree (id)
    "index_social_feed_feed_items_on_social_feed_id" btree (social_feed_id)
    "index_social_feed_feed_items_on_social_feed_id_and_posted_at" btree
(social_feed_id, posted_at DESC NULLS LAST)
    "index_social_feed_feed_items_on_social_message_id" btree
(social_message_id)
    "social_feed_item_feed_message_index" btree (social_feed_id,
social_message_id)

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:

db=> DROP INDEX index_social_feed_feed_items_on_social_feed_id;
DROP INDEX
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=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)

So my question is, without dropping
index_social_feed_feed_items_on_social_feed_id since it's needed by other
queries, how do I make the planner use
index_social_feed_feed_items_on_social_feed_id_and_posted_at for a much
faster performance? Why didn't the query look at the matching WHERE and
ORDER clause and only chose the WHERE to begin its plan?

db=> show SERVER_VERSION;
 server_version
----------------
 9.3.2
(1 row)

Thank you very much for your response(s).

Regards,
Ken



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/WHERE-with-ORDER-not-using-the-best-index-tp5789581.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.