Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used

On Thu, 2018-02-01 at 20:00 +0530, Nandakumar M wrote:
> Hi,
>
> I am using Postgres version 9.4.4 on a Mac machine.
> I have 2 queries that differ only in the order by clause.
> One of it has 'nulls last' and the other one does not have it.
> The performance difference between the two is considerable.
>
> The slower of the two queries is
>
> SELECT [...]
> FROM workorder wo
> left join workorder_fields wof
> ON wo.workorderid=wof.workorderid
> left join servicecatalog_fields scf
> ON wo.workorderid=scf.workorderid
[...]
> ORDER BY 7 DESC nulls last limit 25
>
>
>
> On removing 'nulls last' from the order by clause the query becomes very fast.
> I have attached the query plan for both the queries.

In the above case, the optimizer does not know that it will get the rows
in the correct order: indexes are sorted ASC NULLS LAST by default,
so a backwards index scan will produce the results NULLS FIRST,
which is the default for ORDER BY ... DESC.

If you want the nulls last, PostgreSQL has to retrieve *all* the rows and sort
them rather than using the first 25 results it gets by scanning then indexes.

To have the above query perform fast, add additional indexes with either
ASC NULLS FIRST or DESC NULLS LAST for all used keys.

Yours,
Laurenz Albe