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

maxim(dot)boguk(at)gmail(dot)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