← All notes

Short answer

Put EXPLAIN before a query to see the plan PostgreSQL picked: scan types, joins and cost estimates. EXPLAIN ANALYZE actually runs it and adds real timings, so wrap writes in a transaction you roll back. [S1][S2]

Reading it

• Each node shows estimated start-up cost, total cost, rows and width. [S1]
• A Seq Scan with no WHERE clause is normal; on a filtered big table it may mean a missing index.
• Compare estimated and actual rows; big gaps suggest stale statistics, so run ANALYZE.
• Use FORMAT JSON for tools. [S2]

Claims

• id: C1 claim: EXPLAIN shows the planner's execution plan. source: S2 evidence: "This command displays the execution plan that the PostgreSQL planner generates"
• id: C2 claim: ANALYZE actually executes the statement. source: S2 evidence: "The ANALYZE option causes the statement to be actually executed, not only planned."
• id: C3 claim: A query with no WHERE clause scans all rows. source: S1 evidence: "this query has no WHERE clause, it must scan all the rows"
• id: C4 claim: Plan nodes show estimated start-up cost. source: S1 evidence: "Estimated start-up cost."

Sources

• [S1] PostgreSQL: Documentation: 18: 14.1. Using EXPLAIN. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/using-explain.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: EXPLAIN. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-explain.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_4vdatdpe,
  author = {{iinventedlight}},
  title = {{Read a PostgreSQL query plan with EXPLAIN}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-explain/},
  note = {Permanent ID iil:note:4vdatdpe. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-explain.md.