From richard@ibisaustralia.com Sun Sep  28 03:39:41 2003
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 DEF89D1B4E3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 03:39:41 +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 67023-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 00:39:30 -0300 (ADT)
Received: from server146.hostingrevolution.com (unknown [64.21.143.245])
	by svr1.postgresql.org (Postfix) with ESMTP id 05F55D1B527
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 00:39:30 -0300 (ADT)
Received: from 037.b.001.pth.iprimus.net.au ([210.50.26.37] helo=athlon2000)
	by server146.hostingrevolution.com with smtp (Exim 4.23)
	id 1A3SPT-0005aI-Ck
	for pgsql-sql@postgresql.org; Sat, 27 Sep 2003 22:39:26 -0500
Message-ID: <000c01c38572$73c03c50$251a32d2@athlon2000>
From: "Richard Sydney-Smith" <richard@ibisaustralia.com>
To: <pgsql-sql@postgresql.org>
Subject: Mystery function error
Date: Sun, 28 Sep 2003 11:39:19 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0007_01C385B5.274A2050"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - ibisaustralia.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=BAYES_30,
	HTML_40_50, RCVD_IN_OSIRUSOFT_COM
X-Spam-Level: 
X-Archive-Number: 200309/355
X-Sequence-Number: 15333

This is a multi-part message in MIME format.

------=_NextPart_000_0007_01C385B5.274A2050
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

As I am converting from Sybase I wanted to create a function which would re=
plicate the behaviour of the sybase "Locate" command.

The goal is to have=20

locate( stra, strb) =3D position(strb in stra)

where "position" is the standard postgres function for the index position o=
f string "A" in string "B"

My attempt at a function to do this task returns the error message=20

ERROR: parse error at or near '"'=20=20=20=20

I can not see why.... and have attached the simple function.

---------------------------------------------------------------------------=
---------------------------
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar)
  RETURNS int4 AS
'
  -- search for the position of $2 in $1

  declare=20
    srcstr alias for $1;
    searchstr alias for $2;

begin
return position(searchstr in srcstr);
'
  LANGUAGE 'plpgsql' VOLATILE;
---------------------------------------------------------------------------=
----------------------------------

Thanks very much=20

Richard

------=_NextPart_000_0007_01C385B5.274A2050
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>As I am converting from Sybase I wanted to=
 create a=20
function which would replicate the behaviour of the sybase "Locate"=20
command.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The goal is to&nbsp;have </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>locate( stra, strb) =3D position(strb in=
=20
stra)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>where "position" is the standard postgres =
function=20
for the index position of string "A" in string "B"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>My attempt at a function to do this task r=
eturns=20
the error message </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>ERROR: parse error at or near '"'&nbsp;&nb=
sp;&nbsp;=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I can not see why.... and have attached th=
e simple=20
function.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2>------------------------------------------------------------------=
------------------------------------</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>CREATE OR REPLACE FUNCTION public.locate(b=
pchar,=20
bpchar)<BR>&nbsp; RETURNS int4 AS<BR>'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; -- search for the position of $2 in=
=20
$1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; declare <BR>&nbsp;&nbsp;&nbsp; srcs=
tr alias=20
for $1;<BR>&nbsp;&nbsp;&nbsp; searchstr alias for $2;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>begin<BR>return position(searchstr in=20
srcstr);<BR>'<BR>&nbsp; LANGUAGE 'plpgsql' VOLATILE;</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>------------------------------------------------------------------=
-------------------------------------------</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks very much </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Richard</FONT></DIV></BODY></HTML>

------=_NextPart_000_0007_01C385B5.274A2050--




From mail@joeconway.com Sun Sep  28 05:25:01 2003
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 70C44D1B508
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 05:25:01 +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 86876-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 02:24:31 -0300 (ADT)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id CAFE1D1B4E8
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 02:24:29 -0300 (ADT)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.1.3)
	with ESMTP-TLS id 390154; Sat, 27 Sep 2003 22:24:01 -0700
Message-ID: <3F766FD5.3080001@joeconway.com>
Date: Sat, 27 Sep 2003 22:21:25 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Richard Sydney-Smith <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Mystery function error
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
In-Reply-To: <000c01c38572$73c03c50$251a32d2@athlon2000>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/356
X-Sequence-Number: 15334

