Create a gist now

Instantly share code, notes, and snippets.

-- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment