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

On Mon, 8 Nov 2004 07:56 am, "Miquel van Smoorenburg" wrote:
> I have a database with a btree index on the 'removed' field,
> which is of type 'date'. However it isn't being used:
>
> techdb2=> explain select * from lines where removed > CURRENT_DATE;
>                          QUERY PLAN
> ------------------------------------------------------------
>  Seq Scan on lines  (cost=0.00..243.47 rows=2189 width=324)
>    Filter: (removed > ('now'::text)::date)
> (2 rows)
>
> Now the weird thing is that if I select a range it is being used:
>
> techdb2=> explain select * from lines where removed > CURRENT_DATE and removed < '9999-01-01';
>                                      QUERY PLAN
> ------------------------------------------------------------------------------------
>  Index Scan using lines_removed_idx on lines  (cost=0.00..120.56 rows=33 width=324)
>    Index Cond: ((removed > ('now'::text)::date) AND (removed < '9999-01-01'::date))
> (2 rows)
>
> Why is this?
>
> (Tested with both 7.3.2 and 7.4.6)
>
> Mike.
>
now() and CURRENT_DATE, are and cannot be planned as constants.
So the planner cannot use an index for them.

This have been covered on the list a number of times.  Until a solution is at hand,
you can either use constants instead of now, or create a immutable function that returns now.
However if you PREPARE those queries, you will not get the new time for now() each time you
run the query.

This function fits in a category between STABLE and IMMUTABLE, of which there is currently
no type.

Regards

Russell Smith