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

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.

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

Russell Smith <mr-russ@pws.com.au> 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.


--
greg

In article <87mzxsjgo4.fsf@stark.xeocode.com>,
Greg Stark  <gsstark@mit.edu> wrote:
>
>Russell Smith <mr-russ@pws.com.au> 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.

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.

            regards, tom lane

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.