Re: index not always used when selecting on a date field

Russell Smith <mr-russ@pws.com.au> writes:

> now() and CURRENT_DATE, are and cannot be planned as constants.
> So the planner cannot use an index for them.

It's not that it cannot use an index, but that it doesn't know it should use
an index. The planner knows that it can't count on now() to be constant so it
doesn't use the value it has. As far as it's concerned you're comparing
against an unknown value. And in general the postgres optimizer assumes single
sided inequalities with unknown constants aren't selective enough to justify
an index scan.

The easiest work-around is probably just putting in a bogus second inequality
to make it a range. The planner generally assumes ranges are selective enough
to justify index scans.


--
greg