Re: [PATCH] Add NESTED_STATEMENTS option to EXPLAIN

Hi,

Attached is v3 of the patch with the following improvements over v2:

New features:
- Execution Time per nested statement: each nested statement now shows
its total execution time (using query_instr->total, same source as
auto_explain). Controlled by the SUMMARY option — shown by default
with ANALYZE, hidden with SUMMARY OFF.

- Structured output formats: when using FORMAT JSON, XML, or YAML,
nested plans are now emitted as proper structured objects (with
Node Type, Plans array, typed fields, etc.) instead of flat text
strings. Each nested plan is a valid, independently parseable
document in the chosen format.

I also added new tests to the comprehensive test script to cover the
new features (now 24 tests).

One thing I'm considering for a future version: adding an option to
limit the number of captured statements or maximum nesting depth, e.g.:

EXPLAIN (ANALYZE, NESTED_STATEMENTS 10) SELECT complex_func();
-- or --
EXPLAIN (ANALYZE, NESTED_STATEMENTS, MAX_DEPTH 2) SELECT complex_func();

This would help with complex functions that execute hundreds of nested
statements. Would this be useful, or is the current behavior (capture
all) sufficient?

Attachments:
1- v3-0001-Add-NESTED_STATEMENTS-option-to-EXPLAIN.patch
2- comprehensive_nested_statements_test_v3.sql
3- test_output_all_v3.txt

Mohamed Ali
AWS RDS