On Sun, Feb 27, 2011 at 1:39 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > 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. Interesting. Worth a TODO? -- 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
