On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Laszlo,
>
>> Which is silly. I think that PostgreSQL converts the int side to a
>> float, and then compares them.
>>
>> It would be better to do this, for each item in the loop:
>>
>> * evaluate the right side (which is float)
>> * tell if it is an integer or not
>> * if not an integer, then discard the row immediately
>> * otherwise use its integer value for the index scan
>
> Not terribly likely, I'm afraid. Data type coercion is *way* more
> complex than you realize (consider the number of data types we have, and
> the ability to add UDTs, and then square it). And the functionality you
> propose would break backwards compatibility; many people currently use
> ".0" currently in order to force a coercion to Float or Numeric.
>
> I'm not saying that PostgreSQL couldn't do better on this kind of case,
> but that doing better is a major project, not a minor one.
Specifically, the problem is that x = 4.0, where x is an integer, is
defined to mean x::numeric = 4.0, not x = 4.0::integer. If it meant
the latter, then testing x = 3.5 would throw an error, whereas what
actually happens is it just returns false.
Now, in this particular case, we all know that the only way x::numeric
= 4.0 can be true is if x = 4::int. But that's a property of the
numeric and integer data types that doesn't hold in general. Consider
t = 'foo'::citext, where t has type text. That could be true if t =
'Foo' or t = 'foO' or t = 'FOO', etc.
We could fix this by adding some special case logic that understands
properties of integers and numeric values and optimizes x =
4.0::numeric to x = 4::int and x = 3.5::numeric to constant false.
That would be cool, in a way, but I'm not sure it's really worth the
code it would take, unless it falls naturally out of some larger
project in that area.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company