Descending order does not utilize index #657
Description
Descending queries use NULLS LAST which does not allow them to utilize the same index as ascending order queries.
Query
Given this GraphQL query:
{
article (limit: 10, order_by: date_desc) {
link
}
}Index
create index on article (date);Using NULLS LAST
It produces this SQL:
EXPLAIN ANALYSE SELECT coalesce(json_agg((SELECT "e" FROM (SELECT "r"."link" AS "link") AS "e")), '[]')
FROM (SELECT "link" AS "link"
FROM "public"."article"
WHERE (('true') AND (('true') AND ('true')))
ORDER BY "date" DESC NULLS LAST
LIMIT ('10') :: int) AS "r"Which results in this explain:
Aggregate (cost=2402457.72..2402457.73 rows=1 width=32) (actual time=41347.092..41347.092 rows=1 loops=1)
-> Limit (cost=2402457.47..2402457.50 rows=10 width=92) (actual time=41346.861..41346.875 rows=10 loops=1)
-> Sort (cost=2402457.47..2458049.25 rows=22236712 width=92) (actual time=41346.861..41346.868 rows=10 loops=1)
Sort Key: article.date DESC NULLS LAST
Sort Method: top-N heapsort Memory: 26kB
-> Seq Scan on article (cost=0.00..1921930.12 rows=22236712 width=92) (actual time=0.202..37852.110 rows=22236711 loops=1)
SubPlan 1
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=10)
Planning time: 1.761 ms
Execution time: 41347.308 ms
Using NULLS FIRST
If you change the query to order by NULLS FIRST it does a backward scan using the same index.
EXPLAIN ANALYSE SELECT coalesce(json_agg((SELECT "e" FROM (SELECT "r"."link_web" AS "link_web") AS "e")), '[]')
FROM (SELECT "link_web" AS "link_web"
FROM "public"."acris_simple"
WHERE (('true') AND (('true') AND ('true')))
ORDER BY "recorded_date" DESC NULLS FIRST
LIMIT ('10') :: int) AS "r"Aggregate (cost=29.10..29.11 rows=1 width=32) (actual time=14.866..14.866 rows=1 loops=1)
-> Limit (cost=0.44..28.87 rows=10 width=92) (actual time=14.604..14.770 rows=10 loops=1)
-> Index Scan Backward using acris_simple_recorded_date_idx on acris_simple (cost=0.44..63225433.39 rows=22236712 width=92) (actual time=14.603..14.765 rows=10 loops=1)
SubPlan 1
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=10)
Planning time: 0.209 ms
Execution time: 14.917 ms
This is because Postgres can take advantage of the index forwards and backwards. See doc for more details: https://www.postgresql.org/docs/9.3/static/indexes-ordering.html