Запросы. Хранимые процедуры. Производительность.

Пост сквозь спички в глазах, потому сумбурный.
В ходе эксперимента установил, что запросы (на чистом sql) выполняются немного медленнее, чем хранимые процедуры.
Есть запрос, есть хранимая процедура, которая в цикле возвращает результаты того же запроса построчно (RETURN NEXT). Запрос выполняется за 75-85 мс с вероятностью 75% (приблизительно), процедура - 55-75 мс. К чему бы это?
Ладно, если уж пишется спагетти-код, то на эту мелочь можно и закрыть глаза. Интереснее следующее - первое выполнение запроса занимает около 450 мс, что закономерно, ибо составляется план выполнения запроса. При создании процедуры план (как я понимаю) сохраняется вместе с ней. Вопрос в том, насколько часто при использовании чистого запроса будет составляться его план? Если используется много чистых запросов? Много серверов приложений?

PL/Ruby

Есть такая штука как процедурный язык хранимых процедур для СУБД. Для Postgresql обработчиков этих языков написали около десятка, есть и для Руби.
Концептуальный его недостаток в том, что его можно неправильно приготовить, что с успехом делают некоторые дистрибутивостроители. Скажем, вот такой вот код

1 create or replace function rtype(first int4, second varchar) RETURNS varchar as '
2 first + second.to_i
3 ' language 'plruby';
4 select rtype(1, '5');

правильно выполняется на моем локальном сервере, но не принимается девелопмент сервером, на котором PL/Ruby поставлен из пакета. Значит, надо правильно его собрать, как минимум
ruby extconf.rb --enable-conversion
ну и для особенных ценителей есть ключи --enable-network и --enable-geometry.
Чтобы собрать нужен сорец. А поскольку оффсайт отдавал только мануал, я сбил ноги в кровь в поисках сорца хоть в каком-либо виде, хоть в src.rpm, пока не вспомнил, что сижу на величайшем зеркале сорцов всея Open Source. В общем, брать тут http://distfiles.gentoo.org/distfiles/plruby-0.5.1.tar.gz
Рубить всегда! Рубить везде!

Аггрегируй это.

Проблема
Уже в стародавние времена в STL языка C++ была функция для подсчёта элементов, удовлетворяющих условию. В SQL этому, по идее, должны служить аггрегатные функции (аггрегаты), однако там есть лишь элементарные count.
А они и не умеют, поскольку могут принимать только 1 столбец. А нам надо туда же передавать и значение для сравнения. И решение есть, неизящное, но надёжное.
Решение

 1 create or replace function inc_if(
 2     count int8,
 3     arr anyarray
 4 )
 5 returns int8 as
 6 $$
 7 declare
 8 begin
 9     if (arr[1]=arr[2]) then
10         return count+1;
11     else
12         return count;
13     end if;
14 end
15 $$
16 language 'plpgsql';
17
18 CREATE AGGREGATE count_if
19 (
20 BASETYPE=anyarray,
21 SFUNC=inc_if,
22 STYPE=int8,
23 INITCOND=0
24 );

Использование

Удобно использовать при группировке:
select count_if(ARRAY["passed_exams", 0]) from students group by id_group;
Вот вам и количество кандидатов на отчисление в каждой группе (чёрный юмор).

Недостатки

• Накладные расходы на создание массивов. Не лечится. Аггрегаты принимают 1 столбец.


• Необходимость создания агрегата для каждого потенциально нужного условия, негибкость. Возможно решение в виде использования динамических языков (PL/Python && PL/Ruby), передачи условия внутрь аггрегата с последующим его вычислеием (eval).

Про оптимизацию PL/pgSQL

Довелось сегодня оптимизировать функцию, ускорив её выполнение в 400 раз.
Назначение - каскадное удаление в сложном дереве, начиная с определённого узла. Каждый узел или лист имеет внешний ключ - id прав.
Идеологически всё было в ней правильно, мысль была выражена верно: находились подузлы узла и вызывались функции их удаления. Функция удаления листа - полиморфическая, принимающая имя таблицы и id строки.
Но, с точки зрения реализации данного подхода в СУБД это совершенно неприемлемо:

1. Функция удаления листа использовала оператор EXECUTE. Из документации PostgreSQL

36.6.5. Executing Dynamic Commands
В отличие от всех других команд PL/pgSQL , команда, запущенная оператором EXECUTE не готовится и не сохраняется всего один раз в течение жизни сеанса. Вместо этого команда готовится каждый раз при запуске оператора.

2. Огромные накладные расходы в виде процессорного времени на вызов в цикле функций удаления подузлов.

Моё решение (N и способ обхода известен заранее):
select col_to_arr(distinct id узлов верхнего уровня) ,
col_to_arr(distinct id прав узлов верхнего уровня) ,
...
col_to_arr(distinct id узлов N уровня) ,
col_to_arr(distinct id прав узлов N уровня) ,
...
col_to_arr(distinct id листов) ,
col_to_arr(distinct id прав листов) ,
into имена массивов
FROM много JOIN'ов и условий.

Далее - N*2 операторов DELETE
DELETE from имя таблицы where id in (select * from arr_to_col(имя очередного массива)).

Don't be stupid(c) Scaling Twitter
1. Избегайте EXECUTE, если подразумевается многократный вызов.
2. Меньше декомпозиции работ.
3. Цикл - повод задуматься.
4. Меньше динамики.

Новая функция заняла 45 строк и выполняется в 400 раз быстрее.

И вдоль и поперёк

Ещё когда только пришёл в проект, в котором предполагалось много и занудно писать на PL/pgSQL, написал две элементарные функции. И все спрашивали - да зачем оно надо... Однако сейчас используют широко и говорят спасибо.
Первая является агрегатом (как min, max, avg, sum и подобные) и собирает столбец в массив. Ну а дальше с помощью PL/pgSQL его можно обрабатывать. Используется в SELECT clause. Объявляется так:
CREATE AGGREGATE col_to_arr(BASETYPE = anyelement, SFUNC = array_append, STYPE = anyarray, INITCOND = '{}' );
Вторая решает обратную задачу - представляет массив как столбец. Используется в FROM clause.

 1 create or replace function "arr_to_col"(arr anyarray)

 2 returns setof anyelement as

 3 $$

 4 declare

 5 size int;

 6 index int;

 7 begin

 8 select array_upper(arr,1) into size;

 9 for index in 1..size loop

10     return next arr[index];

11 end loop;

12 return;

13 end

14 $$

15 language 'plpgsql';

При желании можно в одном запросе повернуть скаляр, доставшийся от какого-то другого куска кода, как вдоль так и поперёк. Что иногда приходится проделывать. Выглядит это примерно так:

113 SELECT  col_to_arr(old_id_alias)

114 INTO    new_id_array

115 FROM    table1,table2,tableN, arr_to_col(old_id_arr) old_id_alias

116 WHERE   table1.id=old_id_alias AND ...

Сначала old_id_arr представляется в виде столбца, связывается с несколькими таблицами условиями, полученная выборка преобразуется в массив для дальнейшей работы.
В общем, хозяйке на заметку.