Re: Reversing NULLS in ORDER causes index not to be used? - Mailing list pgsql-performance
On Fri, Dec 18, 2020 at 6:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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?
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.
pgsql-performance by date: