Query Execution Plan

A query execution plan describes how the database will obtain a SQL result: which relations it scans, how it joins them, and where it sorts or aggregates. The optimizer chooses among available plans using estimates of row counts and work. Statistics, predicates, correlations, and available indexes influence those estimates.

In PostgreSQL, plain EXPLAIN shows estimates; EXPLAIN ANALYZE executes the statement and adds observations. Estimated cost is in planner units, not milliseconds. Actual rows and time are averages per execution when a node has several loops: 20 rows over 100 loops means about 2,000 emitted rows. Parent costs include child work, so adding every node’s total double counts it.

BUFFERS shows database buffer activity. A shared read may be served by the operating system cache and does not prove a storage-device read. Start with the earliest substantial estimate error or unexpectedly large work, then inspect its predicates and statistics. SQLite EXPLAIN QUERY PLAN uses a different format and does not supply PostgreSQL’s actual timing fields. ANALYZE executes writes too; inspect such statements only in a suitable controlled environment.

Reference: Official documentation. See the worked examples in Relational Database Internals.