Technical Analysis: Add NESTED_STATEMENTS Option to EXPLAIN

Core Problem

PostgreSQL's EXPLAIN command has a fundamental observability gap: it cannot show execution plans for SQL statements that execute inside PL/pgSQL functions and procedures. When a user calls EXPLAIN (ANALYZE) SELECT my_function(), they see only the top-level Result node — the internal queries (SELECTs, UPDATEs, INSERTs, DELETEs) executed by the function body are completely invisible.

The only existing mechanism to observe these nested plans is auto_explain, which requires:

1. Adding the extension to shared_preload_libraries (requires restart)
2. Setting multiple session-level GUCs (auto_explain.log_nested_statements, auto_explain.log_min_duration)
3. Parsing server logs to find the output

This workflow is hostile to developers and DBAs doing routine query optimization. The gap is architecturally significant because PL/pgSQL functions are a primary abstraction layer in PostgreSQL applications, and performance diagnosis requires visibility into what those functions actually execute.

Proposed Solution: Architecture

The patch introduces NESTED_STATEMENTS as a new boolean EXPLAIN option that, when combined with ANALYZE, captures and displays execution plans for all SQL statements executed within called functions during the EXPLAIN run.

Hook-Based Interception

The implementation leverages PostgreSQL's existing executor hook infrastructure — the same mechanism used by auto_explain. During EXPLAIN (ANALYZE, NESTED_STATEMENTS) execution, the patch temporarily installs four hooks:

• ExecutorStart_hook — tracks nesting depth entry
• ExecutorRun_hook — monitors execution
• ExecutorFinish_hook — captures completed plan text
• ExecutorEnd_hook — tracks nesting depth exit

This is architecturally clean in that it reuses the established extensibility points rather than modifying executor internals. However, it introduces temporary global state mutation during a single EXPLAIN command's lifetime.

Output Format

Nested plans are appended after the main plan output in a "Nested Plans:" section. Each nested statement includes:

• Sequential statement number (ordered by completion time)
• Nesting level (executor call stack depth)
• Query text
• Full execution plan (inheriting parent EXPLAIN options like VERBOSE, BUFFERS)

Nesting Level Semantics

The level semantics follow executor call stack depth:

• PERFORM / SELECT INTO → creates new executor level (depth increases)
• Expression assignment (result := func()) → same level (no new executor)
• Trigger-fired statements → deeper than triggering statement
• SQL-language functions → true nesting (execute during parent query)

This is consistent with auto_explain's internal tracking model, which is important for user mental model consistency.

Critical Design Issues Identified

1. Error Path Hook Leakage (Crash Bug)

The v1 patch failed to remove hooks when an error occurred during EXPLAIN execution (e.g., division by zero inside a function). Since executor hooks are global process state, leaked hooks would corrupt subsequent query execution, causing crashes on the next query.

v2 Fix: PG_TRY/PG_FINALLY wrapping to guarantee hook removal regardless of execution outcome. This is the standard PostgreSQL pattern for resource cleanup.

2. Reentrancy / Nested EXPLAIN (Crash Bug)

If a function itself executes EXPLAIN (ANALYZE, NESTED_STATEMENTS) internally, the static global variables tracking the outer EXPLAIN would be overwritten, causing undefined behavior and crashes.

v2 Fix: A reentrancy guard that skips hook installation if nested tracking is already active. This is a pragmatic solution, though it means inner EXPLAIN NESTED_STATEMENTS calls silently degrade — a tradeoff worth noting.

3. Memory Management (Leak)

The v1 patch allocated captured plan text in TopMemoryContext to ensure it survived until display time. However, on error paths this memory was never freed, constituting a per-session memory leak.

v2 Fix: A dedicated memory context ("Nested EXPLAIN plans") created at EXPLAIN start and unconditionally deleted in PG_FINALLY. This is the correct PostgreSQL idiom — using context-based bulk deallocation rather than tracking individual allocations.

Architectural Tradeoffs and Open Questions

Global Hook Mutation

The patch temporarily overwrites process-global executor hooks. While it saves/restores them, this creates a window where concurrent operations in the same backend could be affected. In practice, PostgreSQL backends are single-threaded, so this is safe — but it's a fragile pattern if backend threading ever advances.

Structured Output Formats

The author acknowledges that JSON/XML/YAML output includes nested plans as text strings rather than structured plan nodes. This is a significant limitation for tooling that parses EXPLAIN output programmatically. A proper implementation would extend the plan tree structure, but this adds considerable complexity.

Relationship to auto_explain

This feature overlaps significantly with auto_explain. The key differentiator is UX: interactive, immediate, no configuration. However, having two code paths for the same functionality raises maintenance burden questions. A cleaner architecture might refactor auto_explain's internals into shared infrastructure that both the extension and core EXPLAIN can use.

Security Implications

The patch exposes query text and plans for all nested statements. If a function is owned by a superuser and contains privileged queries, calling it via EXPLAIN NESTED_STATEMENTS could leak plan details to unprivileged callers. This needs security review.

Performance Impact

When not active, there should be zero overhead (hooks not installed). When active, the overhead is plan capture (string serialization) for each nested statement, allocated in a dedicated context. For functions executing many statements, memory consumption could be significant.

Patch Quality Assessment

The v1 patch demonstrated the concept but had fundamental robustness issues (crashes, memory leaks) that indicate insufficient error-path testing. The v2 patch addresses these with standard PostgreSQL patterns (PG_TRY/PG_FINALLY, dedicated memory contexts, reentrancy guards).

The inclusion of a comprehensive 19-test script is positive for review, though these tests should ideally be integrated into the standard regression test suite rather than shipped as a standalone SQL file.

The feature fills a genuine observability gap and the implementation approach (temporary hook installation) is architecturally sound, following the same pattern that auto_explain has validated over many years in production.