Message-ID: <4D51BE0B.1060404@agliodbs.com>
Date: Tue, 08 Feb 2011 14:04:59 -0800
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: 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
References: <4D514FFF.9060308@shopzeus.com>
In-Reply-To: <4D514FFF.9060308@shopzeus.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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