Skip to content
Database

pg_plan_filter：限制总成本

pg_plan_filter 是一个 Postgres 扩展，用于阻止查询计划器估算的总成本超过阈值的语句执行。它的目的是为数据库管理员提供一种方式，限制单个查询对数据库负载的影响。

启用扩展 #

🌐 Enable the extension

这个扩展默认已经通过 shared_preload_libraries 设置启用了。

🌐 The extension is already enabled by default via shared_preload_libraries setting.

你可以按照下面的指示操作。

🌐 You can follow the instructions below.

应用接口 #

🌐 API

plan_filter.statement_cost_limit：限制已执行语句的最大总费用 plan_filter.limit_select_only：限制为 select 条语句

注意，limit_select_only = true 不等同于只读，因为 select 语句可能会修改数据，例如通过函数调用。

🌐 Note that limit_select_only = true is not the same as read-only because select statements may modify data, for example, through a function call.

示例 #

🌐 Example

为了演示总成本过滤，我们将比较 plan_filter.statement_cost_limit 如何处理低于和超过其成本限制的查询。首先，我们设置一个包含一些数据的表格：

🌐 To demonstrate total cost filtering, we'll compare how plan_filter.statement_cost_limit treats queries that are under and over its cost limit. First, we set up a table with some data:

create table book(
  id int primary key
);
-- CREATE TABLE
insert into book(id) select * from generate_series(1, 10000);
-- INSERT 0 10000

接下来，我们可以查看单条记录查询和全表查询的执行计划。

🌐 Next, we can review the explain plans for a single record select, and a whole table select.

explain select * from book where id =1;
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using book_pkey on book  (cost=0.28..2.49 rows=1 width=4)
   Index Cond: (id = 1)
(2 rows)
explain select * from book;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on book  (cost=0.00..135.00 rows=10000 width=4)
(1 row)

现在我们可以在单选总成本（2.49）和整个表的选择总成本（135.0）之间选择一个 statement_cost_limit 值，这样一个语句会成功，一个会失败。

🌐 Now we can choose a statement_cost_limit value between the total cost for the single select (2.49) and the whole table select (135.0) so one statement will succeed and one will fail.

set plan_filter.statement_cost_limit = 50; -- between 2.49 and 135.0
select * from book where id = 1;
 id
----
  1
(1 row)
-- SUCCESS
select * from book;
ERROR:  plan cost limit exceeded
HINT:  The plan for your query shows that it would probably have an excessive run time. This may be due to a logic error in the SQL, or it maybe just a very costly query. Rewrite your query or increase the configuration parameter "plan_filter.statement_cost_limit".
-- FAILURE

资源 #

🌐 Resources