On Mon, Feb 28, 2011 at 02:04:53PM -0500, Robert Haas wrote: > 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? Since we are discussing int2 casting, I wanted to bring up this other casting issue from 2011, in case it helped the discussion. -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
