Will postgres index work for both directions with NULLS LAST?

user_10188661 0
Viewed 209

If I create index for nullable timestamp column due_date with DESC NULLS LAST, will then index also work for ASC NULLS LAST query?

Maybe it's will be just less performant? Or I need to create second index?

In postgres docs we can see that backward direction scan nulls became first:

An index stored in ascending order with nulls first can satisfy either ORDER BY x ASC NULLS FIRST or ORDER BY x DESC NULLS LAST depending on which direction it is scanned in.

postgresql
2 Answers

No; the index order must be identical or exactly opposite to the ORDER BY clause. A DESC NULLS LAST index will only work for ORDER BY ... DESC NULLS LAST or ORDER BY ... ASC NULLS FIRST.

user_64643080

Building two indexes, or just letting it do the sort, are currently the only clean solutions.

There is another unclean method which is to use UNION ALL.

(select * from foobar where due_date is not null order by due_date asc nulls first)
   union all 
(select * from foobar where due_date is null)

PostgreSQL would be within its rights to return these rows in a different order than you want, but currently it doesn't happen to do that.

user_17212390
Related