PostgreSQL Composite Indexes and Sorting on Multiple Columns

-- Order by one indexed column (FAST)
newsdesk_production=# explain analyze select * from pressreleases order by published_at DESC limit 100;
                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..249.91 rows=100 width=1207) (actual time=26.070..716.453 rows=100 loops=1)
   ->  Index Scan Backward using pressreleases_published_at_index on pressreleases  (cost=0.00..964766.62 rows=386042 width=1207) (actual time=26.067..716.343 rows=100 loops=1)
 Total runtime: 716.709 ms
(3 rows)

- Order by two separately indexed columns (SLOW)
newsdesk_production=# explain analyze select * from pressreleases order by published_at DESC, id DESC limit 100;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=73343.67..73343.92 rows=100 width=1207) (actual time=6644.005..6644.024 rows=100 loops=1)
   ->  Sort  (cost=73343.67..74308.77 rows=386042 width=1207) (actual time=6644.004..6644.012 rows=100 loops=1)
         Sort Key: published_at, id
         Sort Method:  top-N heapsort  Memory: 170kB
         ->  Seq Scan on pressreleases  (cost=0.00..58589.42 rows=386042 width=1207) (actual time=0.028..6445.128 rows=385821 loops=1)
 Total runtime: 6644.102 ms
(6 rows)

-- Creating composite index
newsdesk_production=# create index pressreleases_id_published_at_index on pressreleases (published_at, id);
CREATE INDEX

- Order by two columns in composite index (FAST)
newsdesk_production=# explain analyze select * from pressreleases order by published_at DESC, id DESC limit 100;
                                                                                    QUERY PLAN                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..267.80 rows=100 width=1207) (actual time=0.145..1.955 rows=100 loops=1)
   ->  Index Scan Backward using pressreleases_id_published_at_index on pressreleases  (cost=0.00..1033215.58 rows=385821 width=1207) (actual time=0.143..1.926 rows=100 loops=1)
 Total runtime: 2.009 ms
(3 rows)

- Order by two columns in composite index but in the wrong order (SLOW)
newsdesk_production=# explain analyze select * from pressreleases order by id DESC, published_at DESC limit 100;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=73333.01..73333.26 rows=100 width=1207) (actual time=532.632..532.666 rows=100 loops=1)
   ->  Sort  (cost=73333.01..74297.56 rows=385821 width=1207) (actual time=532.631..532.649 rows=100 loops=1)
         Sort Key: id, published_at
         Sort Method:  top-N heapsort  Memory: 177kB
         ->  Seq Scan on pressreleases  (cost=0.00..58587.21 rows=385821 width=1207) (actual time=0.008..407.786 rows=385821 loops=1)
 Total runtime: 532.842 ms
(6 rows)