Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

28.7 KB
Raw Blame

Plan test data

Real query plans, one file per plan shape, in the exact form Bytebase hands the visualizer. The parser tests read these instead of hand-written plans so each parser is pinned to what the engine actually emits — including the fields nobody thinks to invent.

Keep them verbatim. Editing one to make a test pass defeats the point of capturing it.

postgres/

EXPLAIN (FORMAT JSON) output captured from PostgreSQL 17 against a seeded schema: customers (5,000 rows) and orders (50,000 rows).

File Shape it covers
seq-scan-filter.json A single node with a filter — the smallest real plan
bitmap-index-scan.json An index path: bitmap heap scan over a bitmap index scan
hash-join-aggregate-sort.json Hash join under a hashed aggregate under a sort
cte-nested-loop-initplan.json A CTE, a nested loop, an InitPlan subquery and a limit

To add one:

psql -At -c "EXPLAIN (FORMAT JSON) <query>" > postgres/<shape>.json

mssql/

SHOWPLAN_XML output captured from SQL Server 2022 against the same schema, with orders_customer_id_idx on orders(customer_id). Each file is one statement run with SET SHOWPLAN_XML ON, which is how the driver runs them.

The exception is split-statements-batch.xml, a SQL Server 2016 plan kept as published: Cursors/cursor2.sqlplan from html-query-plan's test plans (MIT License). SQL Server 2016 gives each statement of a batch its own <Statements> block, which SQL Server 2022 no longer does.

File Shape it covers
index-seek-key-lookup.xml Nested loops over an index seek and a key lookup
hash-join-aggregate-sort.xml Merge join, stream and hash aggregates, and a sort
missing-index.xml A missing-index suggestion and a full scan of orders
implicit-conversion-warning.xml Statement-level type conversion warnings
no-join-predicate.xml An operator warning on a join without a condition
if-else.xml An IF with its condition, THEN and ELSE
procedure-two-statements.xml A procedure call holding two statements
two-statement-batch.xml Two statements in one plan, under a batch
static-cursor.xml A cursor's population and fetch queries
scalar-udf.xml A call to a scalar function SQL Server doesn't inline, with the function's own statements
unmatched-filtered-index.xml A procedure whose parameter keeps SQL Server from using a filtered index
split-statements-batch.xml A batch of cursor statements, each in its own <Statements> block

To add one, run the statement in sqlcmd and keep the <ShowPlanXML> document from its output:

printf 'SET SHOWPLAN_XML ON;\nGO\n<statement>\nGO\n' | sqlcmd -S localhost -U sa -C -d plandb -y 0

cockroachdb/

Not an EXPLAIN format: CockroachDB has none that returns a document. These are the payloads behind the diagram link EXPLAIN (DISTSQL) prints beside its text plan, decoded by the driver, captured from CockroachDB 24.3. Processors with the streams between them, and no estimate of any kind — the row counts are in the text plan, not in the link.

File Shape it covers
scan-filter-group.json A read, a filter and an aggregator in one chain
index-join.json A bounded index read feeding a lookup join, each naming its own table
sort-limit.json A top-K sorter over a read whose spans cover everything

To add one, run EXPLAIN (DISTSQL) <query> through the driver with QueryContext{Explain: true} and ExplainFormat_JSON, which is what decodes the link.

mongodb/

explain() output from MongoDB 5, captured through the driver so each file is exactly what Bytebase hands the viewer. The database name in a namespace is the one the test container generated, which is why it reads test_1 rather than anything meaningful — these are verbatim.

MongoDB's planner publishes no estimate at all, so the plans carry shape and index usage; only executionStats adds numbers, and only by running the query.

File Shape it covers
collection-scan.json A COLLSCAN with a filter — a query with no index to use
index-scan-sort.json SORT over FETCH over IXSCAN, with the index bounds
aggregate-group.json An aggregation's stages pipeline, whose $cursor holds the read's own plan
measured-collection-scan.json explain("executionStats"): the same tree with rows returned and documents examined
measured-aggregate-group.json A measured pipeline, where each stage states its rows and its spill counters beside itself rather than in its body
slot-based-index-scan.json The slot-based engine's shape: the tree under winningPlan.queryPlan, beside the plan it compiled for itself
sharded-index-scan.json A sharded collection: each shard's own plan under the SHARD_MERGE that reads them, each naming its shard and namespace
measured-sharded-index-scan.json The same query run, where a shard's entry holds executionStages and states no namespace
measured-slot-based-join.json A measured slot-based plan, whose nlj holds its two sides under outerStage and innerStage
sharded-aggregate-group.json A pipeline split across shards: no stages of its own, each shard's part under shards and the merger's under splitPipeline

