Re: index not always used when selecting on a date field - Mailing list pgsql-general

list-pgsql-general@news.cistron.nl ("Miquel van Smoorenburg" ) writes:
> techdb2=> explain select * from lines where (removed > CURRENT_DATE AND removed < '9999-01-01');

> With 7.3, this query used the index, while with 7.4 it doesn't.

Perhaps you hadn't ANALYZEd in 7.3?  AFAICS 7.3 and 7.4 behave
essentially alike on this point, given comparable statistics.

One thing I did notice in looking at this is that the preferential
treatment for range constraints only applies when *both* sides of the
range are un-estimatable.  So you need to write something like

WHERE (removed > CURRENT_DATE AND removed < CURRENT_DATE + 10000)

to get it to work nicely.  I'll see if I can improve on that for 8.0;
seems like the way you tried ought to work, too.

            regards, tom lane