pl/pgsql faster than raw SQL?

From: Markus Bertheau ☭
Subject: pl/pgsql faster than raw SQL?
Date: ,
Msg-id: 1110284432.4905.18.camel@dicaprio.akademie1.de
(view: Whole thread, Raw)
Responses: Re: pl/pgsql faster than raw SQL?  (Richard Huxton)
Re: pl/pgsql faster than raw SQL?  (John A Meinel)
Re: pl/pgsql faster than raw SQL?  (Gaetano Mendola)
List: pgsql-performance

Tree view

pl/pgsql faster than raw SQL?  (Markus Bertheau ☭, )
 Re: pl/pgsql faster than raw SQL?  (Richard Huxton, )
 Re: pl/pgsql faster than raw SQL?  (John A Meinel, )
 Re: pl/pgsql faster than raw SQL?  (Gaetano Mendola, )
  Re: pl/pgsql faster than raw SQL?  (John A Meinel, )
   Re: pl/pgsql faster than raw SQL?  (Gaetano Mendola, )

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



pgsql-performance by date:

From: Josh Berkus
Date:
Subject: Re: Tuning, configuration for 7.3.5 on a Sun E4500
From: Gaetano Mendola
Date:
Subject: Re: bad plan