EXPLAIN

Applies to VAST Cluster 5.5.

Shows the plan the Query Engine makes for a statement, without running it. Use it to see which conditions VAST DataBase evaluates, and which step makes a statement fail.

Syntax

EXPLAIN <statement>
EXPLAIN (FORMAT json) <statement>

Parameters and rules

• EXPLAIN returns one row with two columns. explain_key holds physical_plan, and explain_value the plan, drawn as boxes with the table scan at the bottom (profile a query).
• EXPLAIN (FORMAT json) returns the same plan as JSON: one object per step, with name, children and extra_info.
• The statement isn't run. EXPLAIN also works for a statement that would fail validation, such as a join, so you can see which step is the problem.
• EXPLAIN ANALYZE isn't supported: it fails with The operator 'EXPLAIN_ANALYZE' type=63 is currently not supported. To time a query, measure it in your client (profile a query).

Reading the plan

So, to read a Query Engine plan:

• A condition that's missing from the plan was handed to VAST DataBase.
• A condition in a FILTER step is evaluated by the Query Engine, after the scan. To push it down, compare the column itself with a constant; see where filters run.
• ~N Rows is the table's row count, on every step, whatever the filter. It isn't an estimate of how many rows match.

What happens on the cluster

The Query Engine plans the statement and returns the plan. It reads no table data.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, creates the schema docs_sql_explain with a small table, and defines explain(), which prints the plan:

import json

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_explain"'

def connect(autocommit=True):
    return dbapi.connect(
        driver=adbc_driver_vastdb.get_driver_path(),
        db_kwargs={
            "vast.db.endpoint": os.environ["QE_ENDPOINT"],
            "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
            "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
        },
        autocommit=autocommit,
    )

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

conn = connect()
TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, city VARCHAR, temperature DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES (1, 'Lisbon', 21.5), (2, 'Oslo', 4.0), (3, 'Cairo', 30.2)")

def explain(sql):
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        key, plan = cur.fetchone()
    print(key)
    print(plan.replace(BUCKET, "<bucket>"))

[]
[]
[(3,)]

A filter that's pushed down: no condition appears in the plan.

explain(f"SELECT id, temperature FROM {TABLE} WHERE city = 'Oslo' AND temperature < 10")

physical_plan
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│        temperature        │
│                           │
│          ~0 Rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│             id            │
│                           │
│          ~0 Rows          │
└───────────────────────────┘

A function on the column: the condition is in a FILTER step.

explain(f"SELECT id FROM {TABLE} WHERE upper(city) = 'OSLO'")

physical_plan
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
│          ~0 Rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (upper(city) = 'OSLO')  │
│                           │
│          ~0 Rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            city           │
│             id            │
│                           │
│          ~0 Rows          │
└───────────────────────────┘

A top-N query, and an aggregate:

explain(f"SELECT id, temperature FROM {TABLE} ORDER BY temperature DESC LIMIT 2")
explain(f"SELECT count(*) FILTER (WHERE temperature > 20) FROM {TABLE}")

physical_plan
┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 2          │
│                           │
│         Order By:         │
│       <bucket>."<bucket>      │
│     /docs_sql_explain"    │
│ .readings.temperature DESC│
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│        temperature        │
│                           │
│          ~0 Rows          │
└───────────────────────────┘

physical_plan
┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│ count_star() FILTER (WHERE│
│    (temperature > 20.0))  │
│  Filter: (temperature > 20│
│            .0)            │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│                           │
│          ~0 Rows          │
└───────────────────────────┘

A join plans a HASH_JOIN step, which is why running it fails. FORMAT json gives the same plan as data; this prints its step names:

JOIN = f"SELECT a.id FROM {TABLE} a JOIN {TABLE} b ON a.id = b.id"
with conn.cursor() as cur:
    cur.execute("EXPLAIN (FORMAT json) " + JOIN)
    _, plan = cur.fetchone()

def steps(nodes):
    for node in nodes:
        yield node["name"].strip()
        yield from steps(node.get("children", []))

tree = json.loads(plan)
print(list(steps(tree if isinstance(tree, list) else [tree])))
run(JOIN)
run(f"EXPLAIN ANALYZE SELECT count(*) FROM {TABLE}")

['PROJECTION', 'HASH_JOIN', 'VAST_SCAN', 'VAST_SCAN']
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.
InternalError: The operator 'EXPLAIN_ANALYZE' type=63 is currently not supported.

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

Errors

Trino and Spark

Trino's EXPLAIN lists the pushed-down conditions in the table scan, and Trino also has EXPLAIN ANALYZE; in Spark, use df.explain(). See Trino, Spark and profile a query.

See also: SELECT, pushdowns, profile a query.