Re: performance of functions - or rather lack of it

"Peter Galbavy" <peter(dot)galbavy(at)knowledge(dot)com> writes:
> We are building a postgresql based backend database for our 'hosting
> provisioning' system. In a vain attempt to add some, what I thought, simple
> performance tweaks, I thought I would try putting some of the larger and
> more straighforward queries into functions. For everything else the same,
> the functions are on the whole slower. Should they be ?

Possibly. In your example, the planner sees dm.instance and mb.instance
being compared to known literal values when you execute the statement
directly, but to unknown values (function parameters) when you use a
function. This might shift the selectivity estimates enough to result
in choice of a different query plan, which could result in speedup or
slowdown depending on how close to reality the estimates are.

Without knowing which PG version you're using, what plans you're
getting, or even whether you've VACUUM ANALYZEd lately, it's difficult
to say more than that.

> I would have thought that not sending the long SQL across the wire 1000
> times would have saved some time even without any potential query
> optimisations by pre-parsing the SQL ?

Unless your TCP connection is running across tin cans and string,
the transfer time for the query text is negligible ...

regards, tom lane