Re: How check execution plan of a function

From: Andy Colson
Subject: Re: How check execution plan of a function
Date: ,
Msg-id: 4BBDF9C9.9050504@squeakycode.net
(view: Whole thread, Raw)
In response to: How check execution plan of a function  ("Sabin Coanda")
List: pgsql-performance

Tree view

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 Wed Apr 7 2010 7:47 AM, Sabin Coanda wrote:
> Hi there,
>
> I have a function which returns setof record based on a specific query.
> I try to check the execution plan of that query, so I write EXPLAIN ANALYZE
> before my select, I call the function and I see the result which shows an
> actual time about 5 seconds. But when I call my function after I remove
> EXPLAIN ANALYZE it takes more than 300 seconds, and I cancel it.
>
> What's happen, or how can I see the function execution plan to optimize it ?
>
> TIA,
> Sabin
>
>

I ran into the same problems, what I did was enable the logging in
postgresql.conf.  I dont recall exactly what I enabled, but it was
something like:

track_functions = pl

log_statement_stats = on
log_duration = on

Then in the serverlog you can see each statement, and how long it took.
  Once I found a statement that was slow I used explain analyze on just
it so I could optimize that one statement.

-Andy



pgsql-performance by date:

From: "Kevin Grittner"
Date:
Subject: Re: significant slow down with various LIMIT
From: Robert Haas
Date:
Subject: Re: PostgreSQL with Zabbix - problem of newbe