Analyzing Queries with EXPLAIN
This section describes how to optimize queries using YSQL's EXPLAIN
and EXPLAIN ANALYZE
statements.
The EXPLAIN Statement
Using the EXPLAIN
statement, you can obtain the query execution plan generated by YSQL for a given SQL statement. In addition to the plan, EXPLAIN
returns the following information:
- The so-called startup cost that represents the estimated query cost before the first row is returned.
- The total cost to run the query to completion.
You can use the EXPLAIN
statement in conjunction with SELECT
, DELETE
, INSERT
, REPLACE
, and UPDATE
statements. The EXPLAIN
statement has the following syntax:
EXPLAIN [ ( option [, ...] ) ] sql_statement;
The option and its values are described in the following table. The most important option is ANALYZE
.
Option | Value | Description |
---|---|---|
ANALYZE | boolean | Returns additional run-time statistics, such as time spent within each plan node, number of processed rows, and so on, by executing sql_statement (as opposed to just creating the plan, as EXPLAIN without ANALYZE does). The output of sql_statement is discarded.To perform analysis of any data-modifying statement (such as INSERT, UPDATE, and DELETE) without affecting the data, you must wrap EXPLAIN ANALYZE in a transaction using the following syntax: BEGIN; EXPLAIN ANALYZE sql_statement; ROLLBACK; |
VERBOSE | boolean | Displays detailed information about the query plan. The default value is FALSE. |
COSTS | boolean | Provides the estimated initial and total costs of each plan node. In addition, estimates the number of rows and the width of each row in the query plan. The default value is TRUE. |
BUFFERS | boolean | Provides information about the most input-output intensive parts of the query. The default value is FALSE. You can only use this option when ANALYZE is set to TRUE. |
TIMING | boolean | Provides information about the actual startup time and the time spent in each node of the output. The default value is TRUE. You can only use this option when ANALYZE is set to TRUE. |
SUMMARY | boolean | Provides additional information, such as the total time after the query plan. The value of this option is TRUE when ANALYZE is set to TRUE. |
FORMAT | { TEXT | XML | JSON | YAML } | Allows you to define the query plan output format. The default value is TEXT. |
Examples
Typically, you start by creating a table in YugabyteDB and inserting rows into it.
To create a table called employees
, execute the following:
yugabyte=# CREATE TABLE employees(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
To insert table rows, execute the following:
yugabyte=# INSERT INTO employees (k1, k2, v1, v2)
VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');
To check the query plan for simple select, execute the following:
yugabyte=# EXPLAIN SELECT * FROM employees WHERE k1 = 1;
The following output displays the query execution cost estimate:
QUERY PLAN
----------------------------------------------------------------
Foreign Scan on employees (cost=0.00..112.50 rows=1000 width=44)
(1 row)
To check the execution plan for select with a complex condition that requires filtering, execute the following:
yugabyte=# EXPLAIN SELECT * FROM employees
WHERE k1 = 2 and floor(k2 + 1.5) = v1;
The following output displays the cost estimate based on the filtered result:
QUERY PLAN
----------------------------------------------------------------
Foreign Scan on employees (cost=0.00..125.00 rows=1000 width=44)
Filter: (floor(((k2)::numeric + 1.5)) = (v1)::numeric)
(2 rows)
By enabling the ANALYZE
option and wrapping it to preserve data integrity, you can trigger the query execution, as follows:
BEGIN;
yugabyte=# EXPLAIN ANALYZE SELECT * FROM employees
WHERE k1 = 2 and floor(k2 + 1.5) = v1;
ROLLBACK;
In addition to the cost estimates from the query planner, EXPLAIN ANALYZE
displays the server output produced during the statement execution, as shown in the following example:
yugabyte=# EXPLAIN ANALYZE SELECT * FROM employees a LEFT JOIN LATERAL
(SELECT * FROM employees b WHERE a = b) c ON TRUE;
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..15202.50 rows=5000 width=88) (actual time=2.853..2.885 rows=3 loops=1)
Join Filter: (a.* = b.*)
Rows Removed by Join Filter: 6
-> Seq Scan on employees_k a (cost=0.00..100.00 rows=1000 width=112) (actual time=1.747..1.749 rows=3 loops=1)
-> Materialize (cost=0.00..105.00 rows=1000 width=112) (actual time=0.155..0.157 rows=3 loops=3)
-> Seq Scan on employees_k b (cost=0.00..100.00 rows=1000 width=112) (actual time=0.450..0.454 rows=3 loops=1)
Planning Time: 0.072 ms
Execution Time: 2.938 ms
(8 rows)
The server output from the preceding example includes the number of rescans (loops) each node performed, the number of milliseconds passed before the first row was returned, total time before the last tuple was returned by each execution node, and the number of tuples returned by each execution node.
EXPLAIN
, on the other hand, does not provide this additional information, as shown in the following examples:
yugabyte=# EXPLAIN SELECT * FROM employees_k a LEFT JOIN LATERAL
(SELECT * FROM employees_k b WHERE a = b) c ON TRUE;
QUERY PLAN
----------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..15202.50 rows=5000 width=88)
Join Filter: (a.* = b.*)
-> Seq Scan on employees_k a (cost=0.00..100.00 rows=1000 width=112)
-> Materialize (cost=0.00..105.00 rows=1000 width=112)
-> Seq Scan on employees_k b (cost=0.00..100.00 rows=1000 width=112)
(5 rows)