Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateAdvanced PostgreSQL

Theory: EXPLAIN and ANALYZE

The query

SQL
-- Q: What is the difference between EXPLAIN and EXPLAIN ANALYZE?
-- A: EXPLAIN shows the *estimated* execution plan (cost, rows) generated 
-- by the query planner without actually running the query.
-- EXPLAIN ANALYZE actually *executes* the query and shows the estimated plan 
-- alongside the *actual* execution times and row counts.
Tested against PostgreSQL 16

Note

“Always use EXPLAIN ANALYZE when hunting down slow queries, but be careful on production as it runs the query (e.g. DELETE will delete!).