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

In article <11542(dot)1099954811(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>list-pgsql-general(at)news(dot)cistron(dot)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.

Well, my problem has been solved by using an immutable function
that returns CURRENT_DATE (thanks for the support!), but this
suggestion doesn't work for me:

techdb2=> vacuum;
WARNING: skipping "pg_shadow" --- only table or database owner can vacuum it
WARNING: skipping "pg_database" --- only table or database owner can vacuum itWARNING: skipping "pg_group" --- only table or database owner can vacuum it
VACUUM
techdb2=> explain select * from lines WHERE (removed > CURRENT_DATE AND removed < CURRENT_DATE + 10000);
QUERY PLAN
-------------------------------------------------------------------------------------------
Seq Scan on lines (cost=0.00..292.71 rows=3125 width=179)
Filter: ((removed > ('now'::text)::date) AND (removed < (('now'::text)::date + 10000)))
(2 rows)

Still a sequential scan. Yes, there is an index and it can be used:

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

Mike.