Re: BUG #18477: A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if an ordering column is n

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.

чт, 23 мая 2024 г. в 20:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> PG Bug reporting form <noreply(at)postgresql(dot)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
>