Does EXPLAIN ANALYZE actually run the query?

Yes. EXPLAIN on its own only plans, but adding ANALYZE executes the statement in full and reports what happened, which means an insert inserts, an update updates and a delete deletes. To measure a write without keeping it, run it inside a transaction and roll back. Nothing about the plain form touches your data; everything about the analyzed form does.

What the numbers do and do not include

Actual row counts and loop counts are the point of the exercise: comparing them against the estimates beside them is how you find the node where the planner was misled, and that node is almost always the bottom of the tree rather than the expensive-looking one at the top.

Timing is less straightforward than it looks. Reading the clock at every node costs something, and on a plan with millions of node entries that overhead can exceed the query. A total that is wildly worse than the same statement run normally is usually measurement cost rather than a discovery, and turning timing off while keeping the row counts gives you the diagnosis without it.

There is also a piece the numbers have historically left out. The work of converting the result into something the client can receive, and of sending it, is not part of a node’s cost, so a plan can look fast while the statement is slow. PostgreSQL 17 added an option that measures exactly that, along with the planner’s own memory use, and on wide result sets it frequently explains the entire gap.

Getting a useful capture rather than a pretty one

Buffer counts are what turn a plan from a story into evidence, because they separate a node that was slow from a node that read a great deal. From PostgreSQL 18 they are included automatically; before that they have to be asked for, and any tooling that parses plan output should expect the extra lines either way.

Two habits are worth more than any option. Capture the plan for the parameters that were actually slow, because a plan taken with a convenient value answers a different question. And capture it more than once, since the first run pays for cold caches and the second does not.

For the difference between a query that is slow and a query whose plan changed, and for how to tell them apart after the fact, see query plan regression. Where a plan promises an index-only scan and does not deliver the speed, the reason is usually visibility rather than the index, which is what an index-only scan really needs.