Thread: Index not being used
Howdy, I have a table like: CREATE TABLE sometable (cdate date, ....); and CREATE INDEX sometable_cdate on sometable (cdate); Question is, why does the following use the index EXPLAIN SELECT cdate FROM sometable WHERE cdate = '02/14/2002'; but this does not EXPLAIN SELECT cdate FROM sometable WHERE cdate = current_date; I would like to make the SQL call dynamic and not have to make my code do the work. Thanks for any help, Matthew ________________________________________________________________ GET INTERNET ACCESS FROM JUNO! Juno offers FREE or PREMIUM Internet access for less! Join Juno today! For your FREE software, visit: http://dl.www.juno.com/get/web/.
Matthew: Please post your PostgreSQL version, and the actual explain output ofboth queries. If you are using Postgres < 7.1.2, I'llwarn you thatthe advice you get is likely to be "upgrade!" -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
>Please post your PostgreSQL version, and the actual explain output of >both queries. EXPLAIN SELECT cdate FROM sometable WHERE cdate = current_date; NOTICE: QUERY PLAN: Seq Scan on sometable (cost=0.00..332.70 rows=71 width=186) EXPLAIN SELECT cdate FROM sometable WHERE cdate = '02/14/2002'; NOTICE: QUERY PLAN: Index Scan using sometable_cdate on sometable (cost=0.00..66.64 rows=71 width=186) My postgres version is 7.1.3 Again, thanks for any help Matthew Price ________________________________________________________________ GET INTERNET ACCESS FROM JUNO! Juno offers FREE or PREMIUM Internet access for less! Join Juno today! For your FREE software, visit: http://dl.www.juno.com/get/web/.
Matthew, 1. Is field cdate DATE or TIMESTAMP? 2. What happens with: EXPLAIN SELECT cdate FROM sometable WHERE cdate = '2001-02-14'::DATE orTIMESTAMP? 3. Please time both queries and give the number of resulting rows fromboth queries. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Thu, 14 Feb 2002, Josh Berkus wrote: > Matthew, > > 1. Is field cdate DATE or TIMESTAMP? > 2. What happens with: > EXPLAIN SELECT cdate FROM sometable WHERE cdate = '2001-02-14'::DATE or > TIMESTAMP? > 3. Please time both queries and give the number of resulting rows from > both queries. Something tells me that current_date is probably not in a form that's considered indexable. You could probably get around that with an iscachable function hiding it however.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Something tells me that current_date is probably not in a form that's
> considered indexable.
More to the point, it's not considered a constant.
You could writeWHERE cdate = date 'now'
instead.
regards, tom lane
> More to the point, it's not considered a constant. Why? Shouldn't it be constant for the duration of a transaction? > You could write > WHERE cdate = date 'now' > instead. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> More to the point, it's not considered a constant.
> Why? Shouldn't it be constant for the duration of a transaction?
There is no such concept, at present. See the archives...
regards, tom lane
> There is no such concept, at present. See the archives... Then why does this work?: australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:35+08 (1 row) australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:36+08 (1 row) australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:37+08 (1 row) australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:38+08 (1 row) australia=# begin transaction; BEGIN australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:43+08 (1 row) australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:43+08 (1 row) australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:43+08 (1 row) australia=# select current_timestamp; timestamp ------------------------2002-02-15 12:03:43+08 (1 row)