EXPLAIN
Synopsis
Use the EXPLAIN
statement to show the execution plan for an statement. If the ANALYZE
option is used, the statement will be executed, rather than just planned. In that case, execution information (rather than just the planner's estimates) is added to the EXPLAIN
result.
Syntax
explain ::= EXPLAIN [ [ ANALYZE ] [ VERBOSE ] | ( option [ , ... ] ) ]
sql_stmt
option ::= ANALYZE [ boolean ]
| VERBOSE [ boolean ]
| COSTS [ boolean ]
| BUFFERS [ boolean ]
| TIMING [ boolean ]
| SUMMARY [ boolean ]
| FORMAT { TEXT | XML | JSON | YAML }
explain
option
Semantics
Where statement is the target statement (see more here).
ANALYZE
Execute the statement and show actual run times and other statistics.
Examples
Create a sample table.
yugabyte=# CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
Insert some rows.
yugabyte=# INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');
Check the execution plan for simple select (condition will get pushed down).
yugabyte=# EXPLAIN SELECT * FROM sample WHERE k1 = 1;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using sample_pkey on sample (cost=0.00..15.25 rows=100 width=44)
Index Cond: (k1 = 1)
(2 rows)
- Check the execution plan for select with complex condition (second condition requires filtering).
yugabyte=# EXPLAIN SELECT * FROM sample WHERE k1 = 2 and floor(k2 + 1.5) = v1;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using sample_pkey on sample (cost=0.00..17.75 rows=100 width=44)
Index Cond: (k1 = 2)
Filter: (floor(((k2)::numeric + 1.5)) = (v1)::numeric)
(3 rows)
Check execution with ANALYZE
option.
yugabyte=# EXPLAIN ANALYZE SELECT * FROM sample WHERE k1 = 2 and floor(k2 + 1.5) = v1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using sample_pkey on sample (cost=0.00..17.75 rows=100 width=44) (actual time=3.123..3.126 rows=1 loops=1)
Index Cond: (k1 = 2)
Filter: (floor(((k2)::numeric + 1.5)) = (v1)::numeric)
Planning Time: 0.149 ms
Execution Time: 3.198 ms
Peak Memory Usage: 8 kB
(6 rows)