Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Laszlo Nagy <gandalf(at)shopzeus(dot)com>, pgsql-performance(at)postgresql(dot)org, Daniel Fekete <danieleff(at)gmail(dot)com>
Subject: Re: Bad query plan when the wrong data type is used
Date: 2011-02-28 19:04:53
Message-ID: AANLkTi=7f1v1bG1FU_gXxzarKgp6TxFjoea88ez6jqP1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2011-02-28 19:09:15
Subject: Re: Talking about optimizer, my long dream
Previous:From: Tom LaneDate: 2011-02-28 18:57:36
Subject: Re: Query on view radically slower than query on underlying table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group