From: Shaun Thomas <sthomas@peak6.com>
To: Zotov <zotov@oe-it.ru>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Bad plan when join on function
Date: Tue, 18 Jan 2011 08:15:21 -0600
Message-ID: <4D35A079.1040401@peak6.com> (raw)
In-Reply-To: <4D33F7D1.4020201@oe-it.ru>
References: <4D33F7D1.4020201@oe-it.ru>
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
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: sthomas@peak6.com, zotov@oe-it.ru
Subject: Re: Bad plan when join on function
In-Reply-To: <4D35A079.1040401@peak6.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