Thread: Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

> > >  btw, how I can find 'something' older than a month
> >   select * from titles
> >    where adate::date < 'today'::Date - '1 month'::timespan;
> this problem doesn't works:
>  apod=> select * from titles
>  apod-> where adate::date < 'today'::Date - '1 month'::timespan;
> ERROR:  There is more than one possible operator '<'
>         for types 'date' and 'datetime'
> but if I specify Datetime instead of Date it works, but still doesn't
> use index.
>  apod=> explain select * from titles
>  apod-> where adate::datetime < 'today'::Datetime
>  apod->  - '1 month'::timespan;
> NOTICE:  QUERY PLAN:
> Seq Scan on titles  (cost=64.10 size=466 width=28)

OK, try
select * from titles where adate < date('today'::Datetime - '1 month'::timespan);

although there may (still) be problems with Postgres recognizing that
it could use an index when the "constant" is an expression.

Let us know what you find out...
                      - Tom

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> OK, try
>
>  select * from titles
>   where adate < date('today'::Datetime - '1 month'::timespan);
>
> although there may (still) be problems with Postgres recognizing that
> it could use an index when the "constant" is an expression.

I'm afraid I can already predict the answer: the optimizer only knows
how to use an index to constrain the scan when it finds a WHERE clause
like "var op constant" or "constant op var".  What you've got there
isn't a constant.

The right solution, of course, is to put in a rewrite phase that does
constant-expression folding (probably after any rule-generated changes).
We've talked about that before, but it ain't gonna happen for 6.5.

BTW, the original question was why "where adate::date < 'today'::date"
wouldn't work.  What the optimizer sees in that case iswhere function(var) < constant
so it doesn't know how to use an index for that either.  Now, if you
had a functional index matching the function, it would know what to do.
But it'd be pretty silly to keep a separate functional index just to let
this work, seeing as how adate is already a date.

It might be nice if the parser could drop dummy type conversions
instead of leaving them as functions in the parse tree... although
doing that as part of a general constant-expression folder is probably
a better answer.
        regards, tom lane

On Tue, 4 May 1999, Thomas Lockhart wrote:

> Date: Tue, 04 May 1999 13:12:07 +0000
> From: Thomas Lockhart <lockhart@alumni.caltech.edu>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: Postgres Hackers List <hackers@postgreSQL.org>
> Subject: Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?
> 
> > > >  btw, how I can find 'something' older than a month
> > >   select * from titles
> > >    where adate::date < 'today'::Date - '1 month'::timespan;
> > this problem doesn't works:
> >  apod=> select * from titles
> >  apod-> where adate::date < 'today'::Date - '1 month'::timespan;
> > ERROR:  There is more than one possible operator '<'
> >         for types 'date' and 'datetime'
> > but if I specify Datetime instead of Date it works, but still doesn't
> > use index.
> >  apod=> explain select * from titles
> >  apod-> where adate::datetime < 'today'::Datetime
> >  apod->  - '1 month'::timespan;
> > NOTICE:  QUERY PLAN:
> > Seq Scan on titles  (cost=64.10 size=466 width=28)
> 
> OK, try
> 
>  select * from titles
>   where adate < date('today'::Datetime - '1 month'::timespan);
> 
> although there may (still) be problems with Postgres recognizing that
> it could use an index when the "constant" is an expression.
> 
> Let us know what you find out...

No, it's  doing Seq Scan. I checked with 6.4.2 and current 6.5 cvs
Oleg

> 
>                        - Tom
> 
> -- 
> Thomas Lockhart                lockhart@alumni.caltech.edu
> South Pasadena, California
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83