postgres-query-optimization

# PostgreSQL Query Optimization

## EXPLAIN Basics

### Running EXPLAIN
```sql
-- Basic plan (no execution)
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- With actual execution times and row counts
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- With buffer/IO statistics
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;

-- Full verbose output with all options
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT * FROM orders WHERE customer_id = 123;
```

### Key Metrics to Watch
- **actual time**: First row time..last row time in milliseconds
- **rows**: Estimated vs actual row counts (large differences indicate stale statistics)
- **loops**: How many times the node executed (important for nested loops)
- **Buffers**: shared hit (cache) vs shared read (disk)—high read count = slow
- **Planning Time**: Query planning overhead
- **Execution Time**: Actual query execution time

## Scan Types

### Sequential Scan (Seq Scan)
Reads every row in the table. Acceptable for:
- Small tables (<10K rows typically)
- Queries returning large % of table (>5-10%)
- No suitable index exists

**Red flag**: Seq Scan on large table with highly selective WHERE clause.

### Index Scan
Uses B-tree index to find rows, then fetches from heap. Best for:
- Highly selective queries (<5% of rows)
- Sorted output matching index order

### Index Only Scan
Answers query entirely from index (no heap fetch). Requires:
- All needed columns in index (via INCLUDE or as key columns)
- Table's visibility map is up-to-date (run VACUUM)

**Goal**: Convert Index Scan → Index Only Scan for read-heavy queries.

### Bitmap Index Scan
Combines multiple index conditions or handles medium selectivity. Pattern:
1. Bitmap Index Scan: Build bitmap of matching pages
2. Bitmap Heap Scan: Fetch pages and recheck conditions

Good for OR conditions and medium selectivity (5-20% of rows).

## Join Types

### Nested Loop
For each row in outer table, scan inner table. B