Explain

Query plan parsing utilities.

Picopyn provides a structured interface to Picodata EXPLAIN. Instead of executing EXPLAIN through fetch() and parsing the returned QUERY PLAN rows manually, the API builds the statement and returns an ExplainPlan or ExplainRawPlan object.

Usage

The explain() method is available on asynchronous Connection and Pool. Pass the SQL query without the EXPLAIN prefix, followed by any bind parameters. Picopyn builds the resulting EXPLAIN query, executes it, and returns the parsed plan as an ExplainPlan object.

plan = await pool.explain(
    'SELECT * FROM "warehouse" WHERE id = $1',
    1,
)

print(plan.root.type)
print(plan.execution_options)
print(plan.buckets)

See the corresponding API reference for Connection.explain() and Pool.explain().

Raw EXPLAIN

Pass raw=True to request EXPLAIN (RAW). Unlike regular EXPLAIN, which returns a query plan tree, RAW output contains execution sections with the SQL statements generated for them and their physical query plans. Picopyn returns this output as an ExplainRawPlan object.

Result structure

Regular EXPLAIN results are represented by ExplainPlan. Its root field contains a tree of PlanNode objects. Motion nodes may additionally expose their data movement policy through MotionPolicy.

RAW EXPLAIN results are represented by ExplainRawPlan. It contains a list of RawSection objects, each describing an execution section with its SQL statement and physical plan rows represented by RawRow.

Example output

The example from the Usage section may produce output similar to:

projection
{'sql_vdbe_opcode_max': 45000, 'sql_motion_row_max': 5000}
[322]

The plan nodes, execution options, and bucket identifiers depend on the query and the cluster configuration.

API Reference

MotionPolicy

Bases: str, Enum

Data movement policy of a motion node in an EXPLAIN plan.

This class lists data motion types.

Attributes:

Name	Type	Description
LOCAL_SEGMENT		Keep data local and calculate bucket IDs.
LOCAL		Keep data local without calculating bucket IDs.
SEGMENT		Redistribute data between storage nodes.
FULL		Send the full data set to target storage nodes.

Source code in picopyn/explain.py

class MotionPolicy(str, Enum):
    """Data movement policy of a motion node in an EXPLAIN plan.

    This class lists [data motion types](https://docs.picodata.io/picodata/26.1/reference/sql/explain/#data_motion_types).

    Attributes:
        LOCAL_SEGMENT: Keep data local and calculate bucket IDs.
        LOCAL: Keep data local without calculating bucket IDs.
        SEGMENT: Redistribute data between storage nodes.
        FULL: Send the full data set to target storage nodes.
    """

    LOCAL_SEGMENT = "local segment"
    LOCAL = "local"
    SEGMENT = "segment"
    FULL = "full"

PlanNode dataclass

Node of a parsed EXPLAIN query plan tree.

Attributes:

Name	Type	Description
type	str	Query plan operation represented by the node.
text	str	Full textual representation of the node.
children	list[PlanNode]	Child nodes in the query plan tree.
motion_policy	MotionPolicy | None	Data movement policy for a motion node, if present.

Source code in picopyn/explain.py

@dataclass(slots=True)
class PlanNode:
    """Node of a parsed EXPLAIN query plan tree.

    Attributes:
        type: Query plan operation represented by the node.
        text: Full textual representation of the node.
        children: Child nodes in the query plan tree.
        motion_policy: Data movement policy for a motion node, if present.
    """

    type: str
    text: str
    children: list[PlanNode]
    motion_policy: MotionPolicy | None = None

ExplainPlan dataclass

Structured result of a regular Picodata EXPLAIN.

Attributes:

Name	Type	Description
root	PlanNode	Root node of the query plan tree.
execution_options	dict[str, int]	Execution options reported by EXPLAIN.
buckets	str	Textual representation of the buckets on which the query is expected to execute, e.g. "[191]", "any"/"unknown" when the query is not restricted to specific buckets.

Source code in picopyn/explain.py

@dataclass(slots=True)
class ExplainPlan:
    """Structured result of a regular Picodata EXPLAIN.

    Attributes:
        root: Root node of the query plan tree.
        execution_options: Execution options reported by EXPLAIN.
        buckets: Textual representation of the
            [buckets](https://docs.picodata.io/picodata/26.1/architecture/distributed_sql/#data_distribution)
            on which the query is expected to execute, e.g. `"[191]"`,
            `"any"`/`"unknown"` when the query is not restricted to specific buckets.
    """

    root: PlanNode
    execution_options: dict[str, int]
    buckets: str

RawRow dataclass

Row of a physical query plan from RAW EXPLAIN output.

Attributes:

Name	Type	Description
selectid	int	Identifier of the subquery or compound query part.
order	int	Execution step within the corresponding query part.
from_index	int	Position of the source in the FROM clause.
detail	str	Details of how the query plan step is executed.

Source code in picopyn/explain.py

@dataclass(slots=True)
class RawRow:
    """Row of a physical query plan from RAW EXPLAIN output.

    Attributes:
        selectid: Identifier of the subquery or compound query part.
        order: Execution step within the corresponding query part.
        from_index: Position of the source in the FROM clause.
        detail: Details of how the query plan step is executed.
    """

    selectid: int
    order: int
    from_index: int
    detail: str

