plan_filter

pg_plan_filter : filter statements by their execution plans.

Overview

Packages

Source

pig build pkg pg_plan_filter;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_plan_filter;		# install via package name, for the active PG version
pig install plan_filter;		# install by extension name, for the current active PG version

pig install plan_filter -v 18;   # install for PG 18
pig install plan_filter -v 17;   # install for PG 17
pig install plan_filter -v 16;   # install for PG 16
pig install plan_filter -v 15;   # install for PG 15
pig install plan_filter -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'plan_filter';

This extension does not need CREATE EXTENSION DDL command

Usage

plan_filter: filter statements by their execution plans

The pg_plan_filter module tests statements against specific configured criteria before execution, raising an error if the criteria are violated. This allows administrators to prevent execution of certain queries on production databases.

The only criterion currently supported is the maximum allowed estimated cost of the statement plan.

Configuration

Add to postgresql.conf:

shared_preload_libraries = 'plan_filter'
plan_filter.statement_cost_limit = 100000.0

The statement_cost_limit must be set to a non-zero value to enable filtering. The default is 0 (no filtering).

GUC Parameters

Examples

Prevent expensive queries globally:

plan_filter.statement_cost_limit = 100000.0

Limit filtering to SELECT statements only (note: SELECT != READONLY, since SELECT can also modify data):

plan_filter.limit_select_only = true

When the module is running with a non-zero statement_cost_limit, it will also prevent EXPLAIN on expensive queries. Temporarily bypass the filter:

BEGIN;
SET LOCAL plan_filter.statement_cost_limit = 0;
EXPLAIN SELECT ...;
COMMIT;

Override the limit per user:

ALTER USER can_run_expensive SET plan_filter.statement_cost_limit = 0;
ALTER USER only_cheap_queries SET plan_filter.statement_cost_limit = 10000;

Caveats

The statement_cost_limit cancels plans based on their estimated cost. The PostgreSQL planner can return cost estimates unrelated to actual query execution time. Be prepared for false positive cancellations and set the limit generously.