Hi,
I went looking at why some of our queries using some custom functions were
a bit sluggish.
usercandoonobject(integer, integer, character, integer) does this:
IF (isSuperuser(p_user_id)) THEN
RETURN true;
END IF;
RETURN userCanDoOnObjectCheckGod($1, $2, $3, $4);
issuperuser(integer) does:
RETURN (SELECT userInGroup($1, 1000));
useringroup(integer, integer) does:
IF ($2 = 1) THEN
RETURN true;
ELSE
RETURN EXISTS(
SELECT groups_users.users_id
FROM groups_users
WHERE groups_users.users_id = $1
AND groups_users.groups_id = $2
);
END IF;
This is someone else's code, but it seems simple and clear enough to me...
But on runtime, I get this:
db=# explain analyze select issuperuser(id) from users;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..6624.00 rows=23000 width=4) (actual time=0.186..644.488 rows=23000 loops=1)
Total runtime: 664.486 ms
(2 rows)
db=# explain analyze select userInGroup(id, 1000) from users;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..6624.00 rows=23000 width=4) (actual time=0.125..417.948 rows=23000 loops=1)
Total runtime: 437.594 ms
(2 rows)
db=# explain analyze select case when users.id = 1 then true else exists(SELECT groups_users.users_id FROM groups_users WHERE groups_users.users_id = users.id AND groups_users.groups_id = '1000') end as issuperuser from users;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..191157.14 rows=23000 width=4) (actual time=0.053..94.756 rows=23000 loops=1)
SubPlan
-> Index Scan using groups_users_pkey on groups_users (cost=0.00..8.27 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=22999)
Index Cond: ((groups_id = 1000) AND (users_id = $0))
Total runtime: 112.154 ms
(5 rows)
Why are the function calls four or six times slower than their own direct
meaning?
I gather from the documentation that there exists some function cache, which
sounds good, and I could understand this overhead if writing to cache was
its purpose, but even if I immediately just repeat the same query, I get
exactly the same slow result (verified by using the actual queries and
\timing in psql, not repeated 'explain analyze's).
What am I missing?
I tried to find an explanation in the documentation, to no avail.
This is with PostgreSQL 8.3.11.
(Please Cc: replies, I'm not subscribed. TIA.)
--
2. That which causes joy or happiness.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
