[PATCH] Add NESTED_STATEMENTS option to EXPLAIN

Hi hackers,

I'd like to propose adding NESTED_STATEMENTS as a core EXPLAIN option
that displays execution plans for SQL statements executed within
called functions and procedures.

Motivation
----------

Currently, to see execution plans for nested statements inside
PL/pgSQL functions, users must:

1. Add auto_explain to shared_preload_libraries
2. Restart the database
3. Set session-level GUCs:

SET client_min_messages TO log;
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_min_duration = 0;
SELECT my_function();

4. Check server logs for the output

With this patch, users can simply run:

EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT my_function();

Note: Requires ANALYZE (validated with clear error message)

and see the plans for all nested statements directly in the EXPLAIN
output, with no extension loading or configuration required.

How It Works
------------

The feature temporarily installs executor hooks (ExecutorStart, Run,
Finish, End) during the EXPLAIN execution to track query nesting depth
and capture plans for nested statements. After the main query plan is
displayed, nested plans are appended with:

- Sequential statement number
- Nesting level (executor call stack depth)
- Query text
- Complete execution plan (inheriting VERBOSE, BUFFERS, etc. options)

Example:

-- Setup
CREATE TABLE products (id INT, name TEXT, price NUMERIC, category TEXT);
INSERT INTO products VALUES
(1, 'Laptop', 999, 'Electronics'),
(2, 'Phone', 699, 'Electronics'),
(3, 'Book', 19, 'Books');

CREATE FUNCTION update_products() RETURNS void AS $$
DECLARE cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM products WHERE category = 'Electronics';
UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';
INSERT INTO products (name, price, category) VALUES ('Keyboard',
79, 'Electronics');
DELETE FROM products WHERE price < 5;
END;
$$ LANGUAGE plpgsql;

-- Run
EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT update_products();

QUERY PLAN
---------------------------------------------------------------
Result (actual time=1.234..1.235 rows=1.00 loops=1)
Buffers: shared hit=50
Planning Time: 0.050 ms
Execution Time: 2.500 ms

Nested Plans:

Nested Statement #1 (level 1):
Query Text: SELECT COUNT(*) FROM products WHERE category = 'Electronics'
Aggregate (actual time=0.015..0.016 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.010..0.011 rows=2.00 loops=1)
Filter: (category = 'Electronics'::text)
Rows Removed by Filter: 1
Buffers: shared hit=1

Nested Statement #2 (level 1):
Query Text: UPDATE products SET price = price * 1.10 WHERE
category = 'Electronics'
Update on products (actual time=0.050..0.050 rows=0.00 loops=1)
Buffers: shared hit=5
-> Seq Scan on products (actual time=0.020..0.022 rows=2.00 loops=1)
Filter: (category = 'Electronics'::text)
Rows Removed by Filter: 1
Buffers: shared hit=1

Nested Statement #3 (level 1):
Query Text: INSERT INTO products (name, price, category) VALUES
('Keyboard', 79, 'Electronics')
Insert on products (actual rows=0.00 loops=1)
Buffers: shared hit=1
-> Result (actual rows=1.00 loops=1)

Nested Statement #4 (level 1):
Query Text: DELETE FROM products WHERE price < 5
Delete on products (actual rows=0.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual rows=0.00 loops=1)
Filter: (price < '5'::numeric)
Rows Removed by Filter: 4
Buffers: shared hit=1

Nesting Level Semantics
-----------------------

The nesting level reflects the executor call stack depth. Here's a
visual representation of the level1_func example above:

EXPLAIN SELECT level1_func(); ← Level 0 (top-level, not shown)
│
└─→ level1_func() executes
│
├─→ PERFORM COUNT(*) FROM products → Level 1, Statement #1
│
├─→ PERFORM level2_func() → creates new executor
│ │
│ └─→ level2_func() executes
│ │
│ ├─→ PERFORM COUNT(*) WHERE id = 1 → Level 2, Statement #2
│ │
│ └─→ PERFORM level3_func() → creates new executor
│ │
│ └─→ level3_func() executes
│ │
│ └─→ PERFORM COUNT(*) WHERE category = 'Books'
│ → Level 3, Statement #3
│
│ (level3_func returns) → Level 2, Statement #4
│ (level2_func returns) → Level 1, Statement #5
│
└─→ (done)

