Обсуждение: 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/