The slot-based one is from MongoDB 7, which runs the classic engine by default; db.adminCommand({ setParameter: 1, internalQueryFrameworkControl: "trySbeEngine" }) is what makes it choose the other one. The sharded pair is from a cluster of two single-node shards behind a mongos, explained through the router on a collection sharded by a hashed key.

To add one, run the statement through the driver with QueryContext{Explain: true}, or in mongosh:

db.orders.find({ total: { $gt: 880 } }).explain()

mysql/

EXPLAIN FORMAT=JSON output against the same schema, with orders_customer_id_idx on orders(customer_id) and both tables analyzed. MySQL 8.0 and MariaDB 11.8 put the same shape under different keys, so one parser reads both and the mariadb- files are the second dialect's.

File Server Shape it covers
full-scan-filter.json MySQL A query block over one scan — the smallest real plan
group-sort-join.json MySQL An ordering over a grouping over a two-table join
derived-table.json MySQL A derived table materialized from its own query block
attached-subquery.json MySQL A real table's scan carrying a subquery's operators under attached_subqueries, which a derived table's materialized_from_subquery is not
ordering-filesort.json MySQL A filesort, whose sort_cost is the sort's alone where the block's query_cost is the whole
union-distinct.json MySQL A union result over two query specifications
mariadb-sort-temporary-join.json MariaDB filesort and temporary_table where MySQL says ordering_operation and grouping_operation
mariadb-block-nl-join.json MariaDB block-nl-join, a join wrapper holding the table it buffers

To add one:

docker exec <container> mysql -uroot -p<password> <database> -N -B -r \
  -e "EXPLAIN FORMAT=JSON <query>" > mysql/<shape>.json

starrocks/

EXPLAIN output from StarRocks 3.4 against the same schema. StarRocks rejects every JSON spelling, so its text plan is what there is. A plan is a list of fragments joined where one sends to another's exchange, and within a fragment the operators are drawn as a tree, a second input introduced by |---- and drawn in a branch behind a bar.

EXPLAIN COSTS would state a cardinality on every operator rather than on the scans alone, but it is a different plan from the one a plain EXPLAIN returns, and the editor asks for the plain one.

File Shape it covers
single-scan.txt Two fragments: a scan sending to the exchange that reads it
scan-aggregate.txt A partial and a final aggregate either side of an exchange
join-exchange.txt A join whose two inputs each arrive over their own exchange
nested-join-branch.txt A join drawn inside another's `

To add one:

docker exec <container> mysql -h127.0.0.1 -P9030 -uroot <database> -N -B -r \
  -e "EXPLAIN <query>" > starrocks/<shape>.txt

doris/

EXPLAIN output from Apache Doris 3.1 against the same schema, read by the StarRocks parser: StarRocks forked from Doris and their plans still agree on the fragments and the tree. Doris rejects every JSON spelling too, so its text plan is what there is.

What differs is how an operator spells itself. Doris prefixes the vectorized operators with V, prints its own plan node id after the name, packs an operator's numbers onto one line, qualifies a scanned table with its database, and prints the statistics it planned from under a rule of equals signs.

File Shape it covers
single-scan.txt Two fragments, and the avgRowSize=0.0 Doris prints with no column statistics
analyzed-join.txt The same tables once Doris had analyzed them, so it states rows and width — and groups a standalone cardinality by thousands
scan-aggregate.txt A partial and a final aggregate either side of an exchange
join-exchange.txt A broadcast join, and the statistics that follow the operator printed last

To add one, the same client as above against a Doris frontend.

tidb/

EXPLAIN FORMAT=tidb_json output from TiDB 8.5 against the same schema. TiDB reports no cost in this format or any other the editor can ask for, so these plans carry rows alone. It has printed these same keys since it added the format in v6.5.0, so one capture stands for every release that answers at all.

File Shape it covers
table-reader-selection.json A reader over a coprocessor selection over a full scan
index-hash-join.json A join's build and probe sides, and an index lookup's own two reads
union-hash-agg.json A union under a hash aggregate
measured-hash-join.json EXPLAIN ANALYZE: actual rows, and the executeInfo string the parser does not read timings from

To add one, EXPLAIN FORMAT=tidb_json through the same client as above.

oracle/

Oracle states a plan as rows of PLAN_TABLE, so these are what the driver renders those rows as — a JSON array, one object per operator, lower-cased column names, columns the optimizer said nothing about left out. Captured from Oracle Free 23ai against the same schema, with orders_customer_id_idx on orders(customer_id) and the schema analyzed. Numbers arrive as strings because that is how the Oracle driver returns NUMBER.

File Shape it covers
full-scan-filter.json A statement over one full scan — the smallest real plan
hash-join-group-sort.json An adaptive join: a hash join beside the nested loops and statistics collector Oracle keeps as the alternative
index-range-scan.json A table read by rowid from an index range scan
union-distinct.json UNION-ALL under the HASH UNIQUE that makes it distinct

To add one, EXPLAIN PLAN SET STATEMENT_ID = '<id>' FOR <query> and then select the columns planStepsQuery lists (see backend/plugin/db/oracle/oracle.go) from PLAN_TABLE where statement_id = '<id>', ordered by id.

clickhouse/

EXPLAIN json = 1, indexes = 1 output from ClickHouse 24.8 against an orders table of 50,000 rows. ClickHouse publishes no cost or row estimate in any EXPLAIN it offers, so these plans carry shape, descriptions and index accounting alone.

File Shape it covers
read-filter-aggregate.json The pipeline from a read up through an aggregate, a sort and a limit
primary-key-range.json A read whose primary-key index entry reports the parts and granules it kept
join-two-tables.json A join over a table read and a table function, which names no table

To add one:

curl -s -u default:<password> --data-binary \
  "EXPLAIN json = 1, indexes = 1 <query> FORMAT TSVRaw" http://localhost:8123/ \
  > clickhouse/<shape>.json

FORMAT TSVRaw matters: the HTTP interface otherwise escapes the plan's newlines into a TSV cell, which is not what the driver hands the viewer.

trino/

EXPLAIN (FORMAT JSON) output from Trino 482 against the tpch connector's sf1 schema, which has statistics behind it. Trino returns the fragments it would distribute, keyed by number, so these files are objects rather than trees; the parser splices them at each RemoteSource.

File Shape it covers
scan-filter-project.json A single fragment whose one operator fuses a scan, a filter and a projection, each with its own estimate
join-aggregate-fragments.json Three fragments: a partial and a final aggregate either side of a join
sort-limit.json Two fragments of partial top-N under a final one

To add one:

docker exec <container> trino --server localhost:8080 --catalog tpch \
  --schema sf1 --output-format TSV --execute "EXPLAIN (FORMAT JSON) <query>"

The client returns it as a quoted, escaped TSV cell; unescape it before saving.

oceanbase/

EXPLAIN FORMAT=JSON output from OceanBase CE 4.3 against the same schema, with orders_customer_id_idx on orders(customer_id). Each operator names its children under CHILD_1, CHILD_2, and EST.TIME(us) covers an operator and everything below it.

File Shape it covers
full-scan-filter.json One operator — the smallest real plan
hash-join-group-sort.json A sort over a group-by over a two-table hash join
index-lookup.json A range scan, which is not a read of a whole table
union-distinct.json A merge union with its two branches

To add one, EXPLAIN FORMAT=JSON <query> through obclient -N -B -r.

redshift/

Redshift's text plan, which is the only plan it publishes. Unlike every other directory here these were not captured by this repository: Redshift runs nowhere but a cluster, so the four XN plans are AWS's own documented output over the TICKIT sample schema, which the affected-rows estimator in backend/plugin/db/redshift/stats.go already pins against.

postgres-same-grammar.txt is the exception, and the point of it. Redshift inherited PostgreSQL's text format and prefixed its operators with XN, so a plan captured from a real PostgreSQL 17 server exercises the same indentation, the same cost tuple and the same attribute lines — which is what checks the grammar against output a server actually produced.

File Server Shape it covers
hash-join-broadcast.txt Redshift (AWS docs) A broadcast join over two scans, the whole plan indented one space
three-way-join.txt Redshift (AWS docs) A merge join under a hash join, with both distribution strategies
hash-aggregate.txt Redshift (AWS docs) An aggregate over a scan
scan-filter.txt Redshift (AWS docs) One scan and its filter
postgres-same-grammar.txt PostgreSQL 17 The same grammar without the XN prefix, captured here

To add a PostgreSQL one:

psql -At -c "EXPLAIN <query>" > redshift/<shape>.txt

spanner/

