Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
-- 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
Something went wrong with that request. Please try again.