[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] NULLS LAST performance
From:       Mathieu De Zutter <mathieu () dezutter ! org>
Date:       2011-02-24 9:47:07
Message-ID: AANLkTinfUrhYOxiQs4-+xA95rjJEBwkLQRT9RsQPxJ__ () mail ! gmail ! com
[Download RAW message or body]

On Wed, Feb 23, 2011 at 10:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Merlin Moncure <mmoncure@gmail.com> writes:
> > you can always do this:
>
> > create index performance_creation_date_desc_idx on
> > performance(creation_date desc nulls last);
>
> > which will index optimize your sql.  Interesting that 'null last'
> > fools disallows index usage even when the index was created with
> > nullls last as the default.
>
> The problem is that his query needs to scan the index in DESC order,
> which means it's effectively NULLS FIRST, which doesn't match the
> requested sort order.
>

Merlin, Tom,

Thanks for explaining the behavior!

Any chance that the planner could get smarter about this? In my naive view,
it would just be telling the planner that it can disregard "NULLS" when
searching for an index, in case the column is known to be NOT NULL.

Kind regards,
Mathieu

[Attachment #3 (text/html)]

<div><br><div class="gmail_quote">On Wed, Feb 23, 2011 at 10:37 PM, Tom Lane <span \
dir="ltr">&lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;</span> \
wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex;">

<div class="im">Merlin Moncure &lt;<a \
href="mailto:mmoncure@gmail.com">mmoncure@gmail.com</a>&gt; writes:<br> &gt; you can \
always do this:<br> <br>
&gt; create index performance_creation_date_desc_idx on<br>
&gt; performance(creation_date desc nulls last);<br>
<br>
&gt; which will index optimize your sql.  Interesting that &#39;null last&#39;<br>
&gt; fools disallows index usage even when the index was created with<br>
&gt; nullls last as the default.<br>
<br>
</div>The problem is that his query needs to scan the index in DESC order,<br>
which means it&#39;s effectively NULLS FIRST, which doesn&#39;t match the<br>
requested sort order.<br></blockquote><div> </div></div></div><div><meta \
http-equiv="content-type" content="text/html; charset=utf-8">Merlin, \
Tom,<div><br></div><div>Thanks for explaining the behavior!</div><div><br></div>

<div>Any chance that the planner could get smarter about this? In my naive view, it \
would just be telling the planner that it can disregard &quot;NULLS&quot; when \
searching for an index, in case the column is known to be NOT NULL.</div>

<div><br></div><div>Kind regards,</div><div>Mathieu<br></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]