"Mindaugas" <mind@xxxxx> writes: > Is it possible to somehow analyze function performance? E.g. > we are using function cleanup() which takes obviously too much time > to execute but I have problems trying to figure what is slowing things > down. > When I explain analyze function lines step by step it show quite > acceptable performance. Are you sure you are "explain analyze"ing the same queries the function is really doing? You have to account for the fact that what plpgsql is issuing is parameterized queries, and sometimes that limits the planner's ability to pick a good plan. For instance, if you have declare x int; begin ... for r in select * from foo where key = x loop ... then what is really getting planned and executed is "select * from foo where key = $1" --- every plpgsql variable gets replaced by a parameter symbol "$n". You can model this for EXPLAIN purposes with a prepared statement: prepare p1(int) as select * from foo where key = $1; explain analyze execute p1(42); If you find out that a particular query really sucks when parameterized, you can work around this by using EXECUTE to force the query to be planned afresh on each use with literal constants instead of parameters: for r in execute 'select * from foo where key = ' || x loop ... The replanning takes extra time, though, so don't do this except where you've specifically proved there's a need. BTW, be careful to use quote_literal() when needed in queries built as strings, else you'll have bugs and maybe even security problems. regards, tom lane
