Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34H1f856129 for ; Wed, 4 Apr 2001 13:01:41 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f34H1Gw07595; Wed, 4 Apr 2001 13:01:16 -0400 (EDT) To: "Peter Galbavy" cc: pgsql-sql@postgresql.org Subject: Re: performance of functions - or rather lack of it In-reply-to: <00bc01c0bcef$c527cf20$6601a8c0@knowledge.com> References: <00bc01c0bcef$c527cf20$6601a8c0@knowledge.com> Comments: In-reply-to "Peter Galbavy" message dated "Wed, 04 Apr 2001 11:12:34 +0100" Date: Wed, 04 Apr 2001 13:01:15 -0400 Message-ID: <7592.986403675@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/51 X-Sequence-Number: 2390 "Peter Galbavy" 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