Почему запрос выполняется в 76 раз медленнее, когда я помещаю его в функцию?

Question

Когда я помещаю следующий запрос в функцию, он идет в 76 раз медленнее. Единственное отличие в плане: сканирование растрового индекса и сканирование индекса

Plan1: http://tatiyants.com/pev/

Plan2: http://tatiyants.com/pev/

плана1

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
        SELECT
            sum( t.group_suma ) OVER( PARTITION BY (t.o).id ) AS total_suma,
            *
        FROM (
            SELECT
             sum( ocd.item_cost     ) AS group_cost,
             sum( ocd.item_suma     ) AS group_suma,
             max( (ocd.ic).consumed ) AS consumed,
             (ocd.ic).consumed_period,
             ocd.o
            FROM order_cost_details( tstzrange( '2019-04-01', '2019-05-01' ) ) ocd
            GROUP BY ocd.o, (ocd.ic).consumed_period
        ) t
WHERE (t.o).id IN ( 6154 ) AND t.consumed_period @> '2019-04-01'::timestamptz
;

Plan2

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT * FROM order_total_suma( tstzrange( '2019-04-01', '2019-05-01' ) ) ots 
WHERE (ots.o).id IN ( 6154 ) AND ots.consumed_period @> '2019-04-01'::timestamptz
;

Функция:

CREATE FUNCTION "order_total_suma" (in _target_range tstzrange default app_period())
 RETURNS    table(
        total_suma  double precision,
        group_cost  double precision,
        group_suma  double precision,
        consumed    double precision,
        consumed_period tstzrange,
        o order_bt
    )

 LANGUAGE sql
 STABLE
 AS $$
    SELECT
        sum( t.group_suma ) OVER( PARTITION BY (t.o).id ) AS total_suma,
        *
    FROM (
        SELECT
         sum( ocd.item_cost     ) AS group_cost,
         sum( ocd.item_suma     ) AS group_suma,
         max( (ocd.ic).consumed ) AS consumed,
         (ocd.ic).consumed_period,
         ocd.o
        FROM order_cost_details( _target_range ) ocd
        GROUP BY ocd.o, (ocd.ic).consumed_period
    ) t
$$
;

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

Можно ли что-то сделать, чтобы они работали одинаково?

UPD
Версия сервера PostgreSQL 12beta2
Из-за ограничения в 30000 символов я размещаю планы здесь и здесь

Answer 1

Спасибо RhodiumToad от IRC:

Я подозреваю, что что-то мешает планировщику определить, что (to).id безопасно проходить через GROUP BY ocd.o

это можно исправить, сделав его отдельным столбцом

Таким образом, я дополнительно GROUP BY столбец odc.id. Итак, мой последний запрос:

    SELECT * FROM (
            SELECT
                sum( t.group_suma ) OVER( PARTITION BY t.order_id ) AS total_suma,
--              sum( t.group_suma ) OVER( PARTITION BY (t.o).id ) AS total_suma,  -- For any WHERE this takes 2700ms
                *
            FROM (
                SELECT
                 sum( ocd.item_cost     ) AS group_cost,
                 sum( ocd.item_suma     ) AS group_suma,
                 max( (ocd.ic).consumed ) AS consumed,
                 (ocd.ic).consumed_period,
                 ocd.o,
                 (ocd.o).id as order_id
                FROM order_cost_details( tstzrange( '2019-04-01', '2019-05-01' ) ) ocd
                GROUP BY ocd.o, (ocd.o).id, (ocd.ic).consumed_period
            ) t
    ) t
    WHERE t.order_id = 6154 AND t.consumed_period @> '2019-04-01'::timestamptz       -- This takes 2ms
--  WHERE (t.o).id = 6154 AND t.consumed_period @> '2019-04-01'::timestamptz   -- This takes 2700ms

Это изменение также ускоряет вызов через функцию. Мне просто нужно отсортировать через order_id поле:

SELECT * FROM order_total_suma( tstzrange( '2019-04-01', '2019-05-01' ) ) ots 
-- This WHERE takes 2.5ms
WHERE ots.order_id IN ( 6154 ) AND ots.consumed_period @> '2019-04-01'::timestamptz
-- This WHERE takes 2500ms
-- WHERE (ots.o).id IN ( 6154 ) AND ots.consumed_period @> '2019-04-01'::timestamptz

Answer 2

Планы совсем другие.

Проблема в том, что в результате подсчета результатов соединения между public.order_bt и split_period подзапрос. Это вызывает функцию public.service_level_price будет оцениваться 2882 раза, а не один раз, где время тратится.

Не уверен, что с этим делать (у нас нет определения представления, и это, вероятно, противно). Поднятие COST функции, вероятно, не помогает, так как оптимизатор считает, что вызовет ее только один раз.

На самом деле, лучшая ставка может быть

ALTER FUNCTION public.calc_item_suma ROWS 1;

что может заставить оптимизатора выбрать другой план.