pl/pgsql faster than raw SQL? - Mailing list pgsql-performance
From | Markus Bertheau ☭ |
---|---|
Subject | pl/pgsql faster than raw SQL? |
Date | |
Msg-id | 1110284432.4905.18.camel@dicaprio.akademie1.de Whole thread Raw |
Responses |
Re: pl/pgsql faster than raw SQL?
Re: pl/pgsql faster than raw SQL? Re: pl/pgsql faster than raw SQL? |
List | pgsql-performance |
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 ☭ <twanger@bluetwanger.de>
pgsql-performance by date: