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.
