pl/pgsql faster than raw SQL?

From: Markus Bertheau ☭ <twanger(at)bluetwanger(dot)de>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: pl/pgsql faster than raw SQL?
Date: 2005-03-08 12:20:32
Message-ID: 1110284432.4905.18.camel@dicaprio.akademie1.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)bluetwanger(dot)de>

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Gaetano Mendola 2005-03-08 12:22:28 Re: bad plan
Previous Message Richard Huxton 2005-03-08 11:39:58 Re: bad plan