Come ottimizzi una query di database lenta?

Question

Accepted Answer

**Inizia dal piano di esecuzione, poi correggi il costo maggiore.** Usa `EXPLAIN`/`EXPLAIN ANALYZE` per vedere cosa fa realmente il database, aggiungi gli **indici** giusti, elimina le query **N+1** e seleziona solo i dati di cui hai bisogno.

## Leggi il piano di esecuzione

`EXPLAIN ANALYZE` mostra il piano di esecuzione e i tempi reali. Un **Seq Scan** su una tabella grande è il classico segnale d'allarme:

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders  (cost=0.00..18500 rows=120)
--   Filter: (customer_id = 42)
--   rows removed by filter: 999880   ← scanned the whole table!
```

## Aggiungi l'indice giusto

Un indice trasforma una scansione completa in una ricerca veloce:

```sql
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now: Index Scan using idx_orders_customer  (cost=0.42..8.5 rows=120)
--   1,000,000-row scan → ~120-row lookup
```

Gli indici hanno un **costo su scritture**: ogni INSERT/UPDATE deve aggiornare anche l'indice, quindi indicizza le colonne su cui filtri/unisci/ordini — non ogni colonna. Un indice **composito** `(customer_id, created_at)` serve `WHERE customer_id = ? ORDER BY created_at` in una sola struttura.

## Correggi N+1 e over-fetching

Il **problema N+1** — una query per una lista, poi una per ogni riga — è una causa top della lentezza. Sostituiscilo con eager loading, un `JOIN`, o `IN (...)` in batch:

```sql
-- N+1: 1 + 100 queries
SELECT * FROM orders;            -- then per order: SELECT * FROM users WHERE id = ?

-- Fixed: one JOIN, only needed columns
SELECT o.id, o.total, u.name
FROM orders o JOIN users u ON u.id = o.user_id;
```

Anche **selezionare solo le colonne necessarie** (evita `SELECT *`) e **paginare** con `LIMIT`/keyset pagination in modo da non caricare mai milioni di righe.

## Perché è importante

Il database è il collo di bottiglia più comune. Leggere il piano ti dice *perché* una query è lenta invece di indovinare; l'indice giusto, rimuovere N+1 e ridurre il set di risultati routinariamente trasformano query che durano secondi in millisecondi — senza scalare hardware.