Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T7qW2-0001zt-Vh for pgsql-performance@postgresql.org; Sat, 01 Sep 2012 16:25:47 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T7qVx-0002ku-6m for pgsql-performance@postgresql.org; Sat, 01 Sep 2012 16:25:45 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1T7qVt-0008SQ-Ra; Sat, 01 Sep 2012 12:25:37 -0400 Date: Sat, 1 Sep 2012 12:25:37 -0400 From: Bruce Momjian To: Robert Haas Cc: Tom Lane , Josh Berkus , Laszlo Nagy , pgsql-performance@postgresql.org, Daniel Fekete Subject: Re: Bad query plan when the wrong data type is used Message-ID: <20120901162537.GA32319@momjian.us> References: <4D514FFF.9060308@shopzeus.com> <4D51BE0B.1060404@agliodbs.com> <18767.1298831981@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -2.1 (--) X-Archive-Number: 201209/3 X-Sequence-Number: 47749 On Mon, Feb 28, 2011 at 02:04:53PM -0500, Robert Haas wrote: > On Sun, Feb 27, 2011 at 1:39 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Tue, Feb 8, 2011 at 5:04 PM, Josh Berkus 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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +