plan_filter

Overview

Version

Build

You can build the RPM / DEB packages for pg_plan_filter using pig build:

pig build pkg pg_plan_filter         # build RPM / DEB packages

Install

You can install pg_plan_filter directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install pg_plan_filter;          # Install for current active PG version

pig ext install -y pg_plan_filter -v 18  # PG 18
pig ext install -y pg_plan_filter -v 17  # PG 17
pig ext install -y pg_plan_filter -v 16  # PG 16
pig ext install -y pg_plan_filter -v 15  # PG 15
pig ext install -y pg_plan_filter -v 14  # PG 14

dnf install -y pg_plan_filter_18       # PG 18
dnf install -y pg_plan_filter_17       # PG 17
dnf install -y pg_plan_filter_16       # PG 16
dnf install -y pg_plan_filter_15       # PG 15
dnf install -y pg_plan_filter_14       # PG 14

apt install -y postgresql-18-pg-plan-filter   # PG 18
apt install -y postgresql-17-pg-plan-filter   # PG 17
apt install -y postgresql-16-pg-plan-filter   # PG 16
apt install -y postgresql-15-pg-plan-filter   # PG 15
apt install -y postgresql-14-pg-plan-filter   # PG 14

Preload:

shared_preload_libraries = 'plan_filter';

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.