> > The parser does know about a heirarchy
> > of built-in data types, and would not downgrade a float8 to an int4.
> OK, that's good. But if it knows that much, I'd expect it to be able
> to *upgrade* an int4 to a float8 where appropriate. That's not
> happening
Ah, but it _is_ happening. The problem lies later. Read on...
> , as in my prior example:
> >> play=> explain select x from f8 where x = 500;
> >> NOTICE: QUERY PLAN:
> >> Seq Scan on f8 (cost=40.00 size=100 width=8)
> It seems the type coercion will happen, but too late for the
> optimizer to notice.
This is the specific case discussed by Vadim, and is, I believe,
directly related to the
select city from locations where lower(city) = lower('st. ignace');
example given by Vince.
The parser is converting this query to become
select x from f8 where x = float8(500);
The problem appears to be that the optimizer/executor does not know how
to evaluate the constant string once-only, and insists on doing a
sequential scan for some reason. Not really in the parser's hands.
Remember, the types are all set and the functions are all defined by the
time the actual optimizer/executor sees anything. There may be a slight
bit of "optimization" which happens in the parser, but only to shoehorn
SQL92 into the Postgres backend. Oh, there are some heuristics in the
parser to fudge the distinctions between char/varchar/text types to
allow reuse of some of the support code, but that is a Bad Thing in
principle.
The downside to putting more heuristics into the parser (as opposed to
upgrading the optimizer/executor to handle the case) is that it embeds
more assumptions about the _behavior_ of, for example, int4->float8
conversions and would reduce the flexibility of the extensible type
system.
istm that we should be focusing on Vadim's hints on what it would take
to use indices with function calls on constants...
- Tom
