What each line of an EXPLAIN ANALYZE plan means, how to spot bad row estimates, sequential scans that should be index scans, sorts that spill to disk and loops that multiply cost, with a worked example of fixing one query.

Postgres answers "why is this query slow" with EXPLAIN ANALYZE, and its output is a tree of nodes with two sets of numbers that rarely agree. Reading it well comes down to comparing estimated and actual rows, multiplying by loops and finding the node where the time goes.
This guide applies those habits to an example plan and fixes the query at the end.
Run it with both options: ANALYZE executes the query and records actual timings, and BUFFERS adds how many 8 kB pages each node read from cache or disk, as described in the EXPLAIN reference:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price_cents
FROM products
WHERE category_id = 42 AND active
ORDER BY price_cents
LIMIT 50;
ANALYZE runs the statement, so wrap an UPDATE or DELETE in BEGIN and ROLLBACK. Run it against data that resembles production, because the planner chooses differently on a table with 200 rows than on one with 20 million.
Each line of the plan is a node, indented under the node that consumes its output. The following is illustrative example output for the query above:
Limit (cost=48211.30..48211.42 rows=50 width=40) (actual time=412.8..412.9 rows=50 loops=1) Buffers: shared hit=1203 read=36988 -> Sort (cost=48211.30..48236.10 rows=9920 width=40) (actual time=412.8..412.8 rows=50 loops=1) Sort Key: price_cents Sort Method: top-N heapsort Memory: 31kB -> Seq Scan on products (cost=0.00..47881.00 rows=9920 width=40) (actual time=0.03..409.6 rows=10213 loops=1) Filter: (active AND (category_id = 42)) Rows Removed by Filter: 1989787 Planning Time: 0.2 ms Execution Time: 412.9 ms
The cost pair is the planner's estimate in arbitrary units, startup cost then total cost, and rows is how many rows it expected. The actual time pair is actual milliseconds to the first row and to the last row, and the actual rows is what came out. Both time and rows in the actual section are per loop, so multiply by loops before comparing nodes.
Four patterns explain most slow plans:
In the illustrative plan above, the Seq Scan read about two million rows and threw away 99.5% of them, and Buffers: read=36988 shows a read of roughly 290 MB from outside the shared buffer cache. The estimate of 9,920 rows against an actual 10,213 is close, so statistics are fine and the problem is the access path.
An index on the filter columns, followed by the sort column, lets Postgres read only matching rows in the right order and stop after 50:
CREATE INDEX products_category_active_price_idx
ON products (category_id, price_cents)
WHERE active;
The expected new plan is an Index Scan using products_category_active_price_idx under the Limit, with no sort node. The partial WHERE active clause keeps inactive products out of the index, which the partial index docs recommend when queries always filter on the same condition.
When estimates are badly wrong instead, check pg_stats for the columns involved and run ANALYZE products. For columns that depend on each other, such as city and postal code, CREATE STATISTICS lets the planner model the correlation, as covered in the planner statistics docs.
The top node's time includes all of its children, so the slow part is the deepest node whose own time is large rather than the root. A Bitmap Heap Scan with Recheck Cond is normal for an index lookup returning many rows and is not a sign of trouble on its own. Planning time above a few milliseconds usually means a query with many joins or partitions rather than a slow execution.
Best fit: run EXPLAIN (ANALYZE, BUFFERS) on every query that pg_stat_statements ranks near the top, and compare estimates with actuals before adding indexes.
Encore is a backend framework with built-in infrastructure, and the database a service declares comes with a local Postgres from encore run and a shell from encore db shell catalog, which is where EXPLAIN ANALYZE runs during development. The index from the example belongs in the service's migrations, so it ships with the query that needs it:
Run in your terminal to get started locally.
// catalog/catalog.ts
import { api } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";
// Migrations in ./migrations create products and the partial index above.
const db = new SQLDatabase("catalog", { migrations: "./migrations" });
interface Product {
id: number;
name: string;
priceCents: number;
}
export const byCategory = api(
{ expose: true, method: "GET", path: "/categories/:categoryId/products" },
async ({ categoryId }: { categoryId: number }): Promise<{ products: Product[] }> => {
const products = await db.queryAll<Product>`
SELECT id, name, price_cents AS "priceCents"
FROM products
WHERE category_id = ${categoryId} AND active
ORDER BY price_cents
LIMIT 50
`;
return { products };
},
);
Every call to byCategory produces a trace with the query's duration, visible in the local development dashboard, so the before and after of the index is one request away. The TypeScript databases docs cover migrations and the query helpers, and in production the same migration runs against RDS or Cloud SQL in your own account, as the AWS infrastructure docs describe.
An AI coding agent working on a slow endpoint can read the plan, add the index as a migration and confirm the faster trace locally, all inside one change that a reviewer reads in order.
Best fit: teams who want query plans checked locally against the same schema and migrations that production runs.
Start from the queries that cost the most in total, run EXPLAIN (ANALYZE, BUFFERS) with realistic parameters, and look for discarded rows, wrong estimates, disk sorts and large loop counts in that order. Fix the access path with an index or the estimates with ANALYZE, then confirm with the same command and the request trace.
EXPLAIN shows the plan Postgres would use and its cost estimates without running the query. EXPLAIN ANALYZE runs the query and adds the actual time, row counts and loops for each node, so you can compare estimates with reality.
Yes. EXPLAIN ANALYZE executes the statement, so an UPDATE or DELETE changes data. Wrap data-changing statements in BEGIN and ROLLBACK when you analyze them.
Loops is how many times the node ran. The actual time and rows shown are averages per loop, so a node with actual time 0.05 ms and loops=20000 spent about one second in total.
Run encore db shell with the database name to open psql against the local database started by encore run, then prefix the query with EXPLAIN (ANALYZE, BUFFERS). The same command with --env connects to a deployed environment.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.