LEARN. PRACTICE. GROW.Concept and mechanism
EXPLAIN shows an estimated plan; EXPLAIN ANALYZE executes the statement and collects runtime measurements. This also applies to statements modifying data. Use a controlled environment for experiments with effects and do not assume ROLLBACK removes sequence changes or external actions. Compare estimated and observed rows, repeated operations, and buffer access. Plan costs are not milliseconds. Unrepresentative statistics or difficult-to-estimate predicates can explain wrong cardinality. ANALYZE updates statistical information, but an index is not automatically the best choice for a query reading much of a table. Compare behavior under the workload that matters to the service.
Guided application
In a fictional report, an isolated improvement can degrade close when many users run the same query. work_mem applies to operations, with multiple consumers per query and session; hash operations have their own multiplier. Measure concurrency before raising limits globally. pg_stat_statements helps distinguish accumulated impact from individual latency but is not a complete parameter history. CREATE INDEX CONCURRENTLY reduces write blocking during construction, with its own costs and restrictions. Failure can leave an INVALID index that still incurs overhead. Change acceptance requires valid state, an observed plan, and measurable improvement beyond object existence.
An estimate of 20 rows and actual execution of 80000 guide investigation; they do not prove that forcing an index is the fix.
Common pitfalls
ANALYZE as read-only; cost as time; work_mem as a global ceiling; created index as success.
Related topics: Connections, identities, and privileges · Transactions, blocking, and resumption · Vacuum, configuration, and capacity
Optimize execution and workload using verifiable criteria.
Reference: Execution plans and runtime evidence · PostgreSQL 18 reference semantics;18.6 current stable at review