---
title: "EXPLAIN"
slug: "dev-database-reference-sql-explain"
updated: 2026-10-30T18:19:11Z
published: 2026-10-30T18:19:11Z
canonical: "docs.vastdata.com/dev-database-reference-sql-explain"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/develop/docs/dev-database-guides-profile-a-query#read-the-plan-with-explain)).
- `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 &apos;EXPLAIN_ANALYZE&apos; type=63 is currently not supported.` To time a query, measure it in your client ([profile a query](/develop/docs/dev-database-guides-profile-a-query#time-the-query)).

### Reading the plan

| Step | What it means |
| --- | --- |
| `VAST_SCAN` | The table scan, in VAST DataBase. It lists the columns it reads (`Projections`) and a row count (`~N Rows`). It doesn't list the conditions it evaluates. |
| `FILTER` | A condition the Query Engine evaluates itself, on the rows the scan returns. |
| `PROJECTION` | Expressions and the columns returned. |
| `TOP_N` | `ORDER BY ... LIMIT`, with its limit and sort keys. |
| `UNGROUPED_AGGREGATE` | Aggregates over the whole result. A `FILTER (WHERE ...)` clause of an aggregate is shown inside this step. |
| `UNION` | `UNION ALL` of two scans. |
| `HASH_JOIN`, `NESTED_LOOP_JOIN`, `CROSS_PRODUCT`, `HASH_GROUP_BY` | Steps the Query Engine can plan but not run in 5.5: the statement fails. A long `IN` list shows as a `HASH_JOIN` with a `COLUMN_DATA_SCAN` of the values. |
| `VAST_VEC_INDEX_SCAN` | A vector search that uses the index; see [query design](/develop/docs/dev-vector-search-guides-query-design#check-the-plan-with-explain). |

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](/develop/docs/dev-database-reference-sql-select#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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction). 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

| You wrote | Message |
| --- | --- |
| `EXPLAIN ANALYZE ...` | `InternalError: The operator &apos;EXPLAIN_ANALYZE&apos; type=63 is currently not supported.` ([profile a query](/develop/docs/dev-database-guides-profile-a-query#what-the-query-engine-doesnt-have)) |

## 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](/develop/docs/dev-database-integrations-trino#explain), [Spark](/develop/docs/dev-database-integrations-spark#what-vast-database-does-for-your-query) and [profile a query](/develop/docs/dev-database-guides-profile-a-query).

See also: [SELECT](/develop/docs/dev-database-reference-sql-select#where-filters-run), [pushdowns](/develop/docs/dev-database-reference-pushdowns), [profile a query](/develop/docs/dev-database-guides-profile-a-query).