STABLE functions

From: Joachim Wieland <jwieland(at)kawo2(dot)rwth-aachen(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: STABLE functions
Date: 2003-04-25 09:41:47
Message-ID: 20030425094147.GA23992@mcknight.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi there,

this is actually a repost from the BUGS-list where I got no answer.

I'm using 7.3.2 and encounter the problem that a SELECT query that uses
a function with a constant argument is quite slow. The function is
declared STABLE. Here's an example:

SELECT ... FROM table WHERE col = f('xyz');

From what I read in the docs

( http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createfunction.html )

I thought that this function is only executed once when declared as
STABLE, however the time of the query seems to indicate that it is
executed for each row of "table".

Below is a small script that generates some SQL commands that should show
the problem. It creates a table, inserts some rows and defines a
function that should just take some time when executed.

In my opinion

SELECT s FROM test WHERE s = f_test('abc');

should (roughly) be as fast as

SELECT f_test('abc');

(for a relatively small table) but it isn't.

-----8<------------------

#!/bin/sh

echo "CREATE TABLE test (s int);"

for i in `seq 0 1000`; do
echo "INSERT INTO test VALUES ($i);"
done

cat << EOF
CREATE OR REPLACE FUNCTION f_test(VARCHAR(200)) RETURNS int AS '
DECLARE
r RECORD;
v VARCHAR(200);
a ALIAS FOR \$1;
BEGIN
IF a IS NULL THEN
RETURN NULL;
END IF;
FOR r IN SELECT * FROM test LOOP
v = r.s;
END LOOP;
RETURN 1;
END;
' LANGUAGE plpgsql STABLE STRICT;

EOF

-----8<------------------

These are the times I got:

=> explain analyze select f_test('abc');
Total runtime: 49.52 msec

=> explain analyze select * from test where s = 1;
Total runtime: 4.90 msec

=> explain analyze select * from test where s = f_test('abc');
Total runtime: 65084.03 msec

I expected ~ 49.52 msec + 4.90 msec...

Can anybody tell me if this is my mistake or PostgreSQL's?

Thanks,
Joachim

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andreas Pflug 2003-04-25 11:07:49 Statement triggers 7.4 NEW/OLD
Previous Message Shi-Sen Chang 2003-04-25 08:28:50 Re: linking problem with gcc-mingw