Функция выполняется быстрее без модификатора STRICT?

Интересно о падении производительности, когда объявлена ​​простая функция SQL STRICT, Я наткнулся на это явление, отвечая на вопрос здесь.

Чтобы продемонстрировать эффект, я создаю два варианта простой функции SQL, которая упорядочивает два элемента массива в порядке возрастания.

Испытательная установка

-- temporary table with 10000 random pairs of integer
CREATE TEMP TABLE arr (i int[]);

INSERT INTO arr 
SELECT ARRAY[(random() * 1000)::int, (random() * 1000)::int]
FROM   generate_series(1,10000);

Функция с STRICT модификатор:

CREATE OR REPLACE FUNCTION f_sort_array1(int[])  RETURNS int[] AS
$$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$$ LANGUAGE sql STRICT IMMUTABLE;

Функция без STRICT модификатор (в остальном идентичен):

CREATE OR REPLACE FUNCTION f_sort_array2(int[])  RETURNS int[] AS
$$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$$ LANGUAGE sql IMMUTABLE;

Результаты

Я казнил каждого около 20 раз и получил лучший результат от EXPLAIN ANALYZE,

SELECT f_sort_array1(i) FROM arr;  -- Total runtime: 103 ms
SELECT f_sort_array2(i) FROM arr;  -- Total runtime:  43 ms (!!!)

Это результаты сервера v9.0.5 в Debian Squeeze. Аналогичные результаты на v8.4. Не тестировал на 9.1, сейчас нет кластера в моем распоряжении. (Может кто-нибудь предоставить дополнительные результаты для v9.1?)

Редактирование: в тесте с 10000 значениями NULL обе функции выполняют одинаковые в одной и той же тестовой среде: ~37 мс.

Я провел небольшое исследование и нашел интересную ошибку. Объявление функции SQL STRICT отключает встраивание функций в большинстве случаев. Подробнее об этом можно узнать в онлайн-журнале PostgreSQL, в списке рассылки pgsql-performance или в вики Postgres.

Но я не совсем уверен, как это может быть объяснением. Как нельзя встраивать функцию в такой простой сценарий, чтобы снизить производительность? Нет индекса, нет чтения диска, нет сортировки. Может быть, накладные расходы от повторного вызова функции, которые упорядочены путем встраивания функции? Вы можете это объяснить? Или я что-то упустил?

Повторное тестирование с Postgres 9.1

Тот же тест на том же оборудовании с PostgreSQL 9.1 обнаружил еще большие различия:

SELECT f_sort_array1(i) FROM arr;  -- Total runtime: 107 ms
SELECT f_sort_array2(i) FROM arr;  -- Total runtime:  27 ms (!!!)

Повторное тестирование с Postgres 9,6

Тот же тест на другом оборудовании с PostgreSQL 9.6. Разрыв еще больше, но:

SELECT f_sort_array1(i) FROM arr;  -- Total runtime: 60 ms
SELECT f_sort_array2(i) FROM arr;  -- Total runtime: 10 ms (!!!)
Источник

2 ответа

Решение

Может быть, накладные расходы от повторного вызова функции, которые упорядочены путем встраивания функции?

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

Тест ниже дает время выполнения 5 мс для встроенного и 50 мс для строгого.

BEGIN;

CREATE SCHEMA f;

SET search_path = f;

CREATE FUNCTION f1(int) RETURNS int AS $$SELECT 1$$ LANGUAGE SQL;
CREATE FUNCTION f2(int) RETURNS int AS $$SELECT 1$$ LANGUAGE SQL STRICT;

\timing on
SELECT sum(f1(i)) FROM generate_series(1,10000) i;
SELECT sum(f2(i)) FROM generate_series(1,10000) i;
\timing off

ROLLBACK;
Источник

Речь идет о встраивании функций, как подозревается и подтверждается тестом Ричарда.

Чтобы было ясно, Postgres Wiki перечисляет это требование для встраивания скалярной функции (как в моем примере):

  • если функция объявлена, то планировщик должен иметь возможность доказать, что выражение тела обязательно возвращает NULLесли какой-либо параметр равен нулю. В настоящее время это условие выполняется только в том случае, если: каждый параметр упоминается хотя бы один раз, и все функции, операторы и другие конструкции, используемые в теле, сами являются .

Пример функции явно не подходит. Оба CASEпостроить и ARRAYКонструктор виноват согласно моим тестам.

Табличные функции (возвращающие набор строк) более требовательны, но:

  • функция не объявлена STRICT

Если функция не может быть встроена, повторное выполнение повторно собирает служебные данные функции. Разница в производительности стала больше в более поздних версиях Postgres.

Повторно протестируйте с PostgreSQL 13 на текущем ноутбуке. Разница еще больше:

      SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:   4 ms
SELECT f_sort_array_strict(arr) FROM tbl;  -- Total runtime:  32 ms

Тот же тест на dbfiddle.com, PostgreSQL 13 . Разница еще больше:

      SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:   4 ms
SELECT f_sort_tblay_strict(arr) FROM tbl;  -- Total runtime: 137 ms (!)

Комплексный тест, включающий тесты с половиной и всеми значениями NULL:

db<>скрипка здесь

Источник
Другие вопросы по тегам