Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ehcpK-0004Kq-J7
	for pgsql-performance@arkaria.postgresql.org; Fri, 02 Feb 2018 15:00:30 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ehcpJ-00071M-Bm
	for pgsql-performance@arkaria.postgresql.org; Fri, 02 Feb 2018 15:00:29 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1ehcpJ-00071C-4B
	for pgsql-performance@lists.postgresql.org; Fri, 02 Feb 2018 15:00:29 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1ehcpG-0004EK-KV
	for pgsql-performance@postgresql.org; Fri, 02 Feb 2018 15:00:28 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id w12F0GtC015988;
	Fri, 2 Feb 2018 10:00:16 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nandakumar M <m.nanda92@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-performance@postgresql.org
Subject: Re: Query optimiser is not using 'not null' constraint when 'order by
 nulls last' clause is used
In-reply-to: 
 <CANcFUu4YyAFYUYw2P=UGTJNo4TKFqSMOjnx5pi1apxiuTqeR+g@mail.gmail.com>
References: 
 <CANcFUu63MDBXZrnGoBML551z2aA0KC3erWSeH=E-2nmyOF3rYQ@mail.gmail.com>
 <1517564200.2452.10.camel@cybertec.at>
 <CANcFUu4YyAFYUYw2P=UGTJNo4TKFqSMOjnx5pi1apxiuTqeR+g@mail.gmail.com>
Comments: In-reply-to Nandakumar M <m.nanda92@gmail.com>
	message dated "Fri, 02 Feb 2018 19:34:30 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <15986.1517583616.1@sss.pgh.pa.us>
Date: Fri, 02 Feb 2018 10:00:16 -0500
Message-ID: <15987.1517583616@sss.pgh.pa.us>
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

Nandakumar M <m.nanda92@gmail.com> 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
nulls in the column, why are you insisting on specifying a nondefault
value of NULLS FIRST/LAST in the query?

			regards, tom lane