From: Andy Colson <andy@squeakycode.net>
To: Sabin Coanda <sabin.coanda@deuromedia.ro>
To: pgsql-performance@postgresql.org
Subject: Re: How check execution plan of a function
Date: Fri, 09 Apr 2010 08:52:00 -0500
Message-ID: <4BBF3100.4060707@squeakycode.net> (raw)
In-Reply-To: <hpn9i7$1dku$1@news.hub.org>
References: <hphv0u$18f$1@news.hub.org>
<4BBDF9C9.9050504@squeakycode.net>
<hpn9i7$1dku$1@news.hub.org>
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
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: andy@squeakycode.net, sabin.coanda@deuromedia.ro
Subject: Re: How check execution plan of a function
In-Reply-To: <4BBF3100.4060707@squeakycode.net>
* 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