EXPLAIN
NAME
EXPLAIN: show how a query is planned (extension)
SYNOPSIS
<explain statement> ::=
EXPLAIN <cursor specification>
DESCRIPTION
EXPLAIN shows how turborel would run a query, without running it. Its rows are the lines of the plan, in one column, PLAN, each a line of text. They show four stages in turn, each headed by a comment line. The plan as bound comes first, then the rewrite rules that changed it, then the plan they left, and last the physical plan that runs, with the indexes it reads and how it joins.
EXPLAIN is direct SQL, as a query is. The turborel client shows its
rows as it shows a query's. A program reaches the same text by the
library's Session::explain (The Embedded Library),
which also takes values for a query's parameters, as direct SQL has
none.
EXPLAIN is not a key word. A table may be named EXPLAIN, and EXPLAIN is read as this statement only before SELECT, VALUES, TABLE, WITH or a parenthesis.
RULES
- The query is one a cursor could be declared for, FOR UPDATE aside, as for a query run directly.
- It is bound as a query run then would be, so it refers only to tables and columns that exist.
PRIVILEGES
Those the query needs. EXPLAIN reads no row, but binding the query checks them.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -103 | The query refers to a table or column that does not exist, or is otherwise invalid |
| 42000 | -104 | Its user lacks a privilege the query needs |
EXAMPLES
A query the planner answers from an index:
CREATE TABLE PARTS (ID INTEGER PRIMARY KEY, NAME VARCHAR(20));
INSERT INTO PARTS
WITH RECURSIVE N (I) AS (VALUES (CAST(1 AS INTEGER)) UNION ALL SELECT I + 1 FROM N WHERE I < 1000)
SELECT I, 'part ' || CAST(I AS VARCHAR(4)) FROM N;
COMMIT;
CREATE INDEX PARTS_NAME ON PARTS (NAME);
EXPLAIN SELECT ID FROM PARTS WHERE NAME = 'part 7';
OK
OK
OK
OK
PLAN
--------------------------------------------------------------
-- bound:
Project PARTS.ID
Filter PARTS.NAME = 'part 7'
Scan ANN.PARTS
-- rules: none
-- optimized:
Project PARTS.ID
Filter PARTS.NAME = 'part 7'
Scan ANN.PARTS
-- physical:
Project PARTS.ID
IndexScan ANN.PARTS using PARTS_NAME [btree]: PARTS.NAME = 'part 7' (est. rows=1, cost=20.6)
(12 rows)
STANDARD
EXPLAIN is an extension of turborel. No part of ISO/IEC 9075 shows how
a statement is planned. In GET DIAGNOSTICS it is COMMAND_FUNCTION
EXPLAIN, code -14.