pl/pgsql faster than raw SQL?

Поиск
Список
Период
Сортировка
От Markus Bertheau ☭
Тема pl/pgsql faster than raw SQL?
Дата
Msg-id 1110284432.4905.18.camel@dicaprio.akademie1.de
обсуждение исходный текст
Ответы Re: pl/pgsql faster than raw SQL?  (Richard Huxton <dev@archonet.com>)
Re: pl/pgsql faster than raw SQL?  (John A Meinel <john@arbash-meinel.com>)
Re: pl/pgsql faster than raw SQL?  (Gaetano Mendola <mendola@bigfoot.com>)
Список 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 по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: bad plan
Следующее
От: Gaetano Mendola
Дата:
Сообщение: Re: bad plan