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>

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