跳转到主要内容

pg_plan_advice

Describe, reproduce, and constrain query plans with plan advice

pg_plan_advice : Describe, reproduce, and constrain query plans with plan advice

Overview

ID Extension Package Version Category License Language
2988 pg_plan_advice pg_plan_advice - FEAT PostgreSQL C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sL--- No Yes Yes No no no

Packages

PG19 PG18 PG17 PG16 PG15
- N/A N/A N/A N/A
This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_plan_advice';

This extension does not need CREATE EXTENSION DDL command

Usage

Sources:

pg_plan_advice is a supplied PostgreSQL 19 module that records planner choices as advice and constrains later planning. This page uses PostgreSQL 19.0 as its version boundary; the module has no independent SQL extension version.

Session Workflow

LOAD 'pg_plan_advice';
CREATE TEMP TABLE advice_demo (id integer PRIMARY KEY, body text);
EXPLAIN (COSTS OFF, PLAN_ADVICE)
  SELECT * FROM advice_demo d WHERE id = 1;
SET pg_plan_advice.advice = 'SEQ_SCAN(d)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
  SELECT * FROM advice_demo d WHERE id = 1;
RESET pg_plan_advice.advice;

Enablement and Feedback

Use LOAD with sufficient privileges for one session, session_preload_libraries for new sessions, or shared_preload_libraries followed by a server restart. There is no extension DDL step. EXPLAIN (PLAN_ADVICE) generates advice using relation aliases as targets. pg_plan_advice.advice supplies constraints on scans, join order, join methods, or parallelism.

Inspect the returned feedback for unmatched, conflicting, inapplicable, or failed advice. pg_plan_advice.feedback_warnings can emit warnings, and pg_plan_advice.always_store_advice_details preserves details useful when explaining prepared statements at additional planning cost.

Limits

Advice only constrains plans the core planner considers viable. It cannot force a semantically invalid plan, control aggregation strategy, or control set operations. Fixed advice can age poorly as data changes. pg_stash_advice optionally stores advice by query ID; pg_plan_guard monitors drift from approved baselines.

表格阅读