postgresql - 使用 pl/pgsql 查询计划缓存

Question

我无法理解 pl/pgsql 的查询计划缓存是如何工作的。

我想用 JOIN 和 IF 构建一体化查询，所以我会有多个不同的查询参数，我将在不止一张 table 。

起初我以为使用pl/pgsql会为每个参数组合产生不同的计划，事实并非如此，因为我有多个表

SQL commands that appear directly in a PL/pgSQL function must refer to the same tables and columns on every execution; that is, you cannot use a parameter as the name of a table or column in an SQL command. To get around this restriction, you can construct dynamic commands using the PL/pgSQL EXECUTE statement — at the price of performing new parse analysis and constructing a new execution plan on every execution. from here

我猜每次都执行新的分析会减慢速度。如果我不使用 EXECUTE 然后

If the statement has no parameters, or is executed many times, the SPI manager will consider creating a generic plan that is not dependent on specific parameter values, and caching that for re-use. Typically this will happen only if the execution plan is not very sensitive to the values of the PL/pgSQL variables referenced in it. If it is, generating a plan each time is a net win. from here

那我应该使用通用计划吗？是更快还是更慢，因为每次都没有计划？至少它们被缓存了。我的查询对其变量很敏感，因为它们是动态的，但是什么

If it is, generating a plan each time is a net win.

究竟是什么意思？每次都使用 EXECUTE/plan 比通用的好还是坏？ “净赢”让我感到困惑。

如果通用计划不准确并且每次EXECUTE/planning 都比较慢，那么为什么还要使用 pl/pgsql？然后我可以用几个 if 写一个简单的查询。

底线是，就速度和计划缓存而言，我无法断定 EXECUTE/plan each time 比 generic cached plan 好还是坏。请解释和指教，我很困惑。

作为引用，这就是我正在创建的。像现在一样工作，但将为 mytables 和 mywhere

添加更多 IF

DROP FUNCTION IF EXISTS __aa(ii int, fk int);
CREATE  FUNCTION __aa(ii int, fk int) RETURNS TABLE(id INTEGER,val text, fd integer) AS $$
DECLARE 
myt text;
mytables text;
mywhere text;
BEGIN

mytables := 'dyn_tab2';
mywhere := 'dyn_tab2.id=$1';
IF fk IS NOT NULL
THEN
mywhere := mywhere || 'AND dyn_tab2.fk_id=$2';
END IF;

RETURN QUERY EXECUTE format('
SELECT dyn_tab2.id, dyn_tab2.value, dyn_tab2.fk_id 
FROM %I WHERE ' ||mywhere,
mytables) 
USING ii, fk;

END;
$$
LANGUAGE 'plpgsql';

谢谢

Answer 1

静态查询计划(没有EXECUTE)总是被缓存，动态查询计划(有EXECUTE)不能被缓存。

在您的情况下，无论如何都不可能使用静态查询，因为正如您引用的那样，这意味着您只能在查询中使用一组固定的表。

我了解到您对文档中静态查询和动态查询之间的权衡的讨论感到困惑。

定义:查询参数是不属于查询字符串的值，例如 $1 或静态查询中的 PL/pgSQL 变量名。

对于静态查询，流程如下:

对于前 5 次执行，将使用实际参数值(“自定义计划”)进行计划，并且如果估计的执行时间没有明显短于忽略实际参数值的计划(“通用计划”) "),从第6次执行开始使用通用计划。

由于缓存了通用计划，这意味着从第六次执行开始就没有计划成本。

动态查询每次执行时都会进行计划。

权衡如下:动态查询在执行时会增加计划成本，但由于它们总是使用实际参数值进行计划，因此它们最终会得到更好的执行计划，这可以在查询执行期间节省时间.

现在，如果查询对参数值敏感，这意味着最优计划将随参数值的不同而显着变化，因此如果您每次都计划查询，通常会获胜。

没有参数的查询将始终受益于计划缓存，除非表内容在单个 session 的生命周期内发生很大变化，从而缓存的计划变得次优。