Your app feels sluggish. Database CPU spikes at 5pm. Observability says "slow query" but the code looks fine. Spoiler: it's 100% an index problem or an N+1 loop. The internet collectively lost its mind over fancy caching and query optimization techniques, but 99% of the time, the fix is boring: add one index, or rewrite one query to stop doing 1000 separate SELECTs when it could do 1 JOIN. EXPLAIN ANALYZE is your diagnostic tool. It shows you exactly what Postgres' query planner decided to do: which tables it's scanning full-table, which indexes it's using (if any), and where time is actually being spent. If you've tuned slow queries on Velocity X or Rebuild Relief dashboards, you've used this workflow: run EXPLAIN ANALYZE, read the planner output, spot the pathology, fix it, re-explain to confirm. Here's the playbook.
What EXPLAIN ANALYZE Actually Does
EXPLAIN shows you the query plan — how Postgres thinks it will execute your query. EXPLAIN ANALYZE runs the query for real and adds actual row counts and timing to the plan. No guessing. You see what the planner predicted vs what actually happened. This is the difference between a doctor's diagnosis (EXPLAIN) and a blood test (EXPLAIN ANALYZE).
-- Just the plan, no execution
explain select * from orders where user_id = 123;
-- Plan + actual numbers
explain analyze select * from orders where user_id = 123;
-- Plan + actual numbers + JSON (easier to parse)
explain (analyze, format json) select * from orders where user_id = 123;EXPLAIN ANALYZE runs your query, so don't use it on writes you don't want — wrap them in a transaction and ROLLBACK. On Supabase, connect with the Postgres client (psql, DBeaver, or pgAdmin) and run EXPLAIN ANALYZE directly. The output is a tree of nodes: each node is a step in the execution, with estimated rows, actual rows, and execution time.
Reading the Output: Nodes, Rows, and Time
Here's a real example from a Rebuild Relief query (simplified):
explain analyze
select o.id, o.created_at, u.email
from orders o
join users u on u.id = o.user_id
where o.status = 'completed';
-- Output (reformatted for clarity):
-- Seq Scan on orders o (cost=0.00..35000.00 rows=5000 width=16)
-- Filter: (status = 'completed')
-- Actual rows: 4920, actual time: 1.234..450.123 ms
-- -> Index Scan using idx_users_id on users u
-- (cost=0.43..0.48 rows=1 width=32)
-- Actual rows: 1, actual time: 0.002..0.003 msBreaking it down:
- Seq Scan on orders: Full table scan. Bad news. Postgres read every row.
- cost=0.00..35000.00: Planner's estimated cost range. Bigger number = slower. This is relative, not milliseconds.
- rows=5000: Planner thinks 5000 rows match. Actual: 4920. Close, so the planner had good stats.
- Actual rows: 4920, actual time: 1.234..450.123 ms: Real numbers. 4920 rows, took 450ms. This is the bottleneck.
- Index Scan using idx_users_id: The join used the index. Fast, 0.003ms per row.
The key metric is actual time. If a node shows actual time: 500..500 ms, that's where time is burning. The Seq Scan on orders is the culprit here: reading 4920 rows sequentially takes 450ms. Solution: add an index on status, so Postgres can jump straight to matching rows.
Three Common Pathologies (and Fixes)
Pathology 1: Sequential Scan When You Wanted an Index Scan
Seq Scan = full table read. On a million-row table, that's slow.
-- Slow query
select * from products where category = 'electronics' order by price desc;
-- EXPLAIN output shows: Seq Scan on products (cost=0.00..50000.00 rows=100000)
-- Postgres reads all 1M rows, then sorts. Takes 2000ms.
-- Fix: add index
create index idx_products_category on products(category);
-- Re-explain: now shows Index Scan using idx_products_category
-- Time drops to 50ms.The index lets Postgres jump straight to category = 'electronics' and read only matching rows. Boom, 40x faster.
Pathology 2: N+1 from the ORM or Application Code
Your ORM loads users, then loops to load their orders. One query per user. Slow.
-- Naive ORM code (pseudocode)
const users = await db.user.findMany();
const orders = await Promise.all(
users.map(u => db.order.findMany({ where: { user_id: u.id } }))
);
// If 100 users, that's 101 queries: 1 to load users, 100 to load their orders.
-- EXPLAIN ANALYZE on a single order query shows it's fast:
-- Index Scan on orders (actual time: 1.234 ms)
-- But running 100 of them takes 123ms. Still slow compared to one JOIN.
-- Fix: use a JOIN
const data = await db.user.findMany({
include: { orders: true }
});
// One query, one JOIN. 2-3ms instead of 123ms.EXPLAIN ANALYZE won't directly show the N+1 problem (you see one query at a time), but when you EXPLAIN the single query and it's fast, but your app still feels slow, ask: how many times is this query running? Use your logger (or app metrics) to count. Then refactor to batch the work into one JOIN.
Pathology 3: Missing Index on a JOIN Column
Two tables, join on a column without an index on the right side.
-- Slow join
select o.id, u.email
from orders o
join users u on u.id = o.user_id;
-- EXPLAIN shows:
-- Seq Scan on users u (cost=0.00..50000.00 rows=100000)
-- For each row in orders, Postgres does a sequential scan of all users. Ouch.
-- Fix: ensure the join column is indexed
create index idx_users_id on users(id);
-- (Primary keys are auto-indexed, but custom join columns need explicit indexes)
-- Re-explain: now shows Index Scan
-- Time: 1000ms → 8ms.The Diagnostic Workflow
Here's the script you run every time an app slows down:
Step 1: Identify the slow query. Check logs, APM, or ask a user. Example: "orders page takes 3 seconds."
Step 2: Run EXPLAIN ANALYZE on the query directly on Supabase.
-- Directly on Supabase (psql, DBeaver, pgAdmin, or the SQL editor in Supabase dashboard)
explain (analyze, format json)
select o.id, o.created_at, o.total, u.email, u.name
from orders o
join users u on u.id = o.user_id
where o.status = 'completed'
order by o.created_at desc
limit 50;Step 3: Look for Seq Scans on large tables. High actual time values. Rows bigger than you expected.
Step 4: Check if indexes exist:
-- List indexes on orders
\d orders
-- or
select indexname from pg_indexes where tablename = 'orders';Step 5: Add a missing index. Most common: the WHERE clause column, or the JOIN column.
create index idx_orders_status on orders(status);
create index idx_orders_created_at on orders(created_at);Step 6: Re-run EXPLAIN ANALYZE. Watch the time drop.
Step 7: Deploy. Test in production. Confirm with logs or APM that latency improved.
Supabase-Specific: Query Stats Dashboard
Supabase logs index usage and slow queries. Check the dashboard:
-- Find missing indexes (Supabase internal query)
select
schemaname,
tablename,
indexname,
idx_scan
from pg_stat_user_indexes
order by idx_scan asc
limit 20;
-- Shows indexes that are rarely used (candidates for removal).
-- Find slow queries from the query log
select
query,
calls,
mean_time,
max_time
from pg_stat_statements
where query like '%orders%'
order by mean_time desc;
-- Shows which queries are slowest on average and in worst case.These views are built into Postgres. Supabase surfaces them in the dashboard under "Performance" or "Logs". Check there first before diving into EXPLAIN ANALYZE.
Six FAQs
Can I run EXPLAIN ANALYZE on writes (INSERT, UPDATE, DELETE)?
Yes, but the query actually runs. If you don't want to commit changes, wrap it in a transaction: begin; explain analyze update orders set status = 'shipped'; rollback; This shows the plan + execution time without actually changing data.
What's the difference between cost and actual time?
Cost is the planner's estimate (not milliseconds, arbitrary units). Actual time is wall-clock milliseconds. If they're very different (estimate 100 rows, actual 100000 rows), the planner's statistics are stale. Run analyze table_name; to refresh statistics.
How do I know if an index will help before creating it?
Run EXPLAIN (without ANALYZE) to see the planner's plan. If it shows Seq Scan and you have a WHERE clause, an index on that column will likely help. Test it with EXPLAIN ANALYZE after creating the index. If actual time doesn't improve, drop it (indexes cost writes).
Is it bad to have too many indexes?
Yes. Each index slows down writes (INSERT, UPDATE, DELETE). Postgres has to maintain all indexes on a table. Rule of thumb: 3-5 indexes per table. Index the WHERE columns, JOIN columns, and ORDER BY columns. Skip columns you never filter/join/sort on.
My EXPLAIN ANALYZE shows a huge "Planning Time" — is that bad?
Not usually. Planning time is how long the planner took to decide on a strategy. Actual execution time is what matters for user experience. If planning time is seconds (rare), you have a complex query or stale statistics. For most apps, execution time dominates.
How often should I re-analyze my queries?
Whenever an app slows down or after adding/dropping large amounts of data. Postgres auto-analyzes periodically, but manual runs (analyze table_name;) refresh statistics immediately. On Supabase, enable autovacuum and autoanalyze (usually on by default) so stats stay fresh.
The Bottom Line
EXPLAIN ANALYZE is the first move when a query is slow. It shows you what the planner is doing, where time is actually spent, and what indexes are missing. Most slow queries have one of three problems: a sequential scan where an index would help, an N+1 loop in application code, or a missing index on a join column. The workflow is: EXPLAIN ANALYZE → spot the pathology → add an index or refactor the query → re-explain → confirm and deploy. You don't need fancy query optimization techniques. Just indexes in the right places. For more on indexing strategy and Postgres architecture, check out full-text search with tsvector. Ready to audit your database? See Aidxn Design pricing for backend performance reviews.