Postgresql 函数执行的时间比同一个查询长得多

Question

我正在使用 PostgreSQL 9.2.9 并遇到以下问题。

有函数:

CREATE OR REPLACE FUNCTION report_children_without_place(text, date, date, integer)
RETURNS TABLE (department_name character varying, kindergarten_name character varying, a1 bigint) AS $BODY$
BEGIN
    RETURN QUERY WITH rh AS (
        SELECT (array_agg(status ORDER BY date DESC))[1] AS status, request
        FROM requeststatushistory
        WHERE date <= $3
        GROUP BY request
    )
    SELECT
        w.name,
        kgn.name,
        COUNT(*)
    FROM kindergarten_request_table_materialized kr
    JOIN rh ON rh.request = kr.id
    JOIN requeststatuses s ON s.id = rh.status AND s.sysname IN ('confirmed', 'need_meet_completion', 'kindergarten_need_meet')
    JOIN workareas kgn ON kr.kindergarten = kgn.id AND kgn.tree <@ CAST($1 AS LTREE) AND kgn.active
    JOIN organizationforms of ON of.id = kgn.organizationform AND of.sysname IN  ('state','municipal','departmental')
    JOIN workareas w ON w.tree @> kgn.tree AND w.active
    JOIN workareatypes mt ON mt.id = w.type AND mt.sysname = 'management'
    WHERE kr.requestyear = $4
    GROUP BY kgn.name, w.name
    ORDER BY w.name, kgn.name;
END
$BODY$ LANGUAGE PLPGSQL STABLE;

EXPLAIN ANALYZE SELECT * FROM report_children_without_place('83.86443.86445', '14-04-2015', '14-04-2015', 2014);

总运行时间:242805.085 毫秒。但是来自函数主体的查询执行得更快:

EXPLAIN ANALYZE WITH rh AS (
SELECT (array_agg(status ORDER BY date DESC))[1] AS status, request
FROM requeststatushistory
WHERE date <= '14-04-2015'
GROUP BY request
)
SELECT
    w.name,
    kgn.name,
    COUNT(*)
FROM kindergarten_request_table_materialized kr
JOIN rh ON rh.request = kr.id
JOIN requeststatuses s ON s.id = rh.status AND s.sysname IN ('confirmed', 'need_meet_completion', 'kindergarten_need_meet')
JOIN workareas kgn ON kr.kindergarten = kgn.id AND kgn.tree <@ CAST('83.86443.86445' AS LTREE) AND kgn.active
JOIN organizationforms of ON of.id = kgn.organizationform AND of.sysname IN  ('state','municipal','departmental')
JOIN workareas w ON w.tree @> kgn.tree AND w.active
JOIN workareatypes mt ON mt.id = w.type AND mt.sysname = 'management'
WHERE kr.requestyear = 2014
GROUP BY kgn.name, w.name
ORDER BY w.name, kgn.name;

总运行时间:2156.740 毫秒。为什么函数执行的时间比同一个查询要长？谢谢

Answer 1

您的查询运行得更快，因为“变量”实际上不是变量——它们是静态值(引号中的 IE 字符串)。这意味着执行计划者可以利用索引。在您的存储过程中，您的变量是实际变量，规划器无法对索引做出假设。例如 - 您可能在 requeststatushistory 上有一个部分索引，其中“date”是 <= '2012-12-31'。仅当 $3 已知时才能使用该索引。由于它可能包含 2015 年的日期，因此部分索引将毫无用处。事实上，这是有害的。

我经常在我的函数中构造一个字符串，在其中我将我的变量作为文字连接起来，然后使用类似下面的内容执行该函数:

DECLARE
    my_dynamic_sql TEXT;
BEGIN
    my_dynamic_sql := $$
        SELECT * 
        FROM my_table 
        WHERE $$ || quote_literal($3) || $$::TIMESTAMPTZ BETWEEN start_time
                                                             AND end_time;$$;

    /* You can only see this if client_min_messages = DEBUG */
    RAISE DEBUG '%', my_dynamic_sql; 
    RETURN QUERY EXECUTE my_dynamic_sql;
END;

动态 SQL 非常有用，因为当我设置了 set client_min_messages=DEBUG; 时，您实际上可以获得查询的解释；我可以从屏幕上抓取查询并将其粘贴回 >EXPLAIN 或 EXPLAIN ANALYZE 并查看执行计划程序在做什么。这还允许您根据需要构建非常不同的查询以优化变量(IE 排除不必要的表，如果有保证)并为您的客户维护一个通用的 API。

您可能会因为担心性能问题而试图避免使用动态 SQL(我一开始就是这样)，但您会惊奇地发现，与在您的计算机上进行几次表扫描的成本相比，在规划上花费的时间是多么少七 table 加入!

祝你好运!

跟进:您也可以尝试使用 Common Table Expressions (CTE) 来提高性能。如果您有一个低信噪比的表(其中的记录比您实际想要返回的记录多得多)，那么 CTE 会非常有用。 PostgreSQL 在查询的早期执行 CTE，并在内存中具体化结果行。这允许您在查询的多个位置多次使用相同的结果集。如果设计得当， yield 真的会令人惊讶。

sql_txt := $$
WITH my_cte as (
   select fk1 as moar_data 1
        , field1
        , field2 /*do not need all other fields taking up RAM!*/
   from my_table
   where field3 between $$ || quote_literal(input_start_ts) || $$::timestamptz
                    and $$ || quote_literal(input_end_ts) || $$::timestamptz
                ),
      keys_cte as ( select key_field
                    from big_look_up_table
                    where look_up_name = ANY($$ || 
                         QUOTE_LITERAL(input_array_of_names) || $$::VARCHAR[])
                  )
SELECT field1, field2, moar_data1, moar_data2
FROM moar_data_table
INNER JOIN my_cte
  USING (moar_data1)
WHERE moar_data_table.moar_data_key in (select key_field from keys_cte) $$;

执行计划很可能表明它选择在 moar_data_tale.moar_data_key 上使用索引。这似乎与我在之前的回答中所说的相反——除了 keys_cte 结果是具体化的(因此不能被竞争条件下的另一个事务更改)——你拥有您自己的小数据副本以用于此查询。

哦 - CTE 可以使用先前在同一查询中声明的其他 CTE。我已经使用这个“技巧”来替换非常复杂的连接中的子查询并看到了很大的改进。

黑客攻击快乐!