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

In article <11542.1099954811@sss.pgh.pa.us>,
Tom Lane  <tgl@sss.pgh.pa.us> wrote:
>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.

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
tableor 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.