Read EXPLAIN ANALYZE output to find why a query is slow — the expensive node, the row estimate that was wrong, and the index that would fix it. Use when a query is slow, before adding an index, or when a plan changed unexpectedly.
--- name: read-a-query-plan category: coding description: >- Read EXPLAIN ANALYZE output to find why a query is slow — the expensive node, the row estimate that was wrong, and the index that would fix it. Use when a query is slow, before adding an index, or when a plan changed unexpectedly. --- # Read a Query Plan Adding indexes by guesswork is how tables end up with eleven indexes, slow writes, and the original slow query. The plan tells you what the database actually did. Learning to read one is a couple of hours that pays back for years. Examples are PostgreSQL; the concepts transfer. ## Always use ANALYZE ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT ...; ``` Plain `EXPLAIN` shows the plan the planner *intends*, with estimates. `ANALYZE` runs it and reports what really happened. Estimates being wrong is the single most common root cause, and you cannot see that without both numbers. `BUFFERS` adds how much data was read and whether it came from cache — the difference between a query that is slow always and one that is slow only when cold. **`ANALYZE` executes the statement.** Wrap a write in a transaction and roll it back.
You are reading the opening of this skill.
The rest is open to any signed-in account — free included. Reading the library costs nothing.
Shared publicly by the author and open for any use. See the Terms.