我正在尝试实现一个带有可选参数的postgres函数。我的方法是用 required 参数实现稳定的SQL函数,然后我委托给那些依赖于简单IF/ELSEIF case链的函数。
下面是一个简单的例子:
安装程序:
CREATE TABLE IF NOT EXISTS test_tab1(
field1 int PRIMARY KEY,
field2 int
);
CREATE TABLE IF NOT EXISTS test_tab2(
field1 int PRIMARY KEY,
field2 int
);
INSERT INTO test_tab1 SELECT FLOOR(RANDOM()*10000), FLOOR(RANDOM()*10000) FROM GENERATE_SERIES(1,10);
INSERT INTO test_tab2 SELECT * FROM test_tab1;
CREATE OR REPLACE FUNCTION test_tab1_func()
RETURNS SETOF test_tab1
LANGUAGE sql STABLE
AS $$
SELECT * FROM test_tab1;
$$;
CREATE OR REPLACE FUNCTION test_tab2_func()
RETURNS SETOF test_tab1
LANGUAGE sql STABLE
AS $$
SELECT * FROM test_tab2;
$$;
字符串
我的问题的主题是:
CREATE OR REPLACE FUNCTION test_func(foo int DEFAULT null)
RETURNS SETOF test_tab1
LANGUAGE plpgsql STABLE
AS $$
BEGIN
IF (foo IS null) THEN
RETURN QUERY
SELECT * FROM test_tab1_func();
ELSE
RETURN QUERY
SELECT * FROM test_tab2_func();
END IF;
END;
$$;
EXPLAIN SELECT * FROM test_func();
型
此EXPLAIN
返回:
Function Scan on test_func (cost=0.25..10.25 rows=1000 width=8)
型
我对更复杂的查询的实验表明,这个Function Scan
估计不是很“聪明”,无论函数体的复杂程度如何,它都会返回基本相同的结果。由于EXPLAIN
输出的信息太少,我来这里是为了了解更多关于执行计划的信息。
我的问题是:由于数据库可以为test_tab1_func
进行规划,也可以为test_tab2_func
进行规划,那么当foo
是null
时,我是否可以指望数据库使用它通常为test_func
设计的test_tab1_func
规划,否则它通常为test_func
设计的test_tab2_func
规划?如果没有,有没有更好的方法来实现这种“case/switch”函数逻辑,让postgres更容易规划?
2条答案
按热度按时间sqougxex1#
我能指望一个简单的case/switch plpgsql函数来使用一个合理的查询计划吗?
是的,你总是可以依靠“正常”的查询计划-在普通SQL中或嵌套在任何函数中。它总是相同的查询计划,只是从不同的上下文调用。一个“疯狂”的查询计划将导致向PostgreSQL错误列表报告错误< email protected(https://stackoverflow.com/cdn-cgi/l/email-protection) >-在确保疯狂不全是你之后。
否,您不能指望 * 一个 * 合理的查询计划,因为函数中的每个嵌套SQL语句都有自己的计划-除非函数作为一个整体是inlined,这只适用于满足几个条件的SQL函数(
LANGUAGE sql
)。因此不适用于PL/pgSQL函数。您的调用将产生一个用于调用
SELECT * FROM test_func();
的计划和另一个用于SELECT * FROM test_tab1_func();
的单独(嵌套)计划。IF
构造的另一个分支中的SELECT * FROM test_tab2_func();
根本没有计划,直到控制实际到达它。要查看幕后发生的每一个细节,请使用
auto_explain
。它很简单,但您必须是超级用户。请参阅:相关:
您的示例
如果将查询沿着用户输入拆分为不同的查询,至少可以为一个分支带来更高效的查询计划,那么这样做就有意义了。但对于您的简单示例来说,情况并非如此,在此示例中,增加的开销远远超过了可能的收益。
你的函数
test_func()
和call可以用普通的SQL替换:字符串
roejwanj2#
您的示例已更新,以包含函数结果的差异:
字符串
添加
VERBOSE
和ANALYZE
表明Postgres区分了这两个函数,并跟踪了每个函数的运行情况:型