To: Robert Haas <robertmhaas@gmail.com>
cc: Josh Berkus <josh@agliodbs.com>, Laszlo Nagy <gandalf@shopzeus.com>,
	pgsql-performance@postgresql.org, Daniel Fekete <danieleff@gmail.com>
Subject: Re: Bad query plan when the wrong data type is used 
In-reply-to: <AANLkTim3q+L0=tmipSQ8nE5JRt3ay8Gcy-LgxHtbZTu8@mail.gmail.com> 
References: <4D514FFF.9060308@shopzeus.com> <4D51BE0B.1060404@agliodbs.com>
	<AANLkTim3q+L0=tmipSQ8nE5JRt3ay8Gcy-LgxHtbZTu8@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Sun, 27 Feb 2011 13:16:55 -0500"
Date: Sun, 27 Feb 2011 13:39:41 -0500
Message-ID: <18767.1298831981@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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.

			regards, tom lane