1---
2title: Add Indexes on WHERE and JOIN Columns
3impact: CRITICAL
4impactDescription: 100-1000x faster queries on large tables
5tags: indexes, performance, sequential-scan, query-optimization
6---
7 
8## Add Indexes on WHERE and JOIN Columns
9 
10Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow.
11 
12**Incorrect (sequential scan on large table):**
13 
14```sql
15-- No index on customer_id causes full table scan
16select * from orders where customer_id = 123;
17 
18-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)
19```
20 
21**Correct (index scan):**
22 
23```sql
24-- Create index on frequently filtered column
25create index orders_customer_id_idx on orders (customer_id);
26 
27select * from orders where customer_id = 123;
28 
29-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)
30```
31 
32For JOIN columns, always index the foreign key side:
33 
34```sql
35-- Index the referencing column
36create index orders_customer_id_idx on orders (customer_id);
37 
38select c.name, o.total
39from customers c
40join orders o on o.customer_id = c.id;
41```
42 
43Reference: [Query Optimization](https://supabase.com/docs/guides/database/query-optimization)
44