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

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(at)agliodbs(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>