How to create an index properly for DESC NULLS LAST sorting

Question

I created an index without specifying ordering or nulls first/last e.g.:

CREATE INDEX index_name ON TABLE table_name (date)

and now my ORDER BY DESC NULLS LAST queries run extremely slowly.

I read in PostgreSQL documentation, that

NULLS FIRST Specifies that nulls sort before non-nulls. This is the default when DESC is specified.

NULLS LAST Specifies that nulls sort after non-nulls. This is the default when DESC is not specified.

therefore if I create an index like this (for col date):

CREATE INDEX index_name ON TABLE table_name (date DESC NULLS LAST)

will I get a serious performance gain for queries like

SELECT * FROM table_name ORDER BY date DESC NULLS LAST LIMIT 50 OFFSET 0

?

Answer 1

From the postgres 9.6 documentation

The NULLS options are useful if you need to support "nulls sort low" behavior, rather than the default "nulls sort high", in queries that depend on indexes to avoid sorting steps.

If you do not provide the NULLS LAST on the index the query planner will most likely need to sort the records before returning them which could cause a huge performance drop if the table contains enough records.