From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Laszlo Nagy <gandalf@shopzeus.com>
Cc: pgsql-performance@postgresql.org, Daniel Fekete <danieleff@gmail.com>
Subject: Re: Bad query plan when the wrong data type is used
Date: Wed, 09 Feb 2011 11:52:44 +0200
Message-ID: <4D5263EC.9000205@gmail.com> (raw)
In-Reply-To: <AANLkTikWGEAgaOe4Z3UfT-k=9jZ6Z0GRTD3AzX3hRmcC@mail.gmail.com>
References: <4D514FFF.9060308@shopzeus.com>
<4D51BE0B.1060404@agliodbs.com>
<AANLkTikWGEAgaOe4Z3UfT-k=9jZ6Z0GRTD3AzX3hRmcC@mail.gmail.com>
09.02.11 01:14, Dave Crooke написав(ла):
> 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 <http://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.
Actually the problem may be in layers, and the problem may even be not
noticed until it's late enough. As far as I remember from this list
there are problems with column being integer and parameter prepared as
bigint or number. Same for number vs double vs float.
As for me it would be great for optimizer to consider the next:
1) val1::narrow = val2::wide as (val1::narrow = val2::narrow and
val2::narrow = val2::wide)
2) val1::narrow < val2::wide as (val1::narrow < val2::narrow and
val1::wide < val2::wide)
3) val1::narrow > val2::wide as (val1::narrow + 1 > val2::narrow and
val1::wide > val2::wide)
Of course it should use additional check it this allows to use an index.
Surely, this is not an easy thing to implement, but as for me similar
question are raised quite often in this list.
Best regards, Vitalii Tymchyshyn
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, dcrooke@gmail.com, josh@agliodbs.com, gandalf@shopzeus.com, danieleff@gmail.com
Subject: Re: Bad query plan when the wrong data type is used
In-Reply-To: <4D5263EC.9000205@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox