使用pl/pgsql查询计划缓存 [英] Query plan caching with pl/pgsql

问题描述

我很难理解pl/pgsql的查询计划缓存如何工作.

I am having troubles understanding how the query plan caching works for pl/pgsql.

我想用JOIN s和IF s建立多合一查询，因此我将有多个不同的查询参数，并且我将在一个以上的表中进行搜索.

I want to built all-in-one queries with JOINs and IFs, so I will have multiple and different query parameters, and I will be searching in more that one tables.

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

At first I thought that using pl/pgsql will produce a different plan for each parameters combination, that is not the case, because I have more than one tables

直接出现在PL/pgSQL函数中的

SQL命令必须参考 每次执行时都有相同的表和列；也就是说，你不能 在SQL命令中使用参数作为表或列的名称.到 克服此限制，您可以使用以下命令构造动态命令 PL/pgSQL EXECUTE语句—以执行新的解析为代价 分析并为每次执行构建新的执行计划. 从这里

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然后

Performing a new analysis every time can slow things down I guess. If I dont use an EXECUTE then

如果该语句没有参数，或者已多次执行，则SPI 经理将考虑创建不依赖于 特定的参数值，并将其缓存以供重用.通常这 仅当执行计划对执行情况不是很敏感时才会发生 其中引用的PL/pgSQL变量的值.如果是， 每次产生一个计划都是一个净赢. 从这里

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

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

Should I use a generic plan then? Is it faster, or is it slower because there is no planning each time ? At least they are cached. My queries are sensitive to their variables, because they are dynamic, but what

如果是的话，每次生成计划都是净赢.

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

实际上是什么意思?每次使用EXECUTE/plan，比普通的更好还是更坏? 净赢"使我感到困惑.

actually means? That using EXECUTE/plan each time, is better or worst than a generic one? "net win" confuses me.

如果通用计划不准确，并且每次EXECUTE/计划都较慢，那么为什么还要麻烦地使用pl/pgsql?我可以用几个ifs编写一个简单的查询.

If a generic plan is inaccurate and EXECUTE/planning each time is slower, then why bother using pl/pgsql? I can write a simple query with a couple of ifs then.

最重要的是，在速度和计划缓存方面，我无法断定EXECUTE/plan each time是好于还是差于generic cached plan.请解释和咨询，我很困惑.

Bottom line is, I cannot conclude if EXECUTE/plan each time is better or worst than generic cached plan in terms of speed and plan caching. Please explain and advice, I am confused.

这是我正在创建的供参考.现在可以使用，但是将为mytables和mywhere

For reference, this is what I am creating. Works as it is now, but more IFs will be added for mytables and mywhere

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

谢谢

推荐答案

始终会缓存静态查询计划(不包含EXECUTE)，而不能缓存动态查询计划(具有EXECUTE).

Plans for static queries (without EXECUTE) are always cached, plans for dynamic queries (with EXECUTE) cannot be cached.

对于您而言，无论如何都无法使用静态查询，因为如您所引用的，这意味着您只能在查询中使用一组固定的表.

In your case, it would be impossible to use a static query anyway, because, as you quote, that would mean that you can only use a fixed set of tables in your query.

我认为您对文档中静态查询和动态查询之间的权衡取舍感到困惑.

I gather that you are confused by the discussion of the trade-offs between static and dynamic queries in the documentation.

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

Definition: query parameters are values that are not part of the query string, like $1 or a PL/pgSQL variable name in a static query.

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

对于前5次执行，将使用实际参数值(自定义计划")进行计划，并且如果估算的执行时间不比忽略实际参数值的计划(通用计划")，则将从第六次执行开始使用通用计划.

For the first 5 executions, it will be planned using the actual parameter values (“custom plan”), and if the estimated execution time is not significantly shorter than that of a plan that ignores the actual parameter values (“generic plan”), the generic plan will be used from the sixth execution on.

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

Since the generic plan is cached, that means that there is no planning cost from the sixth execution on.

动态查询.

权衡如下:动态查询每次执行时都会增加计划成本，但是由于始终使用实际参数值来计划动态查询，因此最终会得到更好的执行计划，从而可以节省查询执行时间

The trade-off is the following: dynamic queries run up planning cost whenever they are executed, but since they are always planned with the actual parameter values, they end up with a better execution plan, which can save time during query execution.

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

Now if a query is sensitive to parameter values, that means that the optimal plan will vary significantly with the parameter values, so you will usually win if you plan the query every time.

没有参数的查询将始终从计划缓存中受益，除非表内容在单个会话的生存期内发生很大变化，从而使缓存的计划变得次优.

Queries without parameters will always profit from plan caching, unless the table contents change a lot during the lifetime of a single session, so that the cached plan becomes suboptimal.

这篇关于使用pl/pgsql查询计划缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！