RawSection dataclass

Execution section of a RAW EXPLAIN plan.

Attributes:

Name	Type	Description
role	str	Execution role of the section.
sql	str	SQL statement executed for the section.
rows	list[RawRow]	Physical query plan for the SQL statement.

Source code in picopyn/explain.py

@dataclass(slots=True)
class RawSection:
    """Execution section of a RAW EXPLAIN plan.

    Attributes:
        role: Execution role of the section.
        sql: SQL statement executed for the section.
        rows: Physical query plan for the SQL statement.
    """

    role: str
    sql: str
    rows: list[RawRow]

ExplainRawPlan dataclass

Structured result of Picodata EXPLAIN (RAW).

Attributes:

Name	Type	Description
sections	list[RawSection]	Execution sections in the order reported by EXPLAIN.

Source code in picopyn/explain.py

@dataclass(slots=True)
class ExplainRawPlan:
    """Structured result of Picodata EXPLAIN (RAW).

    Attributes:
        sections: Execution sections in the order reported by EXPLAIN.
    """

    sections: list[RawSection]

build_explain_query(query, raw)

Add the appropriate EXPLAIN prefix to an SQL query.

Parameters:

Name	Type	Description	Default
query	str	SQL query to explain.	required
raw	bool	Whether to request RAW EXPLAIN output.	required

Returns:

Type	Description
str	EXPLAIN query.

Source code in picopyn/explain.py

def build_explain_query(query: str, raw: bool) -> str:
    """Add the appropriate EXPLAIN prefix to an SQL query.

    Args:
        query: SQL query to explain.
        raw: Whether to request RAW EXPLAIN output.

    Returns:
        EXPLAIN query.
    """
    options: list[str] = []
    if raw:
        options.append("RAW")

    if options:
        return f"EXPLAIN ({', '.join(options)}) {query}"
    return f"EXPLAIN {query}"

rows_to_lines(rows)

Extract query plan lines from EXPLAIN result rows.

Parameters:

Name	Type	Description	Default
rows	list[Any]	Result rows containing the QUERY PLAN column.	required

Returns:

Type	Description
list[str]	Query plan lines.

Raises:

Type	Description
ValueError	If a row is not mapping-like or has no QUERY PLAN value.

Source code in picopyn/explain.py

def rows_to_lines(rows: list[Any]) -> list[str]:
    """Extract query plan lines from EXPLAIN result rows.

    Args:
        rows: Result rows containing the ``QUERY PLAN`` column.

    Returns:
        Query plan lines.

    Raises:
        ValueError: If a row is not mapping-like or has no ``QUERY PLAN`` value.
    """
    lines: list[str] = []
    for row in rows:
        if not hasattr(row, "get") and not isinstance(row, dict):
            raise ValueError("Expected mapping-like rows with QUERY PLAN column")

        value = row.get("QUERY PLAN")
        if value is None:
            raise ValueError("Missing QUERY PLAN value in EXPLAIN output")
        lines.append(str(value))
    return lines

parse_plain_explain_lines(lines)

Parse regular EXPLAIN output into an ExplainPlan.

Parameters:

Name	Type	Description	Default
lines	list[str]	Lines of regular EXPLAIN output.	required

Returns:

Type	Description
ExplainPlan	Structured EXPLAIN plan.

Raises:

Type	Description
ValueError	If the EXPLAIN output is empty or malformed.

Source code in picopyn/explain.py

def parse_plain_explain_lines(lines: list[str]) -> ExplainPlan:
    """Parse regular EXPLAIN output into an ExplainPlan.

    Args:
        lines: Lines of regular EXPLAIN output.

    Returns:
        Structured EXPLAIN plan.

    Raises:
        ValueError: If the EXPLAIN output is empty or malformed.
    """
    if not lines:
        raise ValueError("Empty EXPLAIN output")

    tree_lines, option_lines, buckets = _split_plain_blocks(lines)
    if not tree_lines:
        raise ValueError("No plan tree nodes found in EXPLAIN output")
    if buckets is None:
        raise ValueError("Missing buckets line in EXPLAIN output")

    root = _build_plan_tree(tree_lines)
    execution_options = _parse_execution_options(option_lines)
    return ExplainPlan(root=root, execution_options=execution_options, buckets=buckets)

parse_raw_explain_lines(lines)

Parse RAW EXPLAIN output into an ExplainRawPlan.

Parameters:

Name	Type	Description	Default
lines	list[str]	Lines of RAW EXPLAIN output.	required

Returns:

Type	Description
ExplainRawPlan	Structured RAW EXPLAIN plan.

Raises:

Type	Description
ValueError	If the RAW EXPLAIN output is empty or malformed.

Source code in picopyn/explain.py

def parse_raw_explain_lines(lines: list[str]) -> ExplainRawPlan:
    """Parse RAW EXPLAIN output into an ExplainRawPlan.

    Args:
        lines: Lines of RAW EXPLAIN output.

    Returns:
        Structured RAW EXPLAIN plan.

    Raises:
        ValueError: If the RAW EXPLAIN output is empty or malformed.
    """
    if not lines:
        raise ValueError("Empty RAW EXPLAIN output")

    sections = _parse_raw_sections(lines)
    return ExplainRawPlan(sections=sections)