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.
It depends on, how you created the index. Example NULLS FIRST, ASC, DESC, NULLS FIRST, and/or NULLS LAST
Refer https://www.postgresql.org/docs/current/indexes-ordering.html, explains how to work with Indexes and ORDER BY
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;