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>
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 |