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

[Dave Crooke <dcrooke@xxxxxxxxx>]

You will get the same behaviour from any database product where the query as written requires type coercion - the coercion has to go in the direction of the "wider" type. I have seen the exact same scenario with Oracle, and I view it as a problem with the way the query is written, not with the database server.

Whoever coded the application which is making this query presumably knows that the visa.id field is an integer type in the schema they designed, so why are they passing a float? Convert the 4.0 to 4 on the application side instead, it's one function call or cast.

It's not reasonable to expect the query compiler to pick up the slack for poorly written SQL.

Cheers
Dave

On Tue, Feb 8, 2011 at 4:04 PM, Josh Berkus <josh@xxxxxxxxxxxx> wrote:

Laszlo,

> Which is silly. I think that PostgreSQL converts the int side to a
> float, and then compares them.
>
> It would be better to do this, for each item in the loop:
>
> Â Â * evaluate the right side (which is float)
> Â Â * tell if it is an integer or not
> Â Â * if not an integer, then discard the row immediately
> Â Â * otherwise use its integer value for the index scan

Not terribly likely, I'm afraid. ÂData type coercion is *way* more
complex than you realize (consider the number of data types we have, and
the ability to add UDTs, and then square it). ÂAnd the functionality you
propose would break backwards compatibility; many people currently use
".0" currently in order to force a coercion to Float or Numeric.

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.

--
                Â-- Josh Berkus
                  PostgreSQL Experts Inc.
                  http://www.pgexperts.com

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance