pg_plan_filter: Restrict Total Cost
pg_plan_filter
is PostgreSQL extension to block execution of statements where query planner's estimate of the total cost exceeds a threshold. This is intended to give database administrators a way to restrict the contribution an individual query has on database load.
Enable the extension
pg_plan_filter
can be enabled on a per connection basis:
_10load 'plan_filter';
or for all connections:
_10alter database some_db set session_preload_libraries = 'plan_filter';
API
plan_filter.statement_cost_limit
: restricts the maximum total cost for executed statements
plan_filter.limit_select_only
: restricts to select
statements
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
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:
_10create table book(_10 id int primary key_10);_10-- CREATE TABLE_10_10insert into book(id) select * from generate_series(1, 10000);_10-- INSERT 0 10000
Next, we can review the explain plans for a single record select, and a whole table select.
_12explain select * from book where id =1;_12 QUERY PLAN_12---------------------------------------------------------------------------_12 Index Only Scan using book_pkey on book (cost=0.28..2.49 rows=1 width=4)_12 Index Cond: (id = 1)_12(2 rows)_12_12explain select * from book;_12 QUERY PLAN_12---------------------------------------------------------_12 Seq Scan on book (cost=0.00..135.00 rows=10000 width=4)_12(1 row)
Now we can choose a statement_cost_filter
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.
_10load 'plan_filter';_10set plan_filter.statement_cost_limit = 50; -- between 2.49 and 135.0_10_10select * from book where id = 1;_10 id_10----_10 1_10(1 row)_10-- SUCCESS
_10select * from book;_10_10ERROR: plan cost limit exceeded_10HINT: 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"._10-- FAILURE
Resources
- Official
pg_plan_filter
documentation