Explain Statement

Synopsis

The EXPLAIN command shows 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

option

Grammar

explain ::= EXPLAIN [ ANALYZE] [ VERBOSE ] | ( option [, ...] ) ] statement
option ::= ANALYZE [ boolean ] | 
           VERBOSE [ boolean ] | 
           COSTS [ boolean ] | 
           BUFFERS [ boolean ] | 
           TIMING [ boolean ] | 
           SUMMARY [ boolean ] | 
           FORMAT { TEXT | XML | JSON | YAML }

boolean = TRUE | FALSE;

Where statement is the target statement (see more here).

Examples

• Create a sample table.

  postgres=# CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

• Insert some rows.

  postgres=# 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).

  postgres=# EXPLAIN SELECT * FROM sample WHERE k1 = 1;

                         QUERY PLAN
  ----------------------------------------------------------------
  Foreign Scan on sample  (cost=0.00..112.50 rows=1000 width=44)
  (1 row)
  

• Check the execution plan for select with complex condition (second condition requires filtering).

  postgres=# EXPLAIN SELECT * FROM sample WHERE k1 = 2 and floor(k2 + 1.5) = v1;

                         QUERY PLAN
  ----------------------------------------------------------------
  Foreign Scan on sample  (cost=0.00..125.00 rows=1000 width=44)
  Filter: (floor(((k2)::numeric + 1.5)) = (v1)::numeric)
  (2 rows)
  

• Check execution with ANALYZE option.

  postgres=# EXPLAIN ANALYZE SELECT * FROM sample WHERE k1 = 2 and floor(k2 + 1.5) = v1;

  ----------------------------------------------------------------------------------------------------------
  Foreign Scan on sample  (cost=0.00..125.00 rows=1000 width=44) (actual time=6.483..6.487 rows=1 loops=1)
  Filter: (floor(((k2)::numeric + 1.5)) = (v1)::numeric)
  Planning time: 2.390 ms
  Execution time: 5.146 ms
  (4 rows)
  

See Also

SELECT Other PostgreSQL Statements