Re: Query optimiser is not using 'not null' constraint when 'order bynulls last' clause is used - Mailing list pgsql-performance

Hi,

On Fri, Feb 2, 2018 at 8:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> The planner does not consider this and it doesn't really seem like
> something worth expending cycles on.  If you know that there won't be
> nulls in the column, why are you insisting on specifying a nondefault
> value of NULLS FIRST/LAST in the query?

The query is generated by a framework that adds 'nulls last' to all
order by clause.

This is done apparently to provide common behaviour in our application
irrespective of the database that is used.
SQL server treats nulls as lesser than non null values which is
opposite to what Postgres does.

For any indexes that we create manually, we can do a

--> create index on table_name(column_name nulls first);

But, for the PK column we are not in control of the index that is created.

Regards,
Nanda