Re: [GENERAL] pgsql 7.x...

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