为什么将查询放入函数后执行查询的速度慢了76倍? [英] why the query is executed 76 times slower when I put it into function?

问题描述

当我将下一个查询放入函数中时，它的运行速度降低了76倍. 计划中唯一的区别是:位图索引扫描VS索引扫描

When I put next query into function it goes 76times slower. The only difference at plan is: bitmap-index scan VS index scan

Plan1: http://tatiyants.com/pev/#/plans/plan_1562919134481

Plan2: http://tatiyants.com/pev/#/plans/plan_1562918860704

plan1

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
;

计划2

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
$$
;

为什么要对函数内部的查询在上一次子查询扫描时进行过滤?

是否可以做一些事情使它们平等地工作?

Is it possible to do something so that they work equally?

UPD
服务器版本为PostgreSQL 12beta2
由于30000个字符的限制，我在此处和

UPD
Server version is PostgreSQL 12beta2
Because of 30000 characters limit I post plans here and here

推荐答案

感谢IRC中的 RhodiumToad :

我怀疑是某种原因阻止了计划人员推断(t.o).id可以安全地通过ocd.o进行GROUP BY

I suspect something's stopping the planner from being able to deduce that (t.o).id is safe to push through a GROUP BY ocd.o

可以通过将其单独放置为单独的列来解决

that might be fixable by making it a separate column of its own

因此，我另外在GROUP BY odc.id列中.所以我的最后一个查询是:

Thus I additionally GROUP BY odc.id column. So my final query is:

    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字段进行排序:

This change also makes call via function faster. I just need to sort via order_id field:

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

这篇关于为什么将查询放入函数后执行查询的速度慢了76倍?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！