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

In article <87mzxsjgo4(dot)fsf(at)stark(dot)xeocode(dot)com>,
Greg Stark <gsstark(at)mit(dot)edu> wrote:
>
>Russell Smith <mr-russ(at)pws(dot)com(dot)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.

Well, strangely enough, after checking once more, that works
with 7.3, but with 7.4 it doesn't.

techdb2=> explain select * from lines where (removed > CURRENT_DATE AND removed < '9999-01-01');
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on lines (cost=0.00..259.89 rows=2189 width=178)
Filter: ((removed > ('now'::text)::date) AND (removed < '9999-01-01'::date))
(2 rows)

With 7.3, this query used the index, while with 7.4 it doesn't.
Using an immutable function that returns CURRENT_DATE indeed
makes it work as I expected:

techdb2=> explain select * from lines where removed > today();
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using lines_removed_idx on lines (cost=0.00..4.85 rows=1 width=178)
Index Cond: (removed > '2004-11-08'::date)
(2 rows)

Thanks for the advice,

Mike.