Thread: pgsql 7.x...
will this function/index problem be fixed in 7.x ? ircbot=> explain select * from logins where dttime = NOW(); NOTICE: QUERY PLAN: Seq Scan on logins (cost=33530.89 rows=71043 width=52) EXPLAIN ircbot=> explain select * from logins where dttime = NOW()::datetime; NOTICE: QUERY PLAN: Seq Scan on logins (cost=33530.89 rows=71043 width=52) EXPLAIN ircbot=> select now(); now ---------------------- 1999-12-27 00:23:17-05 (1 row) ircbot=> explain select * from logins where dttime='1999-12-27 00:23:17-05'::datetime; NOTICE: QUERY PLAN: Index Scan using logins_dttime_idx on logins (cost=2.54 rows=11 width=52) EXPLAIN ( logins actually has 755,728 rows right now ) --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "I've learned that you cannot make someone love you. All you can do is stalk them and hope they panic and give in."
Howie wrote: > will this function/index problem be fixed in 7.x ? > > ircbot=> explain select * from logins where dttime = NOW(); > NOTICE: QUERY PLAN: > > Seq Scan on logins (cost=33530.89 rows=71043 width=52) > EXPLAIN > ircbot=> explain select * from logins where dttime = NOW()::datetime; > NOTICE: QUERY PLAN: > > Seq Scan on logins (cost=33530.89 rows=71043 width=52) > > EXPLAIN > ircbot=> select now(); > now > ---------------------- > 1999-12-27 00:23:17-05 > (1 row) > > ircbot=> explain select * from logins where dttime='1999-12-27 > 00:23:17-05'::datetime; > NOTICE: QUERY PLAN: > > Index Scan using logins_dttime_idx on logins (cost=2.54 rows=11 width=52) > > EXPLAIN > > ( logins actually has 755,728 rows right now ) > I realize this doesn't actually answer your question, but you could always do: SELECT * from logins WHERE dttime='now'; Example: emptoris=> explain select * from sales where saledate = now(); NOTICE: QUERY PLAN: Seq Scan on sales (cost=75556.68 rows=134187 width=140) EXPLAIN emptoris=> select 'now'::datetime; ?column? ---------------------------- Mon Dec 27 03:09:58 1999 EST (1 row) emptoris=> explain select * from sales where saledate = 'now'::datetime; NOTICE: QUERY PLAN: Index Scan using k_sales4 on sales (cost=2.80 rows=17 width=140) EXPLAIN emptoris=> explain select * from sales where saledate='now'; NOTICE: QUERY PLAN: Index Scan using k_sales4 on sales (cost=2.80 rows=17 width=140) EXPLAIN emptoris=> Hope that helps, Mike Mascari
On Sun, 26 Dec 1999, Mike Mascari wrote: > Howie wrote: > > > will this function/index problem be fixed in 7.x ? > > > > ircbot=> explain select * from logins where dttime = NOW(); > [SNIP] > emptoris=> explain select * from sales where saledate = 'now'::datetime; > NOTICE: QUERY PLAN: > > Index Scan using k_sales4 on sales (cost=2.80 rows=17 width=140) > > EXPLAIN > emptoris=> explain select * from sales where saledate='now'; > NOTICE: QUERY PLAN: > > Index Scan using k_sales4 on sales (cost=2.80 rows=17 width=140) > [SNIP] not really; just confuses me a bit more. is 'now()' not the same datatype as 'now' ? ircbot=> select now(),'now'::datetime,now()::datetime; now |?column? |datetime ----------------------+----------------------------+---------------------------- 1999-12-27 04:25:35-05|Mon Dec 27 04:25:35 1999 EST|Mon Dec 27 04:25:35 1999 EST (1 row) ircbot=> explain select * from logins where dttime = now()::datetime; Seq Scan on logins (cost=33530.89 rows=71043 width=52) ircbot=> explain select * from logins where dttime = 'now'::datetime; Index Scan using logins_dttime_idx on logins (cost=2.54 rows=11 width=52) ircbot=> select now()::datetime = 'now'::datetime; ?column? -------- t isnt 'NOW()' supposed to return a datetime by default? regardless, shouldnt 'now()::datetime' be a datetime ? if so, why isnt my index on dttime being used when its a direct comparison ? --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "I've learned that you cannot make someone love you. All you can do is stalk them and hope they panic and give in."
Howie wrote: > ...[other stuff]... > > ircbot=> select now(),'now'::datetime,now()::datetime; > now |?column? |datetime > ----------------------+----------------------------+---------------------------- > 1999-12-27 04:25:35-05|Mon Dec 27 04:25:35 1999 EST|Mon Dec 27 04:25:35 1999 EST > (1 row) > > ircbot=> explain select * from logins where dttime = now()::datetime; > Seq Scan on logins (cost=33530.89 rows=71043 width=52) > > ircbot=> explain select * from logins where dttime = 'now'::datetime; > Index Scan using logins_dttime_idx on logins (cost=2.54 rows=11 width=52) > > ircbot=> select now()::datetime = 'now'::datetime; > ?column? > -------- > t > > isnt 'NOW()' supposed to return a datetime by default? regardless, > shouldnt 'now()::datetime' be a datetime ? if so, why isnt my index on > dttime being used when its a direct comparison ? > My guess is that the optimizer is viewing now() as a function which initially cannot be reduced to a constant and therefore cannot be used for an index scan. Alternatively 'now' is a constant expression which can be coerced before the index scan to a datetime and thus can be used. I *think* PostgreSQL came to this point to support the following: sd=> create table example ( sd-> id int4 not null, sd-> dttime datetime not null default 'now'); CREATE sd=> insert into example (id) values (0); INSERT 40107 1 sd=> insert into example (id) values (1); INSERT 40108 1 sd=> select * from example; id|dttime --+---------------------------- 0|Mon Dec 27 04:50:29 1999 EST 1|Mon Dec 27 04:50:29 1999 EST (2 rows) Notice that using 'now', having been reduced to the current date, inserts the creation time of the table into every record. This is probably not what the user intended. But with now(): sd=> create table example ( sd-> id int4 not null, sd-> dttime datetime not null default now()); CREATE sd=> insert into example (id) values (0); INSERT 40120 1 sd=> insert into example (id) values (1); INSERT 40121 1 sd=> select * from example; id|dttime --+---------------------------- 0|Mon Dec 27 04:52:05 1999 EST 1|Mon Dec 27 04:52:10 1999 EST (2 rows) Since now() cannot get reduced to a constant expression, it allows the default clause of CREATE TABLE to function properly. I suppose that was the reasoning behind the difference... Mike Mascari