Re: BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit)

On Fri, Feb 7, 2014 at 12:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> As a workaround you could do
>
> select *
> from liexWebmasterProducts this_
> where
> this_.lwpWebsiteId=5935
> and this_.lwpnotForSale=FALSE
> order by this_.lwpnotForSale desc, this_.lwpCreateDate desc limit 1;
>
> The problem is that "this_.lwpnotForSale=FALSE" is canonicalized into
> "NOT this_.lwpnotForSale", and then the ORDER BY machinery fails to
> realize that that makes the index column a no-op for ordering purposes.
> It does work as you're expecting for index columns of non-boolean types.
>
> I'll see about fixing this, but considering that it's worked like that
> since about 8.1 without complaints, I don't think I'll risk back-patching
> the change.

+1 for fixing this. From my practice people face this issue quite
often. In the most of the cases it can be solved by just creating a
partial index based on boolean condition, but time from time it can
not, some time bringing a huge head ache.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com