EXPLAIN | Clauses

The EXPLAIN clause is used to explain the plan used for a query. It is particularly useful when you want to understand how a query is executed and how it is optimised by the database.

When EXPLAIN is used, the statement returns the execution plan instead of the query result, showing how the query will be performed.

Syntax

Clause Syntax

@query EXPLAIN [FULL]

Using the EXPLAIN clause in addition to the FULL keyword is especially useful when you want to understand the performance of a query and can provide more details when debugging.

Examples

For example, consider the performance of the following query when the field email is not indexed. We can see that the execution plan will iterate over the whole table.

Index not used

CREATE person:tobie SET
	name = "Tobie",
	address = "1 Bagshot Row",
	email = "tobie@surrealdb.com";

SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;

Output

-------- Query --------

{
	attributes: {
		projections: '*'
	},
	children: [
		{
			attributes: {
				direction: 'Forward',
				predicate: "email = 'tobie@surrealdb.com'",
				table: 'person'
			},
			context: 'Db',
			operator: 'TableScan'
		}
	],
	context: 'Db',
	operator: 'SelectProject'
}

-------- Query --------

{
	attributes: {
		projections: '*'
	},
	children: [
		{
			attributes: {
				direction: 'Forward',
				predicate: "email = 'tobie@surrealdb.com'",
				table: 'person'
			},
			context: 'Db',
			metrics: {
				elapsed_ns: 66543,
				output_batches: 1,
				output_rows: 1
			},
			operator: 'TableScan'
		}
	],
	context: 'Db',
	metrics: {
		elapsed_ns: 8251,
		output_batches: 1,
		output_rows: 1
	},
	operator: 'SelectProject',
	total_rows: 1
}

On the other hand, here is the result when the field email is indexed. We can see that the execution plan will use the index to retrieve the record.

Index used

DEFINE INDEX fast_email ON TABLE person FIELDS email;

CREATE person:tobie SET
	name = "Tobie",
	address = "1 Bagshot Row",
	email = "tobie@surrealdb.com";

SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;

Output

-------- Query --------

{
	attributes: {
		projections: '*'
	},
	children: [
		{
			attributes: {
				access: "= 'tobie@surrealdb.com'",
				direction: 'Forward',
				index: 'fast_email'
			},
			context: 'Db',
			operator: 'IndexScan'
		}
	],
	context: 'Db',
	operator: 'SelectProject'
}

-------- Query --------

{
	attributes: {
		projections: '*'
	},
	children: [
		{
			attributes: {
				access: "= 'tobie@surrealdb.com'",
				direction: 'Forward',
				index: 'fast_email'
			},
			context: 'Db',
			metrics: {
				elapsed_ns: 48624,
				output_batches: 1,
				output_rows: 1
			},
			operator: 'IndexScan'
		}
	],
	context: 'Db',
	metrics: {
		elapsed_ns: 4999,
		output_batches: 1,
		output_rows: 1
	},
	operator: 'SelectProject',
	total_rows: 1
}

Bitmap index fusion

Available since: v3.3.0

When a WHERE clause combines conditions that different indexes can answer, the plan can use all of those indexes instead of driving the query from one of them. Each index is read into a bitmap of the records that match it, the bitmaps are combined with set operations, and only the records left at the end are fetched. How SurrealDB combines indexes explains when the planner chooses this plan.

The example below uses the EXPLAIN ANALYZE statement, because its text format shows the plan tree compactly. The EXPLAIN clause returns the same operators as nested objects.

DEFINE FIELD category ON article TYPE string;
DEFINE FIELD published ON article TYPE bool;
DEFINE INDEX idx_category ON article FIELDS category;
DEFINE INDEX idx_published ON article FIELDS published;

INSERT INTO article [
	{ id: 1, category: 'databases', published: true },
	{ id: 2, category: 'databases', published: false },
	{ id: 3, category: 'networking', published: true },
	{ id: 4, category: 'databases', published: true }
];

EXPLAIN ANALYZE SELECT id FROM article WHERE category = 'databases' AND published = true;

Sample output

"SelectProject [ctx: Db] [projections: id] {rows: 2, batches: 1, elapsed: 10.17µs}
    Filter [ctx: Db] [predicate: category = 'databases' AND published = true] {rows: 2, batches: 1, elapsed: 33.58µs}
        BitmapResolve [ctx: Db] [table: article] {rows: 2, batches: 1, elapsed: 322.25µs}
            BitmapAnd [ctx: Db] {rows: 2, batches: 0, elapsed: 0ns}
                BitmapIndexScan [ctx: Db] [index: idx_category, access: = 'databases'] {rows: 3, batches: 0, elapsed: 0ns}
                BitmapIndexScan [ctx: Db] [index: idx_published, access: = true] {rows: 3, batches: 0, elapsed: 0ns}

Total rows: 2"

Under EXPLAIN ANALYZE, the rows of a bitmap operator is the number of candidate records it produced. Each index matched three articles, the intersection left two, and those two are the only records that BitmapResolve fetched. The Filter above it checks the whole WHERE clause again on each fetched record, so a condition that was left out of the bitmaps is still applied.

