Why is "order by" on the primary key changing the query plan so that it ignores an useful index?

user_1315978 0
Viewed 99

After investigating why a multi-column index doesn't help speed up a query when I was expecting it to, I realized that it's because of a simple ORDER BY clause.

I reduced the query to this simple form (first without the ORDER BY, then with it):

somedb=# explain select * from user_resource where resource_id = 943 and status = 2 limit 10;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..39.29 rows=10 width=44)
   ->  Index Scan using user_resource_resource_id_status on user_resource  (cost=0.56..5422.22 rows=1400 width=44)
         Index Cond: ((resource_id = 943) AND (status = 2))
(3 rows)

Time: 0.409 ms
somedb=# explain select * from user_resource where resource_id = 943 and status = 2 order by id desc limit 10;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1000.46..4984.60 rows=10 width=44)
   ->  Gather Merge  (cost=1000.46..558780.31 rows=1400 width=44)
         Workers Planned: 2
         ->  Parallel Index Scan Backward using idx_121518_primary on user_resource  (cost=0.44..557618.69 rows=583 width=44)
               Filter: ((resource_id = 943) AND (status = 2))

Once I add the ORDER BY, you can see the user_resource_resource_id_status key is not used anymore and the query becomes ~10 times slower.

Why is this? And is there a way to fix it? I would think sorting by a simple integer field shouldn't make an index useless. Thank you.

query-performance database-indexes postgresql
2 Answers
user_33914900

It is related to the limit.

You can run the query without the limit clause and with an offset of 0 to prevent inlining the subquery, then apply the limit.

select * from (
   select * from user_resource 
   where resource_id = 943 
      and status = 2
   offset 0
) sub
 order by id desc 
limit 10;
user_76355690
Related