WL#17309: Add Selected Partition Information to Iterator-Based EXPLAIN

Affects: Server-26.x — Status: Complete

Description
Requirements
High Level Architecture
Low Level Design

TRADITIONAL EXPLAIN and JSON format version 1 show the partitions selected for a partitioned table access. Iterator-based EXPLAIN formats currently do not, so the information is missing from EXPLAIN FORMAT=TREE, EXPLAIN ANALYZE, and JSON format version 2.

This work adds the selected partition names to iterator nodes that access a partitioned base table. TREE and supported ANALYZE output add the names to the operation text. JSON v2 adds a structured partitions array to the same access object.

The list is the plan's selected read-partition set after an explicit PARTITION(...) restriction and optimizer partition pruning. It is plan information, not runtime instrumentation of partitions physically visited.

This is an EXPLAIN presentation change. It does not change pruning, plan selection, costs, row estimates, or execution. TRADITIONAL EXPLAIN and JSON v1 remain unchanged.

Example


  CREATE TABLE t (
    id INT PRIMARY KEY
  )
  PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN MAXVALUE
  );

  EXPLAIN FORMAT=TREE
  SELECT * FROM t WHERE id BETWEEN 110 AND 120;

Before this work:


  -> Index range scan on t using PRIMARY over (110 <= id <= 120)

After this work:


  -> Index range scan on t using PRIMARY in partitions (p1) over (110 <= id <= 120)

JSON v2 adds:


  "partitions": ["p1"]

Functional requirements

( F1 ) EXPLAIN FORMAT=TREE, supported EXPLAIN ANALYZE plans, and JSON v2 MUST report selected partitions for an iterator node that directly accesses a partitioned base table when the selected set is available and non-empty.
( F2 ) The reported set MUST be the leaf partitions in TABLE::part_info->read_partitions. It reflects any explicit PARTITION(...) restriction followed by optimizer partition pruning. It is a plan-time candidate read set and does not state that every listed partition was visited or produced rows.
( F3 ) TREE and ANALYZE MUST use the operation-text wording and placement defined in the High-Level Specification. ANALYZE MUST retain the same partition list as TREE while preserving its existing runtime-statistics formatting.
( F4 ) JSON v2 MUST add an optional field named partitions, whose value is an array of partition-name strings, to the corresponding table-access object. The release containing this field MUST use the coordinated json_schema_version "2.1".
( F5 ) TRADITIONAL EXPLAIN and JSON v1 MUST remain unchanged. For an equivalent table access, all formats MUST use the same selected leaf names, metadata order, and subpartition naming. Names MUST be emitted deterministically in server partition-metadata order.
( F6 ) RANGE-, LIST-, HASH-, and KEY-partitioned tables MUST be supported. Tables partitioned by RANGE or LIST and subpartitioned by HASH or KEY MUST also be supported. A selected subpartition MUST be named <parent_partition_name>_<subpartition_name>, matching TRADITIONAL EXPLAIN and JSON v1.
( F7 ) TREE and ANALYZE MUST omit the partition suffix, and JSON v2 MUST omit the partitions field, for an unpartitioned table, an empty selected set, or unavailable partition information. An empty list MUST NOT be displayed as partitions () or as an empty JSON array.
( F8 ) Partition information MUST be placed on the direct table-access node. It MUST NOT be copied to filters, joins, materialization or temporary-table wrappers, zero-row nodes, or DML root nodes.
( F9 ) For UPDATE and DELETE, a qualifying access child reports selected read partitions. For INSERT ... SELECT and REPLACE ... SELECT, qualifying source accesses report selected read partitions. Destination write or locked partitions are outside this feature.
( F10 ) A dynamic index range scan MUST report the statement-level candidate set from read_partitions. Runtime range replanning MUST NOT turn the output into a per-loop set or a union of runtime selections.
( F11 ) When the classic optimizer represents one partitioned const table as Rows fetched before execution, iterator EXPLAIN MUST retain the originating table and report its selected partitions. Table-independent fake-row and table value-constructor nodes MUST remain table-free.
( F12 ) When one Rows fetched before execution node represents multiple classic const tables, it MUST omit table and partition information. A representation for associating separate partition sets with multiple const tables is outside this work.
( F13 ) The direct-access rule includes table scan, index lookup/scan/range scan, dynamic index range scan, table-backed constant-row access, sample scan, and single-table unqualified COUNT(*).
( F14 ) A multi-table unqualified COUNT(*) node MUST omit partition information because the node has no per-table partition representation. Its existing operation text and first-table JSON metadata MUST remain unchanged. It MUST NOT combine partition names belonging to different tables into one partitions array.
( F15 ) Adding partitions to SAMPLE_SCAN MUST preserve its existing JSON fields and values. In particular, an aliased sample scan MUST retain its existing table_name value and MUST NOT gain alias, schema_name, or used_columns fields as a side effect of this work.
( F16 ) Selected partition reporting MUST apply to supported partitioned base tables using both InnoDB and NDB. Both engines MUST use the TREE, supported ANALYZE, and JSON v2 representations defined by this worklog. Partition names MUST come from the server-owned TABLE::part_info->read_partitions state. No new storage-engine-specific partition-reporting API is introduced.

