sort by indexed column speeds up query by 64%

[vgkids]

Before Default Activity Entries query takes ~224ms for an app with 100k rows

EXPLAIN
SELECT activity_entries . id,
 activity_entries . owner_id,
 activity_entries . owner_type,
 activity_entries . app_id,
 activity_entries . register_item_id,
 activity_entries . activity_type,
 activity_entries . status,
 activity_entries . duration_ms,
 activity_entries . created_at 
FROM activity_entries 
WHERE activity_entries . app_id = 579 
ORDER BY activity_entries . created_at DESC 
LIMIT 25;

Limit  (cost=90541.39..90541.40 rows=25 width=66)
  ->  Sort  (cost=90541.39..90593.47 rows=104148 width=66)
        Sort Key: created_at DESC
        ->  Bitmap Heap Scan on activity_entries  (cost=437.51..89953.60 rows=104148 width=66)
              Recheck Cond: (app_id = 579)
              ->  Bitmap Index Scan on index_activity_entries_on_app_id  (cost=0.00..432.31 rows=104148 width=0)
                    Index Cond: (app_id = 579)

After Default Activity Entries query for the same app takes ~80ms by leveraging the existing id index

EXPLAIN
SELECT activity_entries . id,
 activity_entries . owner_id,
 activity_entries . owner_type,
 activity_entries . app_id,
 activity_entries . register_item_id,
 activity_entries . activity_type,
 activity_entries . status,
 activity_entries . duration_ms,
 activity_entries . created_at 
FROM activity_entries 
WHERE activity_entries . app_id = 579 
ORDER BY activity_entries . id DESC
LIMIT 25;

Limit  (cost=0.08..35.86 rows=25 width=66)
  ->  Index Scan Backward using activity_entries_pkey on activity_entries  (cost=0.08..149052.43 rows=104148 width=66)
        Filter: (app_id = 579)