postgres Poor performance on ORDER BY "id" DESC LIMIT 1

Trying to explain why there is difference in performance between the two queries.

This one: SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1 is satisfied by any one row with the matching object_id, so the index on object_id is a natural choice. The query requires minimal I/O: index scan to find the first matching value plus one heap read to fetch the entire row.

The alternative: SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1 requires all rows with the matching object_id be sorted by another column, id, then the row with the maximum value of id be returned. If you were to use the index on object_id you would need to perform the following operations: scan the index to find every matching object_id; for every match go fetch the actual row; then sort all fetched rows by id and return the one with the largest id.

The alternative chosen by the optimizer, presumably based on the object_id histogram, is: scan the index on id backwards, in its entirety; for each value go fetch the row and check if the value of object_id matches; return the first matching row, which will have the maximum possible id value. This alternative avoids sorting the rows, so I guess the optimizer prefers it to using the index on object_id.

The presence of an index on (object_id asc, id desc) allows for yet another alternative: scan this new index for the first entry matching the supplied object_id value, which by definition will have the highest id value; go fetch one matching row and return. Obviously, this is the most efficient approach.

To optimize query

SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;

I have done following

SELECT * FROM 
    (SELECT * FROM "items" 
     WHERE "object_id" = '123'
     ORDER BY "id" DESC) AS "items" 
ORDER BY "id" DESC LIMIT 1;

It helped without adding index (object_id asc, id desc) which suggested by @mustaccio.

# EXPLAIN SELECT * FROM 
    (SELECT * FROM "items" 
     WHERE "object_id" = '123'
     ORDER BY "id" DESC) AS "items" 
ORDER BY "id" DESC LIMIT 1;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=16629.84..16629.86 rows=1 width=59)
   ->  Sort  (cost=16629.84..16640.44 rows=4239 width=59)
         Sort Key: items.id
         ->  Bitmap Heap Scan on items  (cost=125.42..16374.45 rows=4239 width=59)
               Recheck Cond: (object_id = 123::bigint)
                   ->  Bitmap Index Scan on items_object_id_idx  (cost=0.00..124.36 rows=4239 width=0)
                     Index Cond: (object_id = 123::bigint)
(7 rows)