pg_overexplain

Allow EXPLAIN to dump even more details

pg_overexplain : Allow EXPLAIN to dump even more details

Overview

ID	Extension	Package	Version	Category	License	Language
6880	pg_overexplain	pg_overexplain	`1.0`	STAT	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--sL---	No	Yes	Yes	No	no	no

Relationships
See Also	pg_stat_plans pg_show_plans auto_explain explain_ui pg_store_plans pg_stat_statements pg_profile pg_stat_monitor pg_qualstats hypopg

Packages

PG18	PG17	PG16	PG15	PG14
1.0	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_overexplain';

This extension does not need CREATE EXTENSION DDL command

Usage

pg_overexplain: extended EXPLAIN with internal planner details

pg_overexplain extends the EXPLAIN command with additional debugging options to display internal planner data structures. Primarily intended for planner debugging and development.

Loading

LOAD 'pg_overexplain';
-- Or in postgresql.conf:
-- session_preload_libraries = 'pg_overexplain'

EXPLAIN (DEBUG)

Displays internal plan tree information:

EXPLAIN (DEBUG) SELECT * FROM my_table WHERE id = 1;

Shows per-node fields:

Disabled Nodes – raw counter of disabled nodes
Parallel Safe – whether the node can appear under Gather
Plan Node ID – internal ID for parallel query coordination
extParam / allParam – parameters affecting the node

Shows per-query fields:

Command Type – query type (select, update, etc.)
Flags – hasReturning, hasModifyingCTE, canSetTag, transientPlan, etc.
Subplans Needing Rewind – subplan IDs requiring rewind
Relation OIDs – OIDs the plan depends on
Parse Location – location in the query string

EXPLAIN (RANGE_TABLE)

Displays information about the query’s range table entries:

EXPLAIN (RANGE_TABLE) SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;

Shows range table index references (Scan RTI, Nominal RTI, Append RTIs, etc.) and dumps each range table entry with its kind (relation, subquery, join, cte, etc.) and entry-specific fields.

Notes

Output reflects internal planner data structures and may require reading source code to interpret
Output format may change across PostgreSQL versions
Not recommended for general production use