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 j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
