Sql Database Assistant

# Query Optimization Guide

How to read EXPLAIN plans, choose the right index types, understand query plan operators, and configure connection pooling.

---

## Reading EXPLAIN Plans

### PostgreSQL — EXPLAIN ANALYZE

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;
```

**Sample output:**
```
Limit  (cost=0.43..12.87 rows=20 width=128) (actual time=0.052..0.089 rows=20 loops=1)
  ->  Index Scan Backward using idx_orders_status_created on orders  (cost=0.43..4521.33 rows=7284 width=128) (actual time=0.051..0.085 rows=20 loops=1)
        Index Cond: (status = 'paid')
        Buffers: shared hit=4
Planning Time: 0.156 ms
Execution Time: 0.112 ms
```

**Key fields to check:**

| Field | What it tells you |
|-------|-------------------|
| `cost` | Estimated startup..total cost (arbitrary units) |
| `rows` | Estimated row count at that node |
| `actual time` | Real wall-clock time in milliseconds |
| `actual rows` | Real row count — compare against estimate |
| `Buffers: shared hit` | Pages read from cache (good) |
| `Buffers: shared read` | Pages read from disk (slow) |
| `loops` | How many times the node executed |

**Red flags:**
- `Seq Scan` on a large table with a WHERE clause — missing index
- `actual rows` >> `rows` (estimated) — stale statistics, run `ANALYZE`
- `Nested Loop` with high loop count — consider hash join or add index
- `Sort` with `external merge` — not enough `work_mem`, spilling to disk
- `Buffers: shared read` much higher than `shared hit` — cold cache or table too large for memory

### MySQL — EXPLAIN FORMAT=JSON

```sql
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;
```

**Key fields:**
- `query_block.select_id` — identifies subqueries
- `table.access_type` — `ALL` (full scan), `ref` (index lookup), `range`, `index`, `const`
- `table.rows_examined_per_scan` — how many rows the engine reads
- `table.using_index` — covering index (no table lookup needed)
- `table.attached_condition` — the WHERE filter applied

**Access types ranked (best to worst):**
`system` > `const` > `eq_ref` > `ref` > `range` > `index` > `ALL`

---

## Index Types

### B-tree (default)

The workhorse index. Supports equality, range, prefix, and ORDER BY operations.

**Best for:** `=`, `<`, `>`, `<=`, `>=`, `BETWEEN`, `LIKE 'prefix%'`, `ORDER BY`, `MIN()`, `MAX()`

```sql
CREATE INDEX idx_orders_created ON orders (created_at);
```

**Composite B-tree:** Column order matters. The index is useful for queries that filter on a leftmost prefix of the indexed columns.

```sql
-- This index serves: WHERE status = ... AND created_at > ...
-- Also serves: WHERE status = ...
-- Does NOT serve: WHERE created_at > ... (without status)
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
```

### Hash

Equality-only lookups. Faster than B-tree for exact matches but no range support.

**Best for:** `=` lookups on high-cardinality columns

```sql
-- PostgreSQL
CREATE INDEX idx_sessions_token ON sessions USING hash (token);
```

**Limitations:** No range queries, no ORDER BY, not WAL-logged before PostgreSQL 10.

### GIN (Generalized Inverted Index)

For multi-valued data: arrays, JSONB, full-text search vectors.

```sql
-- JSONB containment
CREATE INDEX idx_products_tags ON products USING gin (tags);
-- Query: SELECT * FROM products WHERE tags @> '["sale"]';

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING gin (to_tsvector('english', title || ' ' || body));
```

### GiST (Generalized Search Tree)

For geometric, range, and proximity data.

```sql
-- Range type (e.g., date ranges)
CREATE INDEX idx_bookings_period ON bookings USING gist (during);
-- Query: SELECT * FROM bookings WHERE during && '[2025-01-01, 2025-01-31]';

-- PostGIS geometry
CREATE INDEX idx_locations_geom ON locations USING gist (geom);
```

### BRIN (Block Range INdex)

Tiny index for naturally ordered data (e.g., time-