1---
2title: Use EXPLAIN ANALYZE to Diagnose Slow Queries
3impact: LOW-MEDIUM
4impactDescription: Identify exact bottlenecks in query execution
5tags: explain, analyze, diagnostics, query-plan
6---
7 
8## Use EXPLAIN ANALYZE to Diagnose Slow Queries
9 
10EXPLAIN ANALYZE executes the query and shows actual timings, revealing the true performance bottlenecks.
11 
12**Incorrect (guessing at performance issues):**
13 
14```sql
15-- Query is slow, but why?
16select * from orders where customer_id = 123 and status = 'pending';
17-- "It must be missing an index" - but which one?
18```
19 
20**Correct (use EXPLAIN ANALYZE):**
21 
22```sql
23explain (analyze, buffers, format text)
24select * from orders where customer_id = 123 and status = 'pending';
25 
26-- Output reveals the issue:
27-- Seq Scan on orders (cost=0.00..25000.00 rows=50 width=100) (actual time=0.015..450.123 rows=50 loops=1)
28--   Filter: ((customer_id = 123) AND (status = 'pending'::text))
29--   Rows Removed by Filter: 999950
30--   Buffers: shared hit=5000 read=15000
31-- Planning Time: 0.150 ms
32-- Execution Time: 450.500 ms
33```
34 
35Key things to look for:
36 
37```sql
38-- Seq Scan on large tables = missing index
39-- Rows Removed by Filter = poor selectivity or missing index
40-- Buffers: read >> hit = data not cached, needs more memory
41-- Nested Loop with high loops = consider different join strategy
42-- Sort Method: external merge = work_mem too low
43```
44 
45Reference: [EXPLAIN](https://supabase.com/docs/guides/database/inspect)
46