X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C25B9D1B4E6 for ; Sun, 28 Sep 2003 18:29:29 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 99701-04 for ; Sun, 28 Sep 2003 15:29:20 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 21A12D1B538 for ; Sun, 28 Sep 2003 15:29:19 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h8SITE6c010188; Sun, 28 Sep 2003 14:29:14 -0400 (EDT) To: Joe Conway Cc: Josh Berkus , Richard Sydney-Smith , pgsql-sql@postgresql.org Subject: Re: Mystery function error In-reply-to: <9634.1064767690@sss.pgh.pa.us> References: <000c01c38572$73c03c50$251a32d2@athlon2000> <200309272229.50601.josh@agliodbs.com> <9398.1064765182@sss.pgh.pa.us> <3F770A8D.9030400@joeconway.com> <9634.1064767690@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sun, 28 Sep 2003 12:48:10 -0400" Date: Sun, 28 Sep 2003 14:29:14 -0400 Message-ID: <10187.1064773754@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/362 X-Sequence-Number: 15340 I said: > [hmm, I wonder why plpgsql seems to have gotten slower in 7.4...] False alarm --- or at least, it wasn't plpgsql's fault. I copied Joe's function definition, which was CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' But since position() takes arguments of type TEXT, there are two bpchar->text coercions involved inside the function. This was a "free" binary coercion in 7.3, but 7.4 interprets it as invoking rtrim(). The extra rtrim operations account for most of the slowdown. Using functions declared like "locate(text, text)" to avoid the unwanted type coercions, I get these kinds of numbers: 7.4 7.3 position() 1 usec/call 1 usec/call SQL func 1 usec/call 90 usec/call plpgsql func 110 usec/call 100 usec/call regards, tom lane