Hi, I have the following strange situation:
oocms=# vacuum full analyze;
VACUUM
oocms=# \df+ class_get_number_of_objects
ÐÐÐÑÐÐ ÑÑÐÐÑÐÐ
ÐÑÐÐÐ | ÐÐÑ | ÐÐÐ ÐÐÐÐÑÑ
ÑÐÐÑÐÑÑÐÑÐ | ÐÐÐÑ ÐÐÐÐÑÑ ÐÑÐÑÐÐÐÑÐÐ |
ÐÐÐÐÐÐÐÑ | ÐÐÑÐ | ÐÑÑÐÐÐÑÐ ÑÐÐÑÑ |
ÐÐÐÑÐÐÐÐ
-------+-----------------------------+-----------------------+------------------------+----------+---------+----------------+-----------------------------------------------------------------------------------------------
oocms | class_get_number_of_objects | integer | text
| oocms | plpgsql |
DECLARE
arg_class_name ALIAS FOR $1;
BEGIN
IF arg_class_name IS NULL THEN
RAISE WARNING 'class_get_number_of_objects() with NULL class
name called';
RETURN NULL;
END IF;
RETURN
count(1)
FROM
objects
WHERE
class = arg_class_name;
END;
| Return the number of existing or deleted objects of a class.
Arguments: the name of the class
(1 ÐÐÐÐÑÑ)
oocms=# explain analyze select count(1) from objects where class = 'Picture';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=278.16..278.16 rows=1 width=0) (actual time=44.121..44.123
rows=1 loops=1)
-> Seq Scan on objects (cost=0.00..267.65 rows=4205 width=0) (actual
time=0.030..33.325 rows=4308 loops=1)
Filter: ("class" = 'Picture'::text)
Total runtime: 44.211 ms
(ÐÐÐÐÑÐÐ: 4)
oocms=# explain analyze select class_get_number_of_objects('Picture');
QUERY PLAN
--------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=27.019..27.022 rows=1
loops=1)
Total runtime: 27.062 ms
(ÐÐÐÐÑÐÐ: 2)
I.e. a function takes 27 ms to do what takes an equivalent piece of sql
43 ms. How can this be explained?
Some more info:
oocms=# select class_get_number_of_objects('Picture');
class_get_number_of_objects
-----------------------------
4308
(1 ÐÐÐÐÑÑ)
oocms=# select count(1) from objects;
count
-------
13332
(1 ÐÐÐÐÑÑ)
oocms=# \d objects
ÐÐÐÐÐÑÐ "oocms.objects"
ÐÐÐÐÐÐÐ | ÐÐÐ |
ÐÐÐÐÑÐÐÐÑÐÑÑ
-----------+--------------------------+---------------------------------------------------------------
object_id | integer | not null default
nextval('oocms.objects_object_id_seq'::text)
class | text | not null
created | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
ÐÐÐÐÐÑÑ:
"objects_pkey" PRIMARY KEY, btree (object_id)
"fooooo" btree ("class")
ÐÐÑÐÐÐÑÐÐÐÑ ÐÐ ÐÐÐÑÐÐÐÑ ÐÐÑÑÑ:
"objects_class_fkey" FOREIGN KEY ("class") REFERENCES classes(name) ON
UPDATE CASCADE
--
Markus Bertheau â <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
