Re: NULLS LAST performance

On Feb 24, 2011, at 3:47 AM, Mathieu De Zutter wrote:
> > 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.

Unfortunately, I don't think the planner actually has that level of knowledge.

A more reasonable fix might be to teach the executor that it can do 2 scans of the index: one to get non-null data and a second to get null data. I don't know if the use case is prevalent enough to warrant the extra code though.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net