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

maxim.boguk@gmail.com writes:
> Index on the last three fields defined as:
>     "i_liexwebmasterproducts_2" btree (lwpwebsiteid, lwpnotforsale, lwpcreatedate)

> Target query and plan:
> select *
> from liexWebmasterProducts this_
> where
> this_.lwpWebsiteId=5935
> and this_.lwpnotForSale=FALSE
> order by this_.lwpCreateDate desc limit 1;

>  Limit  (cost=122.18..122.19 rows=1 width=902) (actual time=13.505..13.506
> rows=1 loops=1)
>    ->  Sort  (cost=122.18..124.57 rows=953 width=902) (actual
> time=13.503..13.503 rows=1 loops=1)
>          Sort Key: lwpcreatedate
>          Sort Method: top-N heapsort  Memory: 27kB
>          ->  Index Scan using i_liexwebmasterproducts_2 on
> liexwebmasterproducts this_  (cost=0.43..117.42 rows=953 width=902) (actual
> time=0.171..10.429 rows=1674 loops=1)
>                Index Cond: ((lwpwebsiteid = 5935) AND (lwpnotforsale =
> false))
>                Filter: (NOT lwpnotforsale)
>  Total runtime: 13.626 ms

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.

            regards, tom lane