In article <[EMAIL PROTECTED]>,
Greg Stark <[EMAIL PROTECTED]> wrote:
>
>Russell Smith <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
