From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> |
Cc: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>, Postgres Hackers List <hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ? |
Date: | 1999-05-04 14:32:11 |
Message-ID: | 29725.925828331@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)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 is
where 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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 1999-05-04 14:40:40 | Re: [HACKERS] varchar-array.patch applied |
Previous Message | Thomas Lockhart | 1999-05-04 14:19:45 | Re: [HACKERS] numeric data type on 6.5 |