Richard Sydney-Smith wrote:
>  CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS
> int4 AS ' -- search for the position of $2 in $1
> 
> declare srcstr alias for $1; searchstr alias for $2;
> 
> begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql'
> VOLATILE; 

You are missing the "end" keyword in there. Also, I'd think this 
function is IMMUTABLE not VOLATILE.

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar)
RETURNS int4 AS '
   -- search for the position of $2 in $1
   declare
     srcstr alias for $1;
     searchstr alias for $2;
   begin
     return position(searchstr in srcstr);
   end;
' LANGUAGE 'plpgsql' IMMUTABLE;

This could also be done as:

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar)
RETURNS int4 AS '
   select position($2 in $1)
' LANGUAGE 'sql';


HTH,

Joe





From josh@agliodbs.com Sun Sep  28 05:28:51 2003
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 1967ED1B52D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 05:28:51 +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 75819-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 02:28:41 -0300 (ADT)
Received: from davinci.ethosmedia.com (unknown [209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 5082ED1B508
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 02:28:40 -0300 (ADT)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3690709; Sat, 27 Sep 2003 22:29:12 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Mystery function error
Date: Sat, 27 Sep 2003 22:28:07 -0700
User-Agent: KMail/1.4.3
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
In-Reply-To: <000c01c38572$73c03c50$251a32d2@athlon2000>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200309272228.07762.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/357
X-Sequence-Number: 15335

Richard,

>--------------------------- CREATE OR REPLACE FUNCTION public.locate(bpchar,
> bpchar)
>   RETURNS int4 AS
> '
>   -- search for the position of $2 in $1
>
>   declare
>     srcstr alias for $1;
>     searchstr alias for $2;
>
> begin
> return position(searchstr in srcstr);

You're missing "END;".

> '
>   LANGUAGE 'plpgsql' VOLATILE;

Also, the function is not VOLATILE.  It's IMMUTABLE, and STRICT as well.


-- 
Josh Berkus
Aglio Database Solutions
San Francisco



From josh@agliodbs.com Sun Sep  28 05:30:34 2003
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 827A5D1B511
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 05:30:34 +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 67440-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 02:30:25 -0300 (ADT)
Received: from davinci.ethosmedia.com (unknown [209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id CEA20D1B4F5
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 02:30:22 -0300 (ADT)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3690710; Sat, 27 Sep 2003 22:30:55 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Mystery function error
Date: Sat, 27 Sep 2003 22:29:50 -0700
User-Agent: KMail/1.4.3
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
In-Reply-To: <000c01c38572$73c03c50$251a32d2@athlon2000>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200309272229.50601.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/358
X-Sequence-Number: 15336

Richard,

> The goal is to have
>
> locate( stra, strb) = position(strb in stra)

Also, this will run faster if you do it as a SQL function:

CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



From tgl@sss.pgh.pa.us Sun Sep  28 16:06:38 2003
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 764BDD1B503
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 16:06:38 +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 72214-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 13:06:28 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id B65E7D1B51D
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 13:06:27 -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 h8SG6M6c009399;
	Sun, 28 Sep 2003 12:06:22 -0400 (EDT)
To: Josh Berkus <josh@agliodbs.com>
Cc: "Richard Sydney-Smith" <richard@ibisaustralia.com>,
	pgsql-sql@postgresql.org
Subject: Re: Mystery function error 
In-reply-to: <200309272229.50601.josh@agliodbs.com> 
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
	<200309272229.50601.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Sat, 27 Sep 2003 22:29:50 -0700"
Date: Sun, 28 Sep 2003 12:06:22 -0400
Message-ID: <9398.1064765182@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/359
X-Sequence-Number: 15337

Josh Berkus <josh@agliodbs.com> writes:
> Also, this will run faster if you do it as a SQL function:

> CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
> SELECT POSITION($2, $1);
> ' LANGUAGE SQL IMMUTABLE STRICT;

This is definitely the solution I'd recommend for 7.4 (because 7.4 would
inline the SQL function definition, resulting in zero runtime overhead).
In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
comparable.  Has anyone done any head-to-head performance comparisons
on such simple functions?

			regards, tom lane



From mail@joeconway.com Sun Sep  28 16:25:11 2003
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 1D808D1B503
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 16:25:11 +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 80900-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 13:24:39 -0300 (ADT)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id 92AFCD1B53A
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 13:24:38 -0300 (ADT)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.1.3)
	with ESMTP-TLS id 390684; Sun, 28 Sep 2003 09:24:09 -0700
Message-ID: <3F770A8D.9030400@joeconway.com>
Date: Sun, 28 Sep 2003 09:21:33 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>,
	Richard Sydney-Smith <richard@ibisaustralia.com>, pgsql-sql@postgresql.org
Subject: Re: Mystery function error
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
	<200309272229.50601.josh@agliodbs.com>
	<9398.1064765182@sss.pgh.pa.us>
In-Reply-To: <9398.1064765182@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/360
X-Sequence-Number: 15338

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>>Also, this will run faster if you do it as a SQL function:
> 
>>CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
>>SELECT POSITION($2, $1);
>>' LANGUAGE SQL IMMUTABLE STRICT;
> 
> This is definitely the solution I'd recommend for 7.4 (because 7.4 would
> inline the SQL function definition, resulting in zero runtime overhead).
> In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
> comparable.  Has anyone done any head-to-head performance comparisons
> on such simple functions?
> 

I did a quick check last night on 7.3.4 and found that plpgsql was faster:

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
   -- search for the position of $2 in $1
   declare
     srcstr alias for $1;
     searchstr alias for $2;
   begin
     return position(searchstr in srcstr);
   end;
' LANGUAGE 'plpgsql' IMMUTABLE;

regression=# explain analyze select locate('abc','b');

                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 
rows=1 loops=1)
  Total runtime: 0.03 msec