Non-functional requirements

( N1 ) The feature MUST NOT change partition pruning, read_partitions contents, access-path selection, costs, row estimates, or query execution.
( N2 ) The feature MUST NOT change SQL syntax, parser behavior, table metadata, the data dictionary, storage-engine APIs, or client/server protocol structure.
( N3 ) Partition enumeration and name formatting MUST occur only during EXPLAIN generation and MUST add no per-row execution overhead. Constant-time explain-only table-provenance assignment during plan construction is allowed.
( N4 ) The JSON v2 change MUST be additive. Existing structured fields MUST NOT be removed, renamed, moved, or have their types or meanings changed. The documented operation-text suffix and const-table provenance are intentional additions. Apart from those additions, existing operation text and JSON fields MUST retain their existing content and formatting.
( N5 ) Reported partition-list and JSON construction errors MUST use the existing iterator-EXPLAIN error path. EXPLAIN MUST NOT return a partially constructed JSON result after a reported error.
( N6 ) No new storage-engine or secondary-engine partition-reporting API MUST be introduced. The information comes from server-owned TABLE partition metadata.
( N7 ) Temporary state used to format the feature MUST be proportional to the number and total length of emitted partition names and MUST NOT scale with the number of rows processed.

INTERFACES AFFECTED

Affected

EXPLAIN FORMAT=TREE operation text.
Supported EXPLAIN ANALYZE operation text.
JSON v2 operation text and the new optional partitions array.
JSON v2 json_schema_version, coordinated as "2.1" for the release.
Clients and tools that display or parse iterator-based EXPLAIN output.

Not affected

SQL syntax and parser behavior.
Client/server protocol structure.
Configuration-variable definitions.
TRADITIONAL EXPLAIN and JSON v1 output.
Storage-engine APIs, table metadata, and the data dictionary.
Query execution, replication, instrumentation, and security behavior.

1. Selected-set semantics

The output is derived from TABLE::part_info->read_partitions. The bitmap is the selected read set after an explicit PARTITION(...) restriction and optimizer pruning. When all partitions remain selected, all leaf partition names are reported.

The interface applies to supported partitioned InnoDB and NDB base-table accesses. Both use the server-owned selected read-partition metadata; no engine-specific partition-reporting API is introduced.

The list does not report eliminated partitions, pruning reasons, partitions written or locked by DML, or runtime-observed physical accesses.

2. TREE and ANALYZE representation

Index accesses use:


  in partitions ()

The suffix follows the table and index identification and precedes lookup, range, reverse-scan, and pushed-index-condition text.

Examples:

Index scan on t using idx in partitions (p0,p1)
Index lookup on t using idx in partitions (p1) (id = 110)
Index range scan on t using idx in partitions (p1) over (...)
Index range scan on t (re-planned for each iteration) in partitions (p0,p1)

Table-like accesses use:


  over partitions ()

Examples:

Table scan on t over partitions (p0,p1)
Constant row from t over partitions (p1)
Sample scan on t over partitions (p1)
Count rows in t over partitions (p0,p1)

Names are emitted in server partition-metadata order, separated by commas without spaces. ANALYZE uses the same operation text and appends its existing runtime statistics.

3. JSON v2 representation and placement

JSON v2 adds partitions to the object representing the direct table access:


  {
    "operation": "Index range scan on t using PRIMARY in partitions (p1) over (110 <= id <= 120)",
    "table_name": "t",
    "access_type": "index",
    "index_name": "PRIMARY",
    "partitions": ["p1"]
  }

The operation member is human-readable text. The partitions array is the structured interface for programmatic consumers.

