On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus <josh@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