The level number = how many PERFORM/SELECT calls are on the stack.

The nesting level reflects the executor call stack depth:

- Statements in a function called via PERFORM or SELECT INTO run at a
deeper level than the caller.
- Statements in a function called via expression assignment (result :=
func()) run at the same level as the caller, because no new executor
call is created.
- Trigger-fired statements appear at a deeper level than the
triggering statement.
- SQL functions create true nesting since they execute during the parent query.

This is consistent with how auto_explain tracks nesting internally.

Example showing multiple nesting levels:

CREATE FUNCTION level3_func() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products WHERE category = 'Books';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION level2_func() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products WHERE id = 1;
PERFORM level3_func();
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION level1_func() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products;
PERFORM level2_func();
END;
$$ LANGUAGE plpgsql;

EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT level1_func();

QUERY PLAN
---------------------------------------------------------------
Result (actual time=1.701..1.702 rows=1.00 loops=1)
Buffers: shared hit=35
Planning Time: 0.036 ms
Execution Time: 3.820 ms

Nested Plans:

Nested Statement #1 (level 1):
Query Text: SELECT COUNT(*) FROM products
Aggregate (actual time=0.011..0.012 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.008..0.009 rows=3.00 loops=1)
Buffers: shared hit=1

Nested Statement #2 (level 2):
Query Text: SELECT COUNT(*) FROM products WHERE id = 1
Aggregate (actual time=0.006..0.006 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.005..0.005 rows=1.00 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 2
Buffers: shared hit=1

Nested Statement #3 (level 3):
Query Text: SELECT COUNT(*) FROM products WHERE category = 'Books'
Aggregate (actual time=0.003..0.003 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.002..0.002 rows=1.00 loops=1)
Filter: (category = 'Books'::text)
Rows Removed by Filter: 2
Buffers: shared hit=1

Nested Statement #4 (level 2):
Query Text: SELECT level3_func()
Result (actual time=0.049..0.049 rows=1.00 loops=1)
Buffers: shared hit=1

Nested Statement #5 (level 1):
Query Text: SELECT level2_func()
Result (actual time=0.217..0.217 rows=1.00 loops=1)
Buffers: shared hit=28

Each PERFORM creates a new executor level, so the nesting depth
increases with each function call in the chain.

Testing
-------

I've also included a comprehensive test script
(comprehensive_nested_statements_test.sql) that covers 14 test cases:

1. Validation (NESTED_STATEMENTS requires ANALYZE)
2. Simple PL/pgSQL function (all statements at level 1)
3. PERFORM pattern (creates deeper nesting levels)
4. Expression assignment (stays at same level)
5. Side-by-side comparison of PERFORM vs expression assignment
6. SQL function nesting (true SQL nesting, levels 2-3)
7. Three-level PL/pgSQL chain with PERFORM
8. Recursive function (increasing levels)
9. Exception handling blocks
10. No nested statements (plain query, no Nested Plans section)
11. Trigger-fired nested statements
12. Combined with VERBOSE and BUFFERS options
13. Statement numbering = completion order (triggers demo)
14. BEGIN/ROLLBACK safety pattern

To run the test script and save output:

psql -f comprehensive_nested_statements_test.sql > test_output_all.txt 2>&1

The test output file (test_output_all.txt) shows the full EXPLAIN
output for each test case with explanations of expected behavior.

Structured output formats (JSON, XML, YAML) have been tested and work
correctly. In structured formats, the nested plans appear as a text
string within the Plan field rather than as structured plan nodes.
This could be improved in a future version.

--
Mohamed Ali
AWS RDS