The field is not copied to parent or wrapper objects. If a plan accesses more than one partitioned table, each direct table-access object reports its own selected set.

4. Names and omission

For a non-subpartitioned table, the output contains top-level partition names. For a subpartitioned table, it contains leaf names in the existing form:


  p1_p1s0,p1_p1s1

The suffix and JSON field are omitted when the table is unpartitioned, the selected set is empty, or the set cannot be determined. Therefore, absence of the optional JSON field does not distinguish those cases.

TRADITIONAL EXPLAIN continues to use its partitions column; for an empty set, that column is NULL. JSON v1 continues to use its existing partitions property and omits it for an empty set.

5. Special plan representations

Classic single const table

When a single partitioned const table is collapsed into a pre-execution node:


  Rows fetched before execution from t over partitions (p1)

JSON v2 retains access_type rows_fetched_before_execution and adds the originating table information and partitions array. Multiple collapsed const tables and table-independent single-row sources do not receive this metadata.

Dynamic index range

The displayed set is the statement-level candidate read set. Dynamic range replanning does not update it for individual iterator loops.


Unqualified COUNT(*)

A single-table count reports that table's selected set. A multi-table count does not expose partitions because the current count node has no per-table representation.

DML

Read-access children report selected read partitions; DML roots do not report destination or locked partitions. INSERT ... VALUES and REPLACE ... VALUES have no source access child and therefore add no partition list.

Secondary-engine sample scan

SAMPLE_SCAN uses the same server-owned selected set and preserves all existing sample and table-identity fields. This work does not add EXPLAIN ANALYZE support where secondary-engine TABLESAMPLE is already unsupported.

The implementation affects:

sql/join_optimizer/explain_access_path.cc for partition collection and TREE/ANALYZE/JSON-v2 formatting;
sql/join_optimizer/access_path.h for explain-only provenance on the classic FAKE_SINGLE_ROW representation; and
sql/sql_executor.cc for assigning that provenance when exactly one partitioned const table is collapsed before iterator execution.

Partition enumeration reuses make_used_partitions_str() from sql/sql_partition.cc so iterator EXPLAIN follows the same bitmap, ordering, character conversion, and subpartition naming as TRADITIONAL and JSON v1. The formatter converts the returned names to one local list per access node and uses that same list for both operation text and the JSON array.

make_used_partitions_str() allocates its names in the current THD MEM_ROOT. The formatter copies them into local strings, and each JSON string owns its value; no emitted JSON member retains a pointer to a temporary name buffer. Enumeration takes O(number of leaf partitions) time and memory proportional to the total length of the emitted names. The explain-only provenance assignment during planning is O(1).

SetIndexInfoInObject() adds partition information for fixed-index accesses. SetObjectMembers() handles direct non-index cases, dynamic index range, SAMPLE_SCAN, unqualified count, and FAKE_SINGLE_ROW.

AddTableInfoToObject() adds partitions only when explicitly requested; composite callers continue to use its existing behavior without partition emission.

FOLLOW_TAIL represents a recursive-CTE internal temporary-table scan, not a partitioned base-table access. Its TABLE has no part_info, so generic partition collection returns an empty list and does not change its output.

SAMPLE_SCAN adds the partitions array without switching to generic table-info construction, preserving its existing table_name and field set.

NewFakeSingleRowAccessPath() accepts an optional TABLE pointer. JOIN::create_root_access_path_for_join() supplies it only when exactly one partitioned const table is represented. SetObjectMembers() uses the pointer only for EXPLAIN; iterator behavior is unchanged.

UNQUALIFIED_COUNT must emit partition information only when the count represents one base table. The formatter must omit it for a multi-table count unless a future design adds an unambiguous per-table representation.

Reported helper and JSON-DOM failures propagate through the existing boolean error path. A formatting error returns no partial JSON plan. Partition state is read-only throughout formatting.

NDB table accesses use the same server-owned TABLE::part_info->read_partitions metadata and shared partition-enumeration logic as InnoDB table accesses. The implementation introduces no NDB-specific partition-reporting API.

OUT OF SCOPE

Changes to pruning, optimization, costs, or execution.
Runtime instrumentation of partitions physically visited.
DML destination, write, or lock partition reporting.
A per-table representation for multiple collapsed classic const tables.
A per-table representation for multi-table unqualified COUNT(*).
New SQL syntax, metadata, dictionary, protocol, or engine APIs.
New EXPLAIN ANALYZE support for secondary-engine TABLESAMPLE.