I fixed it by removing NULLS FIRST from the query (it was introduced by our query builder). However, it seems the query optimizer could account for such scenarios.
Additionally, as you mentioned, the default index is created with NULLS LAST, but in this case, the column is non-nullable, making NULLS LAST unnecessary as well.
Thank you,
Alexander.
PG Bug reporting form <noreply@postgresql.org> writes:
> A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if
> an ordering column is not nullable.
The reason it's performing poorly is that
ORDER BY updated_at NULLS FIRST
is not compatible with the sort order of your index (which is,
by default, NULLS LAST). So the query has to be done with an
explicit sort, which requires reading the whole table.
I know you are going to say that it shouldn't matter as long as the
column is marked NOT NULL, but too bad: it does. This is not a bug,
and it's not something we're likely to expend a great deal of sweat
on improving. If you know the column is null-free, why are you
writing NULLS FIRST? If you have a good reason to write NULLS FIRST,
why not declare the index to match?
regards, tom lane