• Download Gist
PostgreSQL Composite Indexes and Sorting on Multiple Columns
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
-- 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)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.