How do I profile a user-defined function so that I know which parts of the function are the ones that are taking the biggest chunk of time?
When I run EXPLAIN on the queries within the function none of them show up as onerous burdens to the performance. But when they are all operating together within the function and within the functional logic they become really expensive. Obviously I've made a mistake somewhere but it isn't obvious (otherwise it would be fixed already) and I'd prefer having a profile report telling me what is taking so long rather than guessing and possibly making things worse.
So is there any way to get a line-by-line timing profile of a user-defined function?
Not really. What you can do is simulate the queries in functions by using PREPARE. You're probably seeing a difference because when PG plans the queries for functions/prepared queries it doesn't know the actual values.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