(2 rows)

DROP FUNCTION public.locate(bpchar, bpchar);
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
   select position($2 in $1)
' LANGUAGE 'sql';
regression=# explain analyze select locate('abc','b');
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24 
rows=1 loops=1)
  Total runtime: 0.26 msec
(2 rows)


On 7.4 (different hardware), I get this:

plpgsql
--------
regression=# explain analyze select locate('abc','b');
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00 
rows=1 loops=1)
  Total runtime: 0.05 msec
(2 rows)

sql
--------
regression=# explain analyze select locate('abc','b');
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 
rows=1 loops=1)
  Total runtime: 0.03 msec
(2 rows)


Joe




From tgl@sss.pgh.pa.us Sun Sep  28 16:48:25 2003
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 28AEED1B538
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 16:48:25 +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 82681-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 13:48:15 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 672D7D1B524
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 13:48:14 -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 h8SGmA6c009635;
	Sun, 28 Sep 2003 12:48:10 -0400 (EDT)
To: Joe Conway <mail@joeconway.com>
Cc: Josh Berkus <josh@agliodbs.com>,
	Richard Sydney-Smith <richard@ibisaustralia.com>, pgsql-sql@postgresql.org
Subject: Re: Mystery function error 
In-reply-to: <3F770A8D.9030400@joeconway.com> 
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
	<200309272229.50601.josh@agliodbs.com>
	<9398.1064765182@sss.pgh.pa.us> <3F770A8D.9030400@joeconway.com>
Comments: In-reply-to Joe Conway <mail@joeconway.com>
	message dated "Sun, 28 Sep 2003 09:21:33 -0700"
Date: Sun, 28 Sep 2003 12:48:10 -0400
Message-ID: <9634.1064767690@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/361
X-Sequence-Number: 15339

Joe Conway <mail@joeconway.com> writes:
>> ...  Has anyone done any head-to-head performance comparisons
>> on such simple functions?

> I did a quick check last night on 7.3.4 and found that plpgsql was faster:

> regression=# explain analyze select locate('abc','b');

Er ... I'm not sure you're measuring anything reliable there.  In
particular, since you declared the plpgsql function immutable, the
planner would have reduced this function call to a constant on sight,
and there is no evaluation happening at runtime at all.  The SQL
version shows as faster only because you neglected to mark it as
immutable; else it'd have gotten the same treatment.  In any case, a
single call of a simple function is likely to be swamped by executor
startup/shutdown overhead.