Operator	What it produces
`BitmapResolve`	Evaluates the bitmap tree beneath it, then fetches the remaining records of `table` for the rest of the plan.
`BitmapIndexScan`	The records that match the `access` condition in a standard or unique `index`.
`BitmapFullTextScan`	The records that match the `query` in a full-text `index`, for a `@@` condition.
`BitmapGraphScan`	The records connected to the `source` record through the `edges` tables in one hop, for a condition such as `->wrote->post CONTAINS post:one`.
`BitmapAnd`	The records present in every child.
`BitmapOr`	The records present in any child.
`BitmapAndNot`	The records in the first child that are not in the second.

The same bitmap operators appear beneath IndexCountScan when a count() is answered from indexes alone, and beneath KnnScan when a vector search is pre-filtered. A branch that stopped reading its index because it passed the SURREAL_BITMAP_BRANCH_BUDGET limit reports dropped: true under EXPLAIN ANALYZE, and a top-level OR plan that fell back to reading its indexes separately reports union_tier: fallback on BitmapResolve.

K-nearest neighbours (KNN) searches

Available since: v3.1.5

When a KNN search over an indexed vector field is combined with an additional non-KNN condition, that condition is pushed into the index search so that non-matching candidates are rejected during the search rather than afterwards. The plan shows this as a predicate attribute on the KnnScan operator. To display the query plan, add the EXPLAIN clause to the end of a query as shown in the example below.

Filtered KNN

DEFINE INDEX idx_pt ON pts FIELDS point HNSW DIMENSION 4;
INSERT INTO pts [
	{ point: [1, 2, 3, 4], flag: true },
	{ point: [4, 3, 2, 1], flag: false },
	{ point: [3, 3, 3, 3], flag: true }
];

SELECT id, flag, vector::distance::knn() AS distance FROM pts
	WHERE flag = true AND point <|2,40|> [2, 3, 4, 5]
	ORDER BY distance EXPLAIN;

Output

{
	attributes: {
		projections: 'id, flag, distance'
	},
	children: [
		{
			attributes: {
				sort_keys: 'distance ASC'
			},
			children: [
				{
					attributes: {
						fields: 'distance = vector::distance::knn(...)'
					},
					children: [
						{
							attributes: {
								predicate: 'flag = true'
							},
							children: [
								{
									attributes: {
										dimension: '4',
										ef: '40',
										index: 'idx_pt',
										k: '2',
										predicate: 'flag = true'
									},
									context: 'Db',
									operator: 'KnnScan'
								}
							],
							context: 'Db',
							expressions: [
								{
									role: 'predicate',
									sql: 'flag = true'
								}
							],
							operator: 'Filter'
						}
					],
					context: 'Db',
					expressions: [
						{
							role: 'distance',
							sql: 'vector::distance::knn(...)'
						}
					],
					operator: 'Compute'
				}
			],
			context: 'Db',
			operator: 'SortByKey'
		}
	],
	context: 'Db',
	operator: 'SelectProject'
}

The innermost KnnScan operator carries the predicate: 'flag = true' attribute - the condition that is evaluated inside the index search. The same condition also appears on the Filter operator above it. Adding EXPLAIN FULL includes per-operator metrics. The query plan is identical for a DISKANN index. For a guided walkthrough, see Filtering through vector search.

Pre-filtered KNN searches

Available since: v3.3.0

When another index gives exactly the records that the extra condition matches, the plan pre-filters instead. That index is read before the search into an allow-list of matching records, which appears as a bitmap operator beneath KnnScan, and the vector search only admits records on the list. The predicate attribute then holds only the conditions that are still checked on each candidate. Under EXPLAIN ANALYZE, KnnScan also reports the prefilter_tier it used: exact, graph, graph_unboosted or fallback.

In the example above, flag has no index. Giving it a type and an index is enough for the condition to be pre-filtered:

DEFINE FIELD flag ON pts TYPE bool;
DEFINE INDEX idx_flag ON pts FIELDS flag;

EXPLAIN ANALYZE SELECT id, flag, vector::distance::knn() AS distance FROM pts
	WHERE flag = true AND point <|2,40|> [2, 3, 4, 5]
	ORDER BY distance;

Sample output

'SelectProject [ctx: Db] [projections: id, flag, distance] {rows: 2, batches: 1, elapsed: 3.96ms}
    SortByKey [ctx: Db] [sort_keys: distance ASC] {rows: 2, batches: 1, elapsed: 3.93ms}
        Compute [ctx: Db] [fields: distance = vector::distance::knn(...)] {rows: 2, batches: 1, elapsed: 3.50ms}
            Filter [ctx: Db] [predicate: flag = true] {rows: 2, batches: 1, elapsed: 3.46ms}
                KnnScan [ctx: Db] [index: idx_pt, k: 2, ef: 40, dimension: 4, prefilter_tier: exact] {rows: 2, batches: 1, elapsed: 3.43ms}
                    BitmapIndexScan [ctx: Db] [index: idx_flag, access: = true] {rows: 2, batches: 0, elapsed: 0ns}

Total rows: 2'

The BitmapIndexScan found the two records with flag = true, and KnnScan has no predicate because no condition is left to check during the search. An allow-list this small takes the exact tier, which computes the distance to each allowed record without using the vector index. Pre-filtering with an index describes the four tiers and which conditions qualify.