Received: from mailstore-1.mail.knowledge.com (frida.server.knowledge.com [213.170.2.69]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34AAE897294 for ; Wed, 4 Apr 2001 06:10:14 -0400 (EDT) (envelope-from peter.galbavy@knowledge.com) Received: from async209-1.nas.onetel.net.uk ([212.67.97.209] helo=peterdesktop) by mailstore-1.mail.knowledge.com with asmtp (Exim 3.12 #1) id 14kkEs-0002jC-00 for pgsql-sql@postgresql.org; Wed, 04 Apr 2001 11:10:10 +0100 Message-ID: <00bc01c0bcef$c527cf20$6601a8c0@knowledge.com> From: "Peter Galbavy" To: Subject: performance of functions - or rather lack of it Date: Wed, 4 Apr 2001 11:12:34 +0100 Organization: Knowledge Matters Ltd. MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200104/35 X-Sequence-Number: 2374 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/