I tried the same function definitions using a test like this

	explain analyze select locate(f1,'b') from t1;

where t1 is a 10000-row table with a single text column.  The results I
get are that the SQL function is very marginally faster than the plpgsql
one in 7.3 (1200 vs 1350 msec), and significantly faster in 7.4
(385 vs 1600 msec).  The basic table-scan overhead can be determined
from

	explain analyze select f1,'b' from t1;

which shows as about 330 msec in both versions; subtracting that off
tells you how long it actually took to do 10000 function calls.

[hmm, I wonder why plpgsql seems to have gotten slower in 7.4...]

			regards, tom lane



From tgl@sss.pgh.pa.us Sun Sep  28 18:29:29 2003
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <mail@joeconway.com>
Cc: Josh Berkus <josh@agliodbs.com>,
	Richard Sydney-Smith <richard@ibisaustralia.com>, 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 <tgl@sss.pgh.pa.us>
	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 <tgl@sss.pgh.pa.us>
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



From josh@agliodbs.com Sun Sep  28 18:48:38 2003
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 3288CD1B4EF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 18:48:38 +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 00913-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 15:48:29 -0300 (ADT)
Received: from davinci.ethosmedia.com (unknown [209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 35173D1B587
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 15:48:27 -0300 (ADT)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3691977; Sun, 28 Sep 2003 11:48:50 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Tom Lane <tgl@sss.pgh.pa.us>, Joe Conway <mail@joeconway.com>
Subject: Re: Mystery function error
Date: Sun, 28 Sep 2003 11:47:41 -0700
User-Agent: KMail/1.4.3
Cc: Richard Sydney-Smith <richard@ibisaustralia.com>, pgsql-sql@postgresql.org
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
	<9634.1064767690@sss.pgh.pa.us> <10187.1064773754@sss.pgh.pa.us>
In-Reply-To: <10187.1064773754@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200309281147.41146.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/364
X-Sequence-Number: 15342

Tom,

> position()	1 usec/call	1 usec/call
> SQL func	1 usec/call	90 usec/call
> plpgsql func	110 usec/call	100 usec/call

Hmmm ... this does still seem to show that plpgsql is 10% slower in 7.4.  Any 
idea why?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



From tgl@sss.pgh.pa.us Sun Sep  28 19:04:27 2003
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 139F0D1B4E1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 19:04:27 +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 02278-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 28 Sep 2003 16:04:18 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 43368D1B4E6
	for <pgsql-sql@postgresql.org>; Sun, 28 Sep 2003 16:04:16 -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 h8SJ4D6c010446;
	Sun, 28 Sep 2003 15:04:13 -0400 (EDT)
To: Josh Berkus <josh@agliodbs.com>
Cc: Joe Conway <mail@joeconway.com>,
	Richard Sydney-Smith <richard@ibisaustralia.com>, pgsql-sql@postgresql.org
Subject: Re: Mystery function error 
In-reply-to: <200309281147.41146.josh@agliodbs.com> 
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
	<9634.1064767690@sss.pgh.pa.us> <10187.1064773754@sss.pgh.pa.us>
	<200309281147.41146.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Sun, 28 Sep 2003 11:47:41 -0700"
Date: Sun, 28 Sep 2003 15:04:13 -0400
Message-ID: <10445.1064775853@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/365
X-Sequence-Number: 15343

Josh Berkus <josh@agliodbs.com> writes:
> Hmmm ... this does still seem to show that plpgsql is 10% slower in
> 7.4.

Yeah, I just did some profiling to check on that, and it seems there is
extra overhead in plpgsql's exec_eval_simple_expr(), to the tune of
another palloc or two down inside CreateExprContext().  This probably
would not show up so heavily in a more complex plpgsql function, but
it's a noticeable percentage in this trivial example.

I had noticed this before but I'm not sure there's any easy solution.
Awhile back I looked at saving the exprcontext across calls instead of
creating and deleting it on each call to exec_eval_simple_expr, but the
idea broke down in situations involving recursive plpgsql functions.
I'll take another look though.

			regards, tom lane