Message-ID: <4D35A079.1040401@peak6.com>
Date: Tue, 18 Jan 2011 08:15:21 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: sthomas@peak6.com
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Zotov <zotov@oe-it.ru>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Bad plan when join on function
References: <4D33F7D1.4020201@oe-it.ru>
In-Reply-To: <4D33F7D1.4020201@oe-it.ru>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit

On 01/17/2011 02:03 AM, Zotov wrote:

> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
> OneRow Contains only one row,
> abstract contains 22 953 500 rows
>
> AsInteger is simple function on Delphi
> it just return input value

Ok... there has to be some kind of misunderstanding, here. First of all, 
if you're trying to cast a value to an integer, there are so many 
built-in ways to do this, I can't imagine why you'd call a C function. 
The most common for your example would be ::INT.

Second, you need to understand how the optimizer works. It doesn't know 
what the function will return, so it has to apply the function to every 
row in your 'abstract' table. You can get around this by applying an 
index to your table with the result of your function, but to do that, 
you'll have to mark your function as STABLE or IMMUTABLE instead of 
VOLATILE.

Joining on the result of a function will always do this. The database 
can't know what your function will return. If you can avoid using a 
function in your join clause, do so.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email