The Spanner emulator does not plan queries, so these are production Spanner plans from spanner-cli's test data (Apache License 2.0), passed through the driver's convertQueryPlanToJSON so each file holds the JSON Bytebase returns for that plan. Biome re-indents it, as it does every JSON file here.

File Source Shape it covers
hash-join.json hash_join.input.json Build and probe inputs, full table and index scans
filter-limit.json filter.input.json Global and local limits, a seek condition
scalar-subquery.json scalar_subquery_with_filter_scan.input.json A scalar subquery with its own aggregates
nested-array-subqueries.json array_subqueries_with_compute_struct.input.json An array subquery inside another

snowflake/

EXPLAIN USING JSON output as Snowflake publishes it in its EXPLAIN documentation — the one fixture here not captured through the driver, because Snowflake runs nowhere but Snowflake. What the driver hands over is the same single JSON value, the one cell the statement returns. Snowflake estimates neither cost nor rows; what it states is how much of each table the query would read.

File Shape it covers
inner-join.json A join and its filter over two scans, each reporting the partitions and bytes assigned to it, with the query's totals in GlobalStats

To add one, run EXPLAIN USING JSON <statement> on a Snowflake warehouse and keep the cell verbatim. The cases a published plan does not show — a scan assigned every partition of its table, a statement compiled into several steps — are written inline in the test, which is where a plan no server emitted belongs.

hive/

EXPLAIN FORMATTED output from Hive 4.0.0, HiveServer2 with its embedded metastore, against customers and orders. The repository's formatter indents the outer object; the plan the parser reads stays one escaped line within it, the CBOPlan string, which is how Hive answers. It states that string only where its cost-based optimizer planned the statement.

File Shape it covers
single-scan.json A scan under a filter under a project — the chain Calcite writes by leaving inputs out
join-aggregate.json A join naming both of its inputs, under an aggregate, on tables nothing had analyzed
analyzed-join.json The same query once the tables held rows and ANALYZE TABLE … COMPUTE STATISTICS FOR COLUMNS had run, so every operator states rows and the scans state a row width
union-all.json A union reading two branches
order-limit.json A sort with a fetch
no-cbo-plan.json hive.cbo.enable=false: the Tez stages alone, with no CBOPlan, which the viewer shows as text

To add one, EXPLAIN FORMATTED through the same client.

bigquery/

The stages of a BigQuery job as the driver encodes them: BigQuery's own REST names for ExplainQueryStage, with its durations as milliseconds. BigQuery is reachable from neither a dev box nor CI, so this is the driver's own output for the stage list in backend/plugin/db/bigquery/query_plan_test.go, which holds the shape a job reports — regenerate it from there rather than editing it. Every plan here is measured, being the record of a run; BigQuery states none for a statement it has not run.

File Shape it covers
filtered-aggregate.json Two stages: the input reads a table, aggregates and writes a shuffle, and the output reads that shuffle and writes the query's rows

databricks/

EXPLAIN FORMATTED output from Spark 3.5.3, which is what Databricks SQL runs, against customers, orders and a partitioned events. Databricks itself is reachable from neither a dev box nor CI; the statement is identical and the layout is Spark's own, though a Databricks runtime may name operators these fixtures do not show. The plan states neither cost nor rows: EXPLAIN COST reports those, and only for the logical plan.

File Shape it covers
single-scan.txt A filter over a read, with the predicate pushed into it
broadcast-join.txt A broadcast hash join under two aggregates and an exchange, with the build side's own exchange
union-all.txt A union of two reads, the one tree with two branches
sort-limit.txt A sort with its fetch, as TakeOrderedAndProject
partition-filter.txt A read of a partitioned table, stating the partitions it skipped
exists-subquery.txt An EXISTS turned into a left semi join

To add one, EXPLAIN FORMATTED <statement> through the same client. Spark suffixes each column with the expression id of the session that planned it, so a re-capture renumbers them; the tests match the shape of those values rather than the numbers.

elasticsearch/

What a profiled search answers with, from Elasticsearch 8.15.0: the response's own took beside its profile, which is what the driver hands over once it has taken the profile out of the rows. Captured against an index of 60 documents and a three-shard index of 30, both created for the capture.

File Shape it covers
bool-aggregation.json One shard: a boolean query over a term and a range, the collectors that gathered it, a terms aggregation with an average nested inside, and the fetch's three phases
multi-shard.json The same search across three shards, each profiled on its own

To add one, send a search with "profile": true and keep the response's took and profile. Every node is timed in nanoseconds and covers its own subtree, so a re-capture renumbers every timing, and the tests that assert them against this capture would need updating with it.