Re: Reversing NULLS in ORDER causes index not to be used?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Dec 18, 2020 at 6:03 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> 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?


I didn't write the query.  I was just trying to troubleshoot one (an d not the one I sent--that was a simplified example).  In this case it didn't matter.  It just hadn't ever occurred to me that NULLS FIRST/LAST could have performance impacts, and I couldn't see why.

I also see now that CREATE INDEX has NULLS FIRST/LAST options, which now makes perfect sense but was news to me.

Still though is there no optimization gain to be had for being able to handle nulls either first or last in an index?  I blissfully know nothing about how such things _actually_ work, but since they're all together at either the beginning or the end, it seems like there'd be at most one skip in the order of the values to account for, which seems like in many cases would be better than not using an index at all.  But there's probably good reasons why that doesn't hold water. :)

Thanks!

Ken



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux