Re: How check execution plan of a function

От: Andy Colson
Тема: Re: How check execution plan of a function
Дата: ,
Msg-id: 4BBF3100.4060707@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: How check execution plan of a function  ("Sabin Coanda")
Список: pgsql-performance

Скрыть дерево обсуждения

How check execution plan of a function  ("Sabin Coanda", )
 Re: How check execution plan of a function  ("Sabin Coanda", )
 Re: How check execution plan of a function  (Andy Colson, )
 Re: How check execution plan of a function  (Andy Colson, )
 Re: How check execution plan of a function  ("Sabin Coanda", )
  Re: How check execution plan of a function  ("Kevin Grittner", )

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



В списке pgsql-performance по дате сообщения:

От: Brian Cox
Дата:
Сообщение: "could not open relation..."
От: norn
Дата:
Сообщение: Re: significant slow down with various LIMIT