Use EXPLAIN to view the execution plan of a statement in PostgreSQL

This article describes the usage of the EXPLAIN statement in PostgreSQL.

In PostgreSQL, you can use the EXPLAIN statement display the execution plan generated by the PostgreSQL planner for a specified statement to determine whether the statement is an efficient statement.

PostgreSQL EXPLAIN syntax

The following is the syntax of the EXPLAIN statement:

EXPLAIN [ ( option [, ...] ) ] statement;

where option can be one of the following:

  • ANALYZE [ boolean ]: Execute the command and display the actual elapsed time and other statistics.

    EXPLAIN ANALYZE
SELECT * FROM address
WHERE postal_code = 'x';

                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161) (actual time=43.589..43.590 rows=0 loops=1)
Index Cond: ((postal_code)::text = 'x'::text)
Planning Time: 0.396 ms
Execution Time: 43.612 ms

  • VERBOSE [ boolean ]: Displays additional information about the plan.

    EXPLAIN VERBOSE
SELECT * FROM address
WHERE postal_code = 'x';

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on public.address  (cost=0.28..8.29 rows=1 width=161)
Output: address_id, address, address2, district, city_id, postal_code, phone, last_update
Index Cond: ((address.postal_code)::text = 'x'::text)

  • COSTS [ boolean ]: Includes information about the estimated startup and total cost of each plan node, as well as the estimated number of rows and the estimated width of each row.

    EXPLAIN (COSTS)
SELECT * FROM address
WHERE postal_code = 'x';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
Index Cond: ((postal_code)::text = 'x'::text)

  • SETTINGS [ boolean ]: Contains information about configuration parameters.

    EXPLAIN (SETTINGS)
SELECT * FROM address
WHERE postal_code = 'x';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
Index Cond: ((postal_code)::text = 'x'::text)

  • BUFFERS [ boolean ]: includes information about buffer usage.

    EXPLAIN (BUFFERS)
SELECT * FROM address
WHERE postal_code = 'x';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
Index Cond: ((postal_code)::text = 'x'::text)

  • WAL [ boolean ]: Contains information about WAL record generation. It must be used when ANALYZE is also enable.

    EXPLAIN (ANALYZE, WAL)
SELECT * FROM address
WHERE postal_code = 'x';

                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: ((postal_code)::text = 'x'::text)
Planning Time: 0.131 ms
Execution Time: 0.043 ms

  • TIMING [ boolean ]: Include the actual startup time and time spent in each node in the output. It must be used when ANALYZE is also enable.

    EXPLAIN (ANALYZE, TIMING)
SELECT * FROM address
WHERE postal_code = 'x';

                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((postal_code)::text = 'x'::text)
Planning Time: 0.082 ms
Execution Time: 0.030 ms

  • SUMMARY [ boolean ]: Include summary information after query plan

    EXPLAIN (SUMMARY)
SELECT * FROM address
WHERE postal_code = 'x';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
Index Cond: ((postal_code)::text = 'x'::text)
Planning Time: 0.096 ms

  • FORMAT { TEXT | XML | JSON | YAML }: Specifies the output format, which can be text, XML, JSON, or YAML.

    The format YAML output:

    EXPLAIN (FORMAT YAML)
SELECT * FROM address
WHERE postal_code = 'x';

                       QUERY PLAN
-----------------------------------------------------
- Plan:                                            +
    Node Type: "Index Scan"                        +
    Parallel Aware: false                          +
    Async Capable: false                           +
    Scan Direction: "Forward"                      +
    Index Name: "address_postal_code_idx"          +
    Relation Name: "address"                       +
    Alias: "address"                               +
    Startup Cost: 0.28                             +
    Total Cost: 8.29                               +
    Plan Rows: 1                                   +
    Plan Width: 161                                +
    Index Cond: "((postal_code)::text = 'x'::text)"

    The format JSON output:

    EXPLAIN (FORMAT JSON)
SELECT * FROM address
WHERE postal_code = 'x';

                          QUERY PLAN
---------------------------------------------------------
[                                                      +
  {                                                    +
    "Plan": {                                          +
      "Node Type": "Index Scan",                       +
      "Parallel Aware": false,                         +
      "Async Capable": false,                          +
      "Scan Direction": "Forward",                     +
      "Index Name": "address_postal_code_idx",         +
      "Relation Name": "address",                      +
      "Alias": "address",                              +
      "Startup Cost": 0.28,                            +
      "Total Cost": 8.29,                              +
      "Plan Rows": 1,                                  +
      "Plan Width": 161,                               +
      "Index Cond": "((postal_code)::text = 'x'::text)"+
    }                                                  +
  }                                                    +
]

    The format XML output:

    EXPLAIN (FORMAT XML)
SELECT * FROM address
WHERE postal_code = 'x';

                               QUERY PLAN
------------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain">        +
 <Query>                                                       +
   <Plan>                                                      +
     <Node-Type>Index Scan</Node-Type>                         +
     <Parallel-Aware>false</Parallel-Aware>                    +
     <Async-Capable>false</Async-Capable>                      +
     <Scan-Direction>Forward</Scan-Direction>                  +
     <Index-Name>address_postal_code_idx</Index-Name>          +
     <Relation-Name>address</Relation-Name>                    +
     <Alias>address</Alias>                                    +
     <Startup-Cost>0.28</Startup-Cost>                         +
     <Total-Cost>8.29</Total-Cost>                             +
     <Plan-Rows>1</Plan-Rows>                                  +
     <Plan-Width>161</Plan-Width>                              +
     <Index-Cond>((postal_code)::text = 'x'::text)</Index-Cond>+
   </Plan>                                                     +
 </Query>                                                      +
</explain>

Note that boolean in all options specifies whether the selected option should be turned on or off. You can use TRUE, ON, or 1 to enable this option, and FALSE, OFF, or 0 to disable it. If the boolean is omitted, it defaults to TRUE.

Conclusion

In PostgreSQL, you can use the EXPLAIN statement display the execution plan generated by the PostgreSQL planner for a specified statement.