Database

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:


_10
load 'plan_filter';

or for all connections:


_10
alter 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:


_10
create table book(
_10
id int primary key
_10
);
_10
-- CREATE TABLE
_10
_10
insert 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.


_12
explain 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
_12
explain 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.


_10
load 'plan_filter';
_10
set plan_filter.statement_cost_limit = 50; -- between 2.49 and 135.0
_10
_10
select * from book where id = 1;
_10
id
_10
----
_10
1
_10
(1 row)
_10
-- SUCCESS


_10
select * from book;
_10
_10
ERROR: plan cost limit exceeded
_10
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".
_10
-- FAILURE

Resources