Потеря производительности из-за бокового соединения в запросе Postgres

Когда операция EXISTS находится непосредственно в запросе, PostgreSQL может преобразовать подзапрос в семантически эквивалентный JOIN; однако, когда операция EXISTS скрыта за вызовом функции, механизм запросов больше не имеет информации, необходимой для выполнения такой оптимизации. Выполнение EXPLAIN (ANALYZE, BUFFERS, VERBOSE) для каждого запроса должно прояснить причину разницы в производительности: включение EXISTS в функцию приводит к тому, что подзапрос выполняется для каждой строки вместо использования более эффективного JOIN.

Вы не показали фактические планы запросов (вывод EXPLAIN (ANALYZE, BUFFERS, SETTINGS)), но в вашей функции есть несколько ловушек производительности. Перепишите как:

CREATE OR REPLACE FUNCTION does_t2_record_exist(_object_id int8, _table_name text)
  RETURNS bool
  LANGUAGE sql STABLE PARALLEL SAFE AS  -- !
$func$
SELECT EXISTS (
   SELECT FROM table2 t2
   WHERE  t2.fk = _object_id
   AND    lower(t2.table_name) = _table_name
   );
$func$;

И ваш запрос тогда просто:

SELECT ...
     , does_t2_record_exist(t1.id, 't1') AS t2_record_exists
FROM   table1 t1

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

Выражение lower(t2.table_name) не является жаргонным . Обязательно наличие многостолбцового B-дерева (по умолчанию) и индекса выражения на table2(fk, lower(tablename)). (Или только в одном из обоих индексных полей, если оно само по себе достаточно избирательно.)

Для простой функции вам не нужен PL/pgSQL. Переключение на LANGUAGE sql возможно позволит встроить функцию.

Добавьте метки функций PARALLEL SAFE и STABLE (потому что они оба действительно применимы!), чтобы обеспечить различные оптимизации производительности.

Тем не менее, во всяком случае, функция может быть медленнее, чем помещение выражения EXISTS с коррелированным подзапросом непосредственно в список SELECT. Но очень немного, если все делать правильно.

Тем не менее, соединение LATERAL или ваша функция, выполняющая коррелированный подзапрос, должна выполняться один раз для каждой соответствующей строки запроса. Если в результате будет запрошено большинство строк, которые пришлось бы обрабатывать простому подзапросу, соединение (не LATERAL) по-прежнему будет самым быстрым вариантом. (Или исходный запрос, который может привести к тому же плану запроса.) Например:

SELECT ...
     , t2.fk IS NOT NULL AS t2_record_exists
FROM   table1 t1
LEFT   JOIN (
   SELECT DISTINCT t2.fk  -- do we even need DISTINCT?
   FROM   table2 t2
   WHERE  lower(t2.table_name) = 't1'
   ) t2 ON t2.fk = t1.id

Видеть:

• В чем разница между LATERAL JOIN и подзапросом в PostgreSQL?

Добавленные планы запросов показывают...

В вашей таблице table2 всего 372 строки, 113 из которых фильтруются. Это крошечный. Вам не понадобится индекс! Postgres делает это с помощью последовательного сканирования исходного запроса одним махом.
Заключение функциональности в «черный ящик» функции PL/pgSQL заставило Postgres выполнять ее loops=1477585 раз. Плохо.

Моя переписанная версия может это исправить. Или вернитесь к исходному запросу. (Или мой добавленный альтернативный вариант.)

Речь идет не о боковом соединении как таковом. Функция действует как барьер оптимизации. Функция вызывается один раз для каждой строки, а не оптимизируется для массовой обработки с использованием хеш-таблиц или хэш-соединений.

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

Если представление обычно используется с очень ограничительным предложением WHERE, которое будет помещено в таблицу table1, это может оказаться преимуществом, поскольку массовая операция может быть неэффективной, когда потребуется всего несколько строк.

Вы можете использовать какую-то систему макросов для упрощения повторяющегося кода. PostgreSQL не предоставляет такого, это будет зависеть от вашего репозитория кода, инфраструктуры или того, что вы используете для управления своим кодом.