postgresql: оптимизация SQL запроса в котором используется CASE с многократным вызовом хранимой функции

Question

postgresql: оптимизация SQL запроса в котором используется CASE с многократным вызовом хранимой функции

Рейтинг: 6Ответов: 4Опубликовано: 25.05.2023

следующая ситуация:

есть вот такой запрос:

select id, CASE
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) = 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) = 1) THEN 0
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) = 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) > 1) THEN 1
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) > 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) = 1) THEN 2
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) > 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) > 1) THEN 3
    ELSE null
END AS result 
from data_t
limit 10

запрос работает очень долго и хотелось бы его ускорить

я не знаю насколько PostreSQL умеет оптимизировать, но кажется, что get_tree(id, false) и get_tree(id, true) хорошо бы вызвать всего 1 раз, а не по 4 раза

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

Или в армии не дураки и запрос внутри движка и так будет оптимизирован и не надо париться?

Сама задача:

A = get_tree(id, false)
B = get_tree(id, false)

требуется выдавать число от 0 до 3 для 4 разных ситуаций

A = 1, B = 1
A > 1, B = 1
A = 1, B > 1
A > 1, B > 1
и остальное (для этого null)

sql postgresql case оптимизация оптимизация-запросов

Источник: Stack Overflow на русском

Answer 1

▲ 4Принят

Можно в OUTER APPLY вычислить get_tree(id, false) и get_tree(id, true) и в CASE уже работать с полученными результатами, что-то типа такого:

SELECT  id, 
        CASE    WHEN get_tree_false.cnt = 1 AND get_tree_true.cnt = 1   THEN 0
                WHEN get_tree_false.cnt = 1 AND get_tree_true.cnt > 1   THEN 1
                WHEN get_tree_false.cnt > 1 AND get_tree_true.cnt = 1   THEN 2
                WHEN get_tree_false.cnt > 1 AND get_tree_true.cnt > 1   THEN 3
                                                                        ELSE NULL
        END AS result 
FROM data_t
LEFT JOIN LATERAL
(
    SELECT COUNT(*) FROM get_tree(data_t.id, false) AS cnt
) AS get_tree_false ON TRUE
LEFT JOIN LATERAL
(
    SELECT COUNT(*) FROM get_tree(data_t.id, true) AS cnt
) AS get_tree_true ON TRUE
LIMIT 10;

Мог где-то ошибиться, писал по памяти, но суть надеюсь передал

Ещё предложен интересный вариант с LEFT JOIN, сравните оба подхода по времени, плану и было бы интересно узнать ваши успехи и что быстрее по итогу, к какому варианту пришли.

Answer 2

▲ 5

Преграда формальная: какой у вас указан уровень volatility функции? Результат VOLATILE функции по своему определению не может быть использован вторично, её необходимо вызвать.

Впрочем, планировщик не анализирует в такой мере запрос. Проверить можно

melkij=> create or replace function get_tree(i int, f bool) returns setof int as $$
begin
raise notice 'called % %', i, f;
end
$$ language plpgsql immutable;
CREATE FUNCTION
melkij=> explain (analyze,buffers) select id, CASE
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) = 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) = 1) THEN 0
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) = 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) > 1) THEN 1
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) > 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) = 1) THEN 2
    WHEN EXISTS(SELECT 1 FROM get_tree(id, false) HAVING COUNT(*) > 1) AND EXISTS(SELECT 1 FROM get_tree(id, true) HAVING COUNT(*) > 1) THEN 3
    ELSE null
