Speeding up index scan backwards query
Your query must be using an index on &quot;id&quot; to scan the index in the implied order, and then filtering out everything where &quot;user_id&quot; does not equal 123, stopping after it finds 31 rows which survive the filter.  Going in one direction it quickly finds 31 such rows, going in the other direction needs to filter out a large number of rows before 31 survive (because none/few of the rows starting at that end have user_id=123).
You could readily confirm this theory by doing an EXPLAIN (ANALYZE, BUFFERS) of the queries.
This is not fundamentally about the order of the index scan.  If you picked a value for 123 which had the opposite property (they all occurred at the logical end of the index rather than the logical beginning) then the situation would be reversed. Specifying DESC would fix the problem, rather than causing it.

Any idea on what I can do to try and improve the speed of this query when sorting by DESC?

Your query seems pointless.  Counting is not an order-dependent activity.  This is probably not your real query.  So who knows if our suggestions would transfer over to your real query?  The most straighforward fix for this query would be to build a multicolumn index on (user_id, id).  Then no rows would get filtered out one by one, as they would be removed in wholesale through the operation of the index.