On Fri, Dec 18, 2020 at 6:03 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Ken Tanzer <ken.tan...@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 *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
