By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

index not always used when selecting on a date field

P: n/a
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 ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 ma*******@postgresql.org

Nov 23 '05 #2

P: n/a

Russell Smith <mr*****@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a
In article <87************@stark.xeocode.com>,
Greg Stark <gs*****@mit.edu> wrote:

Russell Smith <mr*****@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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

P: n/a
li****************@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

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

Nov 23 '05 #5

P: n/a
In article <11**************@sss.pgh.pa.us>,
Tom Lane <tg*@sss.pgh.pa.us> wrote:
li****************@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 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 ma*******@postgresql.org

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.