Довелось сегодня оптимизировать функцию, ускорив её выполнение в 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 раз быстрее.
