Пишу, по большей части, про историю, свою жизнь и немного про программирование.

Ускорение пользовательских функций в PostgreSQL

Мы ещё в процессе перевода последних наших проектов с «Оракла» на «Постгрес», поэтому всё ещё не закончили избавляться от характерных для диалекта «Оракла» функций, тащим их с собой — пока так проще.

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

Ниже две функции на разных языках, поддерживаемых «Постгресом», по смыслу идентичные — обе представляют собой замену функции NVL2 «Оракла» над целыми числами:

CREATE FUNCTION nvl2_plpgsql(a numeric, b numeric, c numeric) RETURNS numeric AS '
BEGIN
  IF a IS NULL
    THEN RETURN c;
    ELSE RETURN b;
  END IF;
END;
' LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION nvl2_sql(a numeric, b numeric, c numeric) RETURNS numeric AS '
SELECT CASE WHEN a IS NULL THEN c ELSE b END;
' LANGUAGE SQL IMMUTABLE;

Будет ли какая-то разница в производительности? Посмотрим на тесты:

 p95=# EXPLAIN ANALYZE SELECT nvl2_plpgsql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.066 ms  Execution time: 38881.927 ms

p95=# EXPLAIN ANALYZE SELECT nvl2_sql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.253 ms  Execution time: 15435.231 ms

Как ни странно, функция на чистом ЭсКуЭле быстрее. Судя по моим экспериментам, так всегда. Иногда результаты различаются вдвое, иногда на порядок — зависит от того, что она делает, но как правило быстрее, обратных примеров я ещё не встречал.

Это неплохое подспорье, так как у нас такие функции встречаются не только в самих запросах, но и в функциональных индексах, значит ускорятся не только чтения, но и вставки.