Message-ID: <4BBF3100.4060707@squeakycode.net>
Date: Fri, 09 Apr 2010 08:52:00 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.9) Gecko/20100317 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Sabin Coanda <sabin.coanda@deuromedia.ro>,
	pgsql-performance@postgresql.org
Subject: Re: How check execution plan of a function
References: <hphv0u$18f$1@news.hub.org> <4BBDF9C9.9050504@squeakycode.net>
	<hpn9i7$1dku$1@news.hub.org>
In-Reply-To: <hpn9i7$1dku$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On Fri Apr 9 2010 8:18 AM, Sabin Coanda wrote:
> I have just a function returning a cursor based on a single coplex query.
> When I check the execution plan of that query it takes about 3 seconds. Just
> when it is used inside the function it freezes.
>
> This is the problem, and this is the reason I cannot imagine what is happen.
> Also I tried to recreate the function as it was before when it run in 3
> seconds, but I cannot make it to run properly now.
>
>

a query, like: "select stuff from aTable where akey = 5" can be 
planned/prepared differently than a function containing: "select stuff 
from aTable where akey = $1".  I'm guessing this is the problem you are 
running into.  The planner has no information about $1, so cannot make 
good guesses.

I think you have two options:
1) dont use a function, just fire off the sql.
2) inside the function, create the query as a string, then execute it, like:

a := "select junk from aTable where akey = 5";
EXECUE a;

(I dont think that's the exact right syntax, but hopefully gets the idea 
across)

-Andy