[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: Reversing NULLS in ORDER causes index not to be used?
From: Ken Tanzer <ken.tanzer () gmail ! com>
Date: 2020-12-19 2:30:16
Message-ID: CAD3a31VF67_qmLE6dKXD=XMFF_BZGgTHaatpFabqPXFYj6AESQ () mail ! gmail ! com
[Download RAW message or body]
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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr">On Fri, Dec 18, 2020 at 6:03 PM Tom Lane <<a \
href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>> \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">Ken Tanzer <<a \
href="mailto:ken.tanzer@gmail.com" target="_blank">ken.tanzer@gmail.com</a>> \
writes:<br> > Hi. I'm wondering if this is normal or at least known \
behavior?<br> > Basically, if I'm specifying a LIMIT and also NULLS FIRST \
(or NULLS LAST<br> > with a descending sort), I get a sequence scan and a couple \
of orders of<br> > magnitude slower query. Perhaps not relevantly, but \
definitely ironically,<br> > the sort field in question is defined to be NOT \
NULL.<br> <br>
The index won't get credit for matching the requested ordering if it's<br>
got the wrong null-ordering polarity. There's not an exception for<br>
NOT NULL columns. If you know the column hasn't got nulls, why are<br>
you bothering with a nondefault null-ordering request?<br>
<br></blockquote><div><br></div><div>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.</div><div><br></div><div>I also see now that CREATE INDEX has NULLS FIRST/LAST \
options, which now makes perfect sense but was news to \
me.</div><div><br></div><div>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. \
:)</div><div><br></div><div>Thanks!</div><div><br></div><div>Ken</div><div><br></div><div><br></div></div><div><br></div>-- \
<br><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><img \
src="https://agency-software.org/agency_logo_medium.png"><div><font size="4">AGENCY \
Software </font><br><div>A Free Software data system</div><div>By and for \
non-profits</div><div><i><a href="http://agency-software.org/" \
target="_blank">http://agency-software.org/</a></i></div><div><i><a \
href="https://demo.agency-software.org/client" \
target="_blank">https://demo.agency-software.org/client</a></i><br></div><div><a \
href="mailto:ken.tanzer@agency-software.org" \
target="_blank">ken.tanzer@agency-software.org</a></div><div>(253) \
245-3801</div><div><br></div></div><div><a \
href="mailto:agency-general-request@lists.sourceforge.net?body=subscribe" \
target="_blank">Subscribe to the mailing list</a> to</div><div>learn more about \
AGENCY or</div><div>follow the \
discussion.</div></div></div></div></div></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic