Re: Bad query plan when the wrong data type is used

On Sun, Feb 27, 2011 at 1:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus <josh@agliodbs.com> 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