From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joe Conway <mail@joeconway.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Richard Sydney-Smith <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Mystery function error
Date: Sun, 28 Sep 2003 12:48:10 -0400
Message-ID: <9634.1064767690@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mail@joeconway.com, josh@agliodbs.com, richard@ibisaustralia.com
Subject: Re: Mystery function error
In-Reply-To: <9634.1064767690@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox