My app is making the following psql query, and it is running extremely slow:
SELECT COUNT(*) FROM ( SELECT 1 AS one FROM "large_table" WHERE "large_table"."user_id" = 123 ORDER BY "large_table"."id" desc LIMIT 1 OFFSET 30 ) subquery_for_count;
When I change the
ORDER BY to
ASC, it runs like 100x quicker. I have the default primary key index on id, and I've experimented with adding an additional index for the id in desc order, however it didn't seem to make a difference.
When I run Explain Analyze, I see that it is using an index scan backwards on the slow query (
desc). I tried manually disabling index scans for my session, and found that the query ran in 40seconds instead of 2 minutes, which is a noticeable improvement.
Any idea on what I can do to try and improve the speed of this query when sorting by DESC? I've read that for b-tree indices, it should generally give you the same performance irregardless of sort order, but that does not seem to be the case.