pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
performance of functions - or rather lack of it
4+ messages / 2 participants
[nested] [flat]

* performance of functions - or rather lack of it
@ 2001-04-04 10:12  Peter Galbavy <peter.galbavy@knowledge.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Peter Galbavy @ 2001-04-04 10:12 UTC (permalink / raw)
  To: pgsql-sql

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 = true and 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 = true and 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/




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: performance of functions - or rather lack of it
@ 2001-04-04 10:48  Peter Galbavy <peter.galbavy@knowledge.com>
  parent: Peter Galbavy <peter.galbavy@knowledge.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Peter Galbavy @ 2001-04-04 10:48 UTC (permalink / raw)
  To: pgsql-sql

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/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: performance of functions - or rather lack of it
@ 2001-04-04 17:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter Galbavy <peter.galbavy@knowledge.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-04-04 17:01 UTC (permalink / raw)
  To: Peter Galbavy <peter.galbavy@knowledge.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: performance of functions - or rather lack of it
@ 2001-04-05 07:29  Peter Galbavy <peter.galbavy@knowledge.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Peter Galbavy @ 2001-04-05 07:29 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-04-05 07:29 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-04 10:12 performance of functions - or rather lack of it Peter Galbavy <peter.galbavy@knowledge.com>
2001-04-04 10:48 ` Peter Galbavy <peter.galbavy@knowledge.com>
2001-04-04 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 07:29   ` Peter Galbavy <peter.galbavy@knowledge.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox