Robert Haas <robertmhaas@xxxxxxxxx> writes: > On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus <josh@xxxxxxxxxxxx> wrote: >> 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. > 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. I think that most of the practical problems around this case could be solved without such a hack. What we should do instead is invent cross-type operators "int = numeric" etc and make them members of both the integer and numeric index opclasses. There are reasons why that wouldn't work for integer versus float (read the last section of src/backend/access/nbtree/README) but right offhand it seems like it ought to be safe enough for numeric. Now, it wouldn't be quite as fast as if we somehow downconverted numeric to integer beforehand, but at least you'd only be talking about a slow comparison operator and not a fundamentally stupider plan. That's close enough for me, for what is in the end a stupidly written query. Of course, the above is still not exactly a small project, since you'd be talking about something like 36 new operators to cover all of int2, int4, int8. But it's a straightforward extension. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
