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`; doecho "INSERT INTO test VALUES ($i);"
done
cat << EOF
CREATE OR REPLACE FUNCTION f_test(VARCHAR(200)) RETURNS int AS '
DECLAREr RECORD;v VARCHAR(200);a ALIAS FOR \$1;
BEGINIF 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
