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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Mon, 8 Nov 2004 07:56 am, "Miquel van Smoorenburg" wrote:
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 majordomo (AT) postgresql (DOT) org

Russell Smith <mr-russ (AT) pws (DOT) com.au> writes:

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

In article <87mzxsjgo4.fsf (AT) stark (DOT) xeocode.com>,
Greg Stark <gsstark (AT) mit (DOT) edu> wrote:
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 majordomo (AT) postgresql (DOT) org)

list-pgsql-general (AT) news (DOT) cistron.nl ("Miquel van Smoorenburg" ) writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

In article <11542.1099954811 (AT) sss (DOT) pgh.pa.us>,
Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
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 table or 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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org