我正在使用Postgresql 9.2.9并且存在以下问题.
有功能：
CREATE OR REPLACE FUNCTION report_children_without_place(text,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',2014);
总运行时间：242805.085 ms.
但是从函数体的查询执行得更快：
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,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','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','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;
您的查询运行得更快,因为“变量”实际上不是变量 – 它们是静态值(引号中的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非常有用,因为当我设置client_min_messages = DEBUG时,您实际上可以获得对查询的解释.我可以从屏幕上抓取查询并在EXPLAIN或EXPLAIN ANALYZE之后将其粘贴回来,看看执行计划程序正在做什么.这也允许您根据需要构建非常不同的查询以优化变量(如果有保证,IE排除不必要的表)并为您的客户维护一个通用API.
您可能会因为担心性能问题而避免使用动态sql(我刚开始)但是您会惊讶于计划花费的时间与七桌上几次表扫描的一些成本相比加入！
祝好运！
后续行动：您也可以尝试使用公用表格表达式(CTE)来提高性能.如果你的表具有较低的信噪比(其中有许多,多于你实际想要返回的记录),那么CTE可能非常有用. Postgresql在查询的早期执行CTE,并在内存中实现结果行.这允许您在查询中多次和多个位置使用相同的结果集.如果你正确设计它的好处真的会令人惊讶.
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.我已经使用这个“技巧”来替换非常复杂的连接中的子查询,并看到了很大的改进.
快乐黑客！
