From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ken Tanzer <ken.tanzer@gmail.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Reversing NULLS in ORDER causes index not to be used?
In-reply-to: 
 <CAD3a31XJF3vQ_a6YaKaxbdiaXa1f2RJmC07oOEh4YUp1Je392w@mail.gmail.com>
References: 
 <CAD3a31XJF3vQ_a6YaKaxbdiaXa1f2RJmC07oOEh4YUp1Je392w@mail.gmail.com>
Comments: In-reply-to Ken Tanzer <ken.tanzer@gmail.com>
	message dated "Fri, 18 Dec 2020 17:53:03 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <226298.1608343379.1@sss.pgh.pa.us>
Date: Fri, 18 Dec 2020 21:02:59 -0500
Message-ID: <226299.1608343379@sss.pgh.pa.us>
Precedence: bulk

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi.  I'm wondering if this is normal or at least known behavior?
> Basically, if I'm specifying a LIMIT and also  NULLS FIRST (or NULLS LAST
> with a descending sort), I get a sequence scan and a couple of orders of
> magnitude slower query.  Perhaps not relevantly, but definitely ironically,
> the sort field in question is defined to be NOT NULL.

The index won't get credit for matching the requested ordering if it's
got the wrong null-ordering polarity.  There's not an exception for
NOT NULL columns.  If you know the column hasn't got nulls, why are
you bothering with a nondefault null-ordering request?

			regards, tom lane