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

2018-02-02 Thread Nandakumar M
Hi, On Fri, Feb 2, 2018 at 9:28 PM, David G. Johnston wrote: > You probably can (I assume the nulls aspect of the index doesn't prevent PK > usage), but you must add the PK to the table after creating the index and > not let the system auto-generate the index for

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

2018-02-02 Thread David G. Johnston
On Fri, Feb 2, 2018 at 8:49 AM, Nandakumar M wrote: > But, for the PK column we are not in control of the index that is created. > ​You probably can (I assume the nulls aspect of the index doesn't prevent PK usage), but you must add the PK to the table after creating the

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

2018-02-02 Thread Nandakumar M
Hi, On Fri, Feb 2, 2018 at 8:30 PM, Tom Lane 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

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

2018-02-02 Thread Tom Lane
Nandakumar M writes: > The order by column has a not null constraint on it and so nulls last or > first shouldn't make any difference. 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

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

2018-02-02 Thread Nandakumar M
Hi, On 2 Feb 2018 15:06, "Laurenz Albe" wrote: >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

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

2018-02-02 Thread Laurenz Albe
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

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

2018-02-01 Thread Nandakumar M
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