Thread: performance of functions - or rather lack of it
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 ? The whole thing is being driver through perl DBI. This may be contributory. Anyhow, the original query: SELECT COUNT(mb.instance) FROM domain dm, mail ms, mailbox mb WHERE dm.enabled = true and dm.existent = true and dm.available= true AND ms.enabled = true and ms.existent = true and ms.available = true AND mb.enabled = trueand mb.existent = true and mb.available = true AND dm.parent = ms.parent AND mb.parent = ms.serviceid AND dm.instance = $q_domain AND mb.instance = $q_local_part; where $q_XXX are quoted perl scalars. The function is then: CREATE FUNCTION mail_is_mailbox(text, text) RETURNS int4 AS ' SELECT COUNT(mb.instance) FROM domain dm, mail ms, mailbox mb WHERE dm.enabled = true and dm.existent = true and dm.available= true AND ms.enabled = true and ms.existent = true and ms.available = true AND mb.enabled = trueand mb.existent = true and mb.available = true AND dm.parent = ms.parent AND mb.parent = ms.serviceid AND dm.instance = $2 AND mb.instance = $1;' LANGUAGE 'sql'; SELECT mail_is_mailbox($q_local_part, $q_domain); Running both these 1000 times from a remote (same subnet 100BaseTX) client with the same query results in time for the function typically 20 - 25% more than the bare query. 22 vs 16 seconds for example. 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 ? rgds, -- Peter Galbavy Knowledge Matters Ltd. http://www.knowledge.com/
BTW The service is 7.0.2 and the client 7.1RC1 and the OSes are OpenBSD/i386 2.8-stable. On Wed, Apr 04, 2001 at 11:12:34AM +0100, Peter Galbavy wrote: > 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 ? -- Peter Galbavy Knowledge Matters Ltd http://www.knowledge.com/
"Peter Galbavy" <peter.galbavy@knowledge.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
On Wed, Apr 04, 2001 at 01:01:15PM -0400, Tom Lane wrote: > 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 followed that up quickly - server 7.0.2, client 7.1RC1. VACUUMed etc prior to the test. OTOH, since the tests were done multiple times directly after the other, the underlying infrastructure should be the same. BTW I deleted your paragraph above, but I agree about the parameters and the constant stuff. I will retry with a fixed-value function just for the completeness of it. > Unless your TCP connection is running across tin cans and string, > the transfer time for the query text is negligible ... Fair point. I am not really in the 100Mb networking work in my heart ... :-) -- Peter Galbavy Knowledge Matters Ltd http://www.knowledge.com/