• Browse categories
• Random entry
• PostgreSQL releases
  • PostgreSQL 16 (dev)
  • PostgreSQL 15 (latest)
  • PostgreSQL 14
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10 (EOL)

Version charts

Useful overviews

Useful links

EXPLAIN

EXPLAIN is a utility command for showing the execution plan of a statement.

EXPLAIN has always been present in PostgreSQL.

Usage

EXPLAIN can be used to show the execution plan of one of the following statement types:

• SELECT
• INSERT
• UPDATE
• DELETE
• VALUES
• EXECUTE
• DECLARE
• CREATE TABLE AS
• CREATE MATERIALIZED VIEW

Change history

• PostgreSQL 15
  • I/O timing for temporary file blocks tracked (efb0ef90)
• PostgreSQL 14
  • EXPLAIN VERBOSE will now display the query identifier, if compute_query_id is not disabled (commit 4f0b0966)
• PostgreSQL 13
  • WAL option added (commit 33e05f89)
• PostgreSQL 12
  • SETTINGS option added (commit ea569d64)
• PostgreSQL 11
  • parallel workers' sort activity displayed in output (commit bf11e7ee)
• PostgreSQL 10
  • SUMMARY option added (commit f9b1a0dd)
• PostgreSQL 9.4
  • EXPLAIN ANALYZE now shows planning time (commit 9347baa5)
  • EXPLAIN ANALYZE now shows exact and lossy block counts in bitmap heap scans (commit 2bb1f14b)
  • total runtime label renamed to execution time (commit 5f86cbd7)
  • now shows  the grouping columns in Agg and Group nodes (commit f2609905)
• PostgreSQL 9.2
  • TIMING option added (commit af7914c6)
• PostgreSQL 9.0
  • EXPLAIN (option [, ...]) syntax added (commit d4382c4a)
  • BUFFERS option added (commit cddca5ec)
  • COSTS option added (commit d4382c4a)
  • FORMAT option added with following output formats:
    
    • TEXT (default)
    • JSON (commit 9bd27b7c)
    • YAML (commit 324385d6)
    • XML (commit 9bd27b7c)
• PostgreSQL 8.4
  • VERBOSE option now prints the target list of each plan node, rather than dumping the internal representation (commit 87a2f050)
• PostgreSQL 7.2
  • ANALYZE option added (commit 89fa5518)

Examples

postgres=# EXPLAIN SELECT * FROM bar;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on bar  (cost=0.00..35.50 rows=2550 width=4)
(1 row)

postgres=# EXPLAIN COPY bar FROM STDIN;
ERROR:  syntax error at or near "COPY"
LINE 1: EXPLAIN COPY bar FROM STDIN;

References

Useful links

Categories