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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus <josh(at)agliodbs(dot)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?

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +