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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
