EXPLAIN and EXPLAIN ANALYZE
What does EXPLAIN ANALYZE do in PostgreSQL?
EXPLAIN shows how PostgreSQL intends to run a query; EXPLAIN ANALYZE also runs it and reports the actual row counts and timings of each step.
EXPLAIN prints the execution strategy the planner chose for a statement: which tables are read and how, which join methods are used, and the estimated cost and number of rows at each step. With the ANALYZE option, PostgreSQL actually executes the statement and adds the real time and row counts next to the estimates.
Comparing estimated rows with actual rows is the quickest way to spot outdated statistics or a poor choice of access method. The BUFFERS option adds how many pages were found in shared memory and how many had to be read. Because EXPLAIN ANALYZE really runs the statement, wrap statements that change data in BEGIN and ROLLBACK when you only want to inspect them.
Example
The example looks up one email address in a table that has no index on that column. The output shows a Seq Scan with both estimated and actual row counts, timings and buffer statistics.
CREATE TABLE users (id int PRIMARY KEY, email text);
INSERT INTO users
SELECT g, 'user' || g || '@example.com' FROM generate_series(1, 50000) AS g;
ANALYZE users;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'user42@example.com';Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).