END AS result 
from (select 1 as id) dummydata;
NOTICE:  called 1 f
NOTICE:  called 1 f
NOTICE:  called 1 f
NOTICE:  called 1 f
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=102.10..102.11 rows=1 width=8) (actual time=0.101..0.103 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (actual time=0.068..0.068 rows=0 loops=1)
           Filter: (count(*) = 1)
           Rows Removed by Filter: 1
           ->  Function Scan on get_tree  (cost=0.25..10.25 rows=1000 width=0) (actual time=0.065..0.065 rows=0 loops=1)
   InitPlan 2 (returns $1)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (never executed)
           Filter: (count(*) = 1)
           ->  Function Scan on get_tree get_tree_1  (cost=0.25..10.25 rows=1000 width=0) (never executed)
   InitPlan 3 (returns $2)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
           Filter: (count(*) = 1)
           Rows Removed by Filter: 1
           ->  Function Scan on get_tree get_tree_2  (cost=0.25..10.25 rows=1000 width=0) (actual time=0.009..0.009 rows=0 loops=1)
   InitPlan 4 (returns $3)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (never executed)
           Filter: (count(*) > 1)
           ->  Function Scan on get_tree get_tree_3  (cost=0.25..10.25 rows=1000 width=0) (never executed)
   InitPlan 5 (returns $4)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
           Filter: (count(*) > 1)
           Rows Removed by Filter: 1
           ->  Function Scan on get_tree get_tree_4  (cost=0.25..10.25 rows=1000 width=0) (actual time=0.009..0.009 rows=0 loops=1)
   InitPlan 6 (returns $5)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (never executed)
           Filter: (count(*) = 1)
           ->  Function Scan on get_tree get_tree_5  (cost=0.25..10.25 rows=1000 width=0) (never executed)
   InitPlan 7 (returns $6)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=1)
           Filter: (count(*) > 1)
           Rows Removed by Filter: 1
           ->  Function Scan on get_tree get_tree_6  (cost=0.25..10.25 rows=1000 width=0) (actual time=0.008..0.008 rows=0 loops=1)
   InitPlan 8 (returns $7)
     ->  Aggregate  (cost=12.75..12.76 rows=1 width=4) (never executed)
           Filter: (count(*) > 1)
           ->  Function Scan on get_tree get_tree_7  (cost=0.25..10.25 rows=1000 width=0) (never executed)
 Planning Time: 0.139 ms
 Execution Time: 0.179 ms
(39 rows)

4 вызова функции для получения null в case.

Проблема почему планировщик не делает такую оптимизацию, как обычно, проста - для этого нужно тратить ресурсы (CPU, время) на анализ запроса. То есть замедлять в некоторой мере абсолютно каждый запрос к базе. Посмотрите, даже здесь в синтетическом примере с функцией-заглушкой время планирования запроса едва ли не превышает время выполнения. Возможно усложнить планировщик так, чтобы он переписывал самостоятельно всякие разные запросы, но будет ли это действительно лучше, если каждый из самых простейших запросов будет планироваться не меньше десятков миллисекунд?

select id, CASE
    WHEN (get_tree_false = 1) AND (get_tree_true = 1) THEN 0
    WHEN (get_tree_false = 1) AND (get_tree_true > 1) THEN 1
    WHEN (get_tree_false > 1) AND (get_tree_true = 1) THEN 2
    WHEN (get_tree_false > 1) AND (get_tree_true > 1) THEN 3
    ELSE null
END AS result
from data_t
join lateral (
    select count(*) as get_tree_true from get_tree(id, false)
) get_tree_false_rs on true
join lateral (
    select count(*) as get_tree_false from get_tree(id, true)
) get_tree_true_rs on true
limit 10;

Поскольку если limit в запросе (добавьте какой-то order by для детерминированности), то нет нужны вычислять get_tree для всех строк таблицы, чтобы отбросить большинство. Для этих целей lateral join куда уместнее.

Answer 3

▲ 4

Здесь мы воспроизводим объединение двух get_tree по параметру false и true.

Обозначим за t1 вызов с false, а за t2 вызов с true, будем возвращать из них COUNT, группируем по id, а после этого соединим их с data_t.id:

SELECT
  data_t.id,
  CASE
    WHEN t1.count = 1 AND t2.count = 1 THEN 0
    WHEN t1.count = 1 AND t2.count > 1 THEN 1
    WHEN t1.count > 1 AND t2.count = 1 THEN 2
    WHEN t1.count > 1 AND t2.count > 1 THEN 3
    ELSE NULL
  END AS result
FROM
  data_t
LEFT JOIN (
  SELECT id AS t1_id, COUNT(*) AS count
  FROM get_tree(data_t.id, false)
  GROUP BY t1_id
) AS t1 ON data_t.id = t1.t1_id
LEFT JOIN (
  SELECT id AS t2_id, COUNT(*) AS count
  FROM get_tree(data_t.id, true)
  GROUP BY t2_id
) AS t2 ON data_t.id = t2.t2_id
LIMIT 10;

Answer 4

▲ 3

select id,
       case when false_cnt = 1 and true_cnt = 1 then 0
            when false_cnt = 1 and true_cnt > 1 then 1
            ...
        end
  from (
    select id,
           (SELECT count(1) FROM get_tree(id, false)) as false_cnt,
           (SELECT count(1) FROM get_tree(id, true)) as true_cnt
      from data_t limit 10
  ) x

postgresql: оптимизация SQL запроса в котором используется CASE с многократным вызовом хранимой функции

Ответы