Ottimizzazione delle Query PostgreSQL: Da Lente a Sub-Millisecondi

Il mese scorso ho ricevuto un messaggio Slack alle 2 di notte: "La dashboard va in timeout." Mi sono collegato in SSH al server di produzione, ho aperto pg_stat_activity e ho trovato una singola query che scansionava 14 milioni di righe per restituire 12 risultati. La soluzione è stata un indice parziale che ha richiesto 30 secondi per essere creato. La query è passata da 4,2 secondi a 0,3 millisecondi.

Questo è il punto con le prestazioni di PostgreSQL. I problemi non sono quasi mai esotici. Sono indici mancanti, statistiche obsolete, query che avevano senso quando la tabella aveva 10.000 righe ma ora ne ha 10 milioni. Le soluzioni sono generalmente semplici — una volta che sai dove guardare.

Questo post è tutto ciò che ho imparato sull'ottimizzazione delle query PostgreSQL gestendo database di produzione. Niente teoria senza pratica. Ogni tecnica qui presentata mi ha fatto risparmiare tempo reale su sistemi reali.

La Mentalità del Debug: Non Tirare a Indovinare, Misura#

L'errore più grande che vedo fare agli sviluppatori con le query lente è tirare a indovinare. "Forse ci serve un indice su quella colonna." "Forse la JOIN è lenta." "Forse dovremmo aggiungere più RAM."

Smetti di indovinare. PostgreSQL ha uno dei migliori analizzatori di query di qualsiasi database. Usalo.

EXPLAIN — Il Progetto#

Il semplice EXPLAIN mostra cosa PostgreSQL pianifica di fare, senza eseguire effettivamente la query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

Questo mostra il piano — un index scan — e il costo stimato. Ma è una previsione. PostgreSQL potrebbe sbagliarsi completamente sul conteggio delle righe. Ho visto stime di "1 riga" quando il risultato effettivo era di 50.000 righe. Questo tipo di errore di stima si propaga in scelte di piano pessime.

Usa il semplice EXPLAIN quando vuoi dare un'occhiata rapida al piano senza eseguire effettivamente la query. Questo è importante quando stai analizzando un DELETE che modificherebbe i dati, o una query che impiega 30 minuti per essere eseguita.

EXPLAIN ANALYZE — La Verità#

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Ora vedi il tempo di esecuzione effettivo e il conteggio effettivo delle righe. È qui che avviene il debug reale. Confronta rows=1 (stimato) con rows=1 (effettivo) — corrispondono, quindi il planner ha preso una buona decisione. Quando non corrispondono, questo è il tuo primo indizio.

Attenzione: EXPLAIN ANALYZE esegue effettivamente la query. Se stai analizzando un UPDATE o DELETE, avvolgilo in una transazione:

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Il Quadro Completo#

Questo è quello che uso effettivamente il 90% delle volte:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

L'output BUFFERS è fondamentale. shared hit=312 significa che 312 pagine provenivano dalla cache del buffer (RAM). read=45 significa che 45 pagine hanno dovuto essere lette dal disco. Se vedi molto read rispetto a hit, il tuo shared_buffers potrebbe essere troppo piccolo, o la tabella è troppo grande per rimanere in cache.

Uso anche FORMAT JSON quando ho bisogno di incollare il piano su explain.dalibo.com per la visualizzazione. La vista ad albero rende i piani complessi molto più facili da leggere:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Opzioni EXPLAIN che Uso Realmente#

Ecco la query diagnostica completa che eseguo per prima per qualsiasi indagine su query lente:

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

• TIMING — temporizzazione per nodo (attivo di default con ANALYZE, ma lo specifico esplicitamente)
• VERBOSE — mostra le liste delle colonne di output e i nomi delle tabelle qualificati con lo schema

Leggere i Piani di Query Come un Detective#

Ogni piano di query è un albero. PostgreSQL lo legge dal basso verso l'alto: i nodi più profondi vengono eseguiti per primi, alimentando i risultati verso l'alto. Comprendere i tipi di scansione è la base di tutto il resto.

Seq Scan — La Scansione Completa della Tabella#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

La scansione sequenziale legge ogni singola riga della tabella. Per una tabella con un milione di righe, significa ogni pagina su disco.

Ma ecco la sfumatura: il Seq Scan non è sempre negativo. Se stai selezionando il 30% o più della tabella, una scansione sequenziale è in realtà più veloce di un index scan perché l'I/O sequenziale è molto più veloce dell'I/O casuale. PostgreSQL lo sa. Se sta scegliendo un Seq Scan quando hai un indice, controlla quanto è selettiva la tua clausola WHERE.

Quando è un problema: quando stai selezionando una piccola frazione di righe da una tabella grande e non c'è un indice.

Index Scan — La Ricerca Mirata#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Va all'indice B-tree, trova le voci corrispondenti, poi recupera le righe effettive dall'heap della tabella. Ogni recupero di riga è un'operazione di I/O casuale verso l'heap. Questo è ottimo per query altamente selettive ma ogni recupero dall'heap ha un costo.

Index Only Scan — Il Santo Graal#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

Questo è il tipo di scansione più veloce. PostgreSQL ottiene tutto ciò di cui ha bisogno solo dall'indice, senza mai toccare l'heap della tabella. Vedi questo quando tutte le colonne che usi in SELECT e WHERE sono nell'indice.

Il trucco: Heap Fetches: 0 significa che la mappa di visibilità è aggiornata. Se la tua tabella ha molte tuple morte (non è stata fatta vacuum di recente), PostgreSQL deve comunque controllare l'heap per verificare la visibilità delle righe. Questa è una ragione per cui l'autovacuum è importante per le prestazioni, non solo per lo spazio su disco.

Bitmap Scan — La Via di Mezzo#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Le bitmap scan sono la risposta di PostgreSQL al problema "troppe righe per un index scan, troppo poche per un seq scan". Costruisce una bitmap di quali pagine contengono righe corrispondenti, le ordina per posizione fisica, poi le recupera in ordine. Questo converte l'I/O casuale in I/O sequenziale.

Vedrai spesso bitmap scan quando due o più indici vengono combinati:

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Questo BitmapAnd combina due indici separati. È PostgreSQL che dice "non ho un indice composito, ma posso combinare questi due indici a colonna singola." Funziona, ma un indice composito appropriato sarebbe più veloce.

Individuare Stime Errate#

La cosa numero uno che cerco in un piano di query sono le stime non corrispondenti:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

Il planner ha stimato 1 riga. Il risultato effettivo è stato 48.753 righe. Questo è un errore di stima di cinque ordini di grandezza. Il planner ha scelto un Nested Loop perché pensava di fare il join contro 1 riga. Con 48.753 righe, un Hash Join sarebbe stato di ordini di grandezza più veloce.

Cause comuni di stime errate:

• Statistiche obsolete: Esegui ANALYZE sulla tabella
• Colonne correlate: Il planner assume che i valori delle colonne siano indipendenti. Se status = 'shipped' e created_at > '2026-01-01' sono correlati (gli ordini più recenti sono spediti), il planner sottostima la selettività combinata
• Funzioni personalizzate nel WHERE: Il planner usa una stima di selettività predefinita (di solito 0,5% per uguaglianza, 33% per range) quando non può analizzare una funzione
• Query parametrizzate con piani generici: Dopo 5 esecuzioni, PostgreSQL potrebbe passare a un piano generico che non considera il valore effettivo del parametro

Quando vedi stime errate, la soluzione è di solito una di: eseguire ANALYZE, creare statistiche estese, riscrivere la query, o usare una CTE come barriera di ottimizzazione.

-- Crea statistiche estese per colonne correlate
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Strategia degli Indici: L'Indice Giusto per il Compito Giusto#

Gli indici non sono gratuiti. Ogni indice rallenta le scritture, consuma spazio su disco e richiede manutenzione. L'obiettivo non è "indicizzare tutto" — è "indicizzare esattamente ciò di cui hai bisogno."

B-tree — Il Cavallo da Lavoro Predefinito#

B-tree è il tipo predefinito e gestisce la stragrande maggioranza dei casi. Supporta operatori di uguaglianza e range (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

CREATE INDEX idx_orders_created_at ON orders (created_at);

L'ordine delle colonne nell'indice B-tree è enormemente importante per gli indici compositi. La colonna più a sinistra è l'ordinamento primario, poi la successiva, e così via. Un indice su (a, b, c) può rispondere efficientemente a:

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND b > 5
• WHERE a = 1 ORDER BY b

Non può rispondere efficientemente a:

• WHERE b = 2 (salta la prima colonna)
• WHERE c = 3 (salta le prime due colonne)
• WHERE a = 1 AND c = 3 (buco nel mezzo — la condizione a è usata, la condizione c richiede un filtro)

Pensalo come un elenco telefonico ordinato per cognome, poi nome. Puoi trovare rapidamente tutte le voci "Rossi", o specificamente "Rossi, Mario". Ma non puoi trovare rapidamente tutte le voci "Mario" senza scansionare l'intero elenco.

La regola: metti prima le condizioni di uguaglianza, poi le condizioni di range, poi le colonne di ordinamento.

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Buono: uguaglianza prima, poi range, poi ordinamento
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Cattivo: range prima dell'uguaglianza
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Indici Covering con INCLUDE#

PostgreSQL 11+ ti permette di aggiungere colonne non-chiave a un indice con INCLUDE. Queste colonne sono memorizzate nelle pagine foglia ma non fanno parte della struttura B-tree. Abilitano gli index-only scan senza appesantire l'albero:

-- Eseguiamo frequentemente:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Ora PostgreSQL può soddisfare l'intera query solo dall'indice. Senza INCLUDE, avrebbe dovuto recuperare total e status dall'heap. La differenza con una cache fredda può essere drammatica — ho visto query passare da 50ms a 0,2ms solo aggiungendo colonne INCLUDE.

Indici Parziali — Indicizza Solo Ciò Che Conta#

Questa è la mia singola funzionalità preferita di PostgreSQL e quella che la maggior parte degli sviluppatori non conosce.

-- Solo il 2% degli ordini è 'pending', ma li interroghiamo costantemente
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Questo indice è minuscolo rispetto a un indice completo su created_at. Contiene solo righe dove status = 'pending'. Le query che corrispondono alla clausola WHERE usano questo indice piccolo e veloce. Le query che non corrispondono lo ignorano.

Esempio reale dalla produzione: avevo una tabella sessions con 50 milioni di righe. Solo circa 200.000 erano attive (non scadute). Un indice completo su user_id era 1,2 GB. Un indice parziale WHERE expires_at > now() era 8 MB. La query è passata da 12ms a 0,1ms perché l'intero indice stava in cache.

-- Un altro pattern comune: soft delete
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Vincolo unico solo sui record attivi
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Quest'ultimo è incredibilmente utile. Ti permette di avere un vincolo di email univoco sugli utenti attivi pur permettendo alla stessa email di apparire nei record cancellati.

Indici GIN — Ricerca Full-Text e JSONB#

GIN (Generalized Inverted Index) è la risposta quando hai bisogno di cercare all'interno dei valori — array, documenti JSONB o vettori di ricerca full-text.

-- Query di contenimento JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Ora questo è veloce:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Ricerca full-text
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

Gli indici GIN sono grandi e lenti da costruire, ma fulminei da interrogare. Valgono la pena per carichi di lavoro con molte letture e query di contenimento complesse.

Per JSONB, c'è anche jsonb_path_ops che è più piccolo e veloce per le query di contenimento @>, ma non supporta gli operatori di esistenza (?, ?|, ?&):

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

Indici GiST — Tipi Geometrici e Range#

GiST (Generalized Search Tree) gestisce tipi di dati sovrapposti: forme geometriche, range, ricerca full-text (alternativa a GIN, più piccolo ma più lento).

-- Ricerche di range IP
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Range temporali (conflitti di prenotazione)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Trovare prenotazioni sovrapposte:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Indici Hash — Di Nicchia ma Utili#

Gli indici Hash sono utili solo per confronti di uguaglianza. Da PostgreSQL 10 sono registrati nel WAL e sicuri in caso di crash. Sono più piccoli dei B-tree per colonne larghe e leggermente più veloci per la pura uguaglianza:

-- Buono per ricerche solo-uguaglianza su colonne di testo grandi
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- Questo è veloce:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- Questo NON PUÒ usare l'indice hash:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

In pratica, uso raramente gli indici hash. B-tree gestisce bene l'uguaglianza, e l'incapacità di fare query di range o ordinamento rende gli indici hash troppo rigidi per la maggior parte dei casi d'uso reali.

Quando gli Indici Fanno Male#

Ogni indice ha un costo:

• Amplificazione delle scritture: Ogni INSERT aggiorna ogni indice sulla tabella. Una tabella con 8 indici significa 8 scritture aggiuntive per INSERT
• Aggiornamenti HOT bloccati: Gli aggiornamenti Heap-Only Tuple (HOT) sono un'ottimizzazione importante dove PostgreSQL può aggiornare una riga senza aggiornare gli indici, ma solo se nessuna colonna indicizzata è cambiata. Più indici = più possibilità di bloccare gli aggiornamenti HOT
• Sovraccarico del vacuum: Più indici significa che il vacuum impiega più tempo
• Sovraccarico del planner: Più indici significa che il planner ha più opzioni da valutare

Controllo regolarmente gli indici sulle tabelle di produzione:

-- Trova indici inutilizzati
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- escludi chiave primaria e vincoli unici
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Ho trovato indici multi-gigabyte che erano stati creati durante una migrazione una tantum e mai più utilizzati. Eliminarli ha velocizzato le scritture in modo notevole.

-- Trova indici duplicati (stesse colonne, stesso ordine)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

Il Problema delle Query N+1#

Ogni sviluppatore che usa un ORM ci sbatte contro prima o poi. È il problema di prestazioni più comune che debuggo.

Come si Presenta N+1#

# Python / SQLAlchemy — la classica trappola
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # Questo innesca una query separata per OGNI ordine
    print(f"Customer: {order.customer.name}")

Questo genera:

-- Query 1: Ottieni tutti gli ordini in attesa
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Ottieni il cliente per l'ordine 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Ottieni il cliente per l'ordine 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Ottieni il cliente per l'ordine 500
SELECT * FROM customers WHERE id = 600;

501 query invece di 1 o 2. Ogni query è veloce individualmente, forse 0,5ms. Ma 501 di esse si sommano a 250ms di solo tempo di database, più la latenza del round-trip di rete per ciascuna.

Rilevare N+1 nei Log#

Il modo più veloce per catturare le query N+1 è abilitare temporaneamente il logging delle istruzioni:

-- Logga tutte le query che impiegano più di 0ms (cioè tutte le query)
SET log_min_duration_statement = 0;
 
-- Meglio per la produzione: logga solo le query lente
ALTER SYSTEM SET log_min_duration_statement = 50;  -- soglia 50ms
SELECT pg_reload_conf();

Poi guarda i log. N+1 è inconfondibile — vedrai centinaia di query identiche con valori di parametro diversi in rapida successione.

Un approccio più mirato per lo sviluppo:

-- Abilita auto_explain per query lente
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Questo logga l'output completo di EXPLAIN ANALYZE per qualsiasi query oltre i 100ms, incluse le query all'interno delle funzioni.

La Soluzione: Caricamento Eager o JOIN#

Approccio ORM — di' all'ORM di caricare i dati correlati in anticipo:

# SQLAlchemy — joinedload recupera i clienti nella stessa query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Approccio SQL puro — usa semplicemente una JOIN:

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Una query. Fatto.

JOIN vs Query Multiple#

C'è un dibattito valido qui. A volte due query sono meglio di una JOIN:

-- Approccio 1: Singola JOIN (potrebbe produrre dati duplicati se uno-a-molti)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approccio 2: Due query (meno trasferimento dati se gli ordini hanno molte colonne)
SELECT * FROM orders WHERE status = 'pending';
-- l'applicazione raccoglie order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

L'approccio JOIN duplica i dati dell'ordine per ogni articolo. Se ogni ordine ha 20 articoli e la riga dell'ordine è larga, questo significa 20x di trasferimento dati. L'approccio a due query invia ogni ordine esattamente una volta.

La mia regola: usa le JOIN per relazioni uno-a-uno, considera query separate per uno-a-molti quando il lato "uno" è largo. Ma fai sempre benchmark — il round-trip di rete di una seconda query spesso costa più dei dati duplicati.

Riscritture Comuni delle Query#

Alcune query sono lente non per indici mancanti ma per come sono scritte. Il planner di PostgreSQL è bravo, ma non è magico.

Subquery vs JOIN vs CTE#

Questi tre approcci possono produrre piani molto diversi:

-- Subquery nel WHERE — spesso va bene, a volte terribile
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — di solito la scelta migliore
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materializzata in PG 11 e precedenti, ottimizzata in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

In PostgreSQL 12+, le CTE vengono di solito inlineate (il planner le tratta come subquery), quindi le prestazioni sono identiche. Ma in PostgreSQL 11 e precedenti, le CTE sono barriere di ottimizzazione — il planner le materializza e non può spingere i predicati attraverso di esse. Se sei ancora su PG 11, evita le CTE per le query critiche per le prestazioni.

EXISTS vs IN vs JOIN#

Questo viene fuori costantemente:

-- EXISTS — si ferma alla prima corrispondenza, ottimo per verificare l'esistenza
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN con subquery — costruisce prima l'intero set di risultati
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — può produrre duplicati se returns ha più righe per ordine
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Per tabelle grandi, EXISTS spesso vince perché fa cortocircuito. La versione con IN deve costruire l'intera lista di order_id dei resi prima di filtrare. La versione con JOIN può produrre duplicati, richiedendo DISTINCT che aggiunge un passaggio di ordinamento o hash.

La mia scelta predefinita: usa EXISTS quando verifichi l'esistenza di righe correlate. È la più chiara semanticamente e di solito la più veloce.

Ma c'è un controesempio. Se il set di risultati della subquery è piccolo e ne hai bisogno per condizioni multiple:

-- Se la subquery restituisce poche righe, IN va benissimo
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

Evitare SELECT *#

Questa non è solo una preferenza di stile del codice. Ha implicazioni reali sulle prestazioni:

-- Male: recupera tutte le 25 colonne inclusa una colonna TEXT con kilobyte di dati
SELECT * FROM products WHERE category = 'electronics';
 
-- Bene: recupera solo ciò di cui hai bisogno
SELECT id, name, price FROM products WHERE category = 'electronics';

Con SELECT *:

• Non puoi usare index-only scan (tutte le colonne dovrebbero essere nell'indice)
• Trasferisci più dati sulla rete
• Usi più memoria per ordinamento e hashing
• Se qualcuno aggiunge una colonna BYTEA da 10MB dopo, le tue query esistenti diventano silenziosamente più lente

Funzioni Window vs Subquery#

Le funzioni window sono una delle funzionalità più potenti di PostgreSQL e quasi sempre superano le subquery correlate:

-- Lento: la subquery correlata viene eseguita una volta per riga
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Veloce: le funzioni window calcolano in un singolo passaggio
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Un altro pattern comune — ottenere la riga più recente per gruppo:

-- Lento: subquery correlata
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Veloce: DISTINCT ON (specifico di PostgreSQL, estremamente utile)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Anche veloce: funzione window ROW_NUMBER
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON è la mia scelta preferita per questo pattern. È conciso, leggibile e PostgreSQL lo ottimizza bene con l'indice giusto:

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Paginazione Fatta Bene#

OFFSET è una trappola per grandi dataset:

-- Pagina 1: veloce
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Pagina 500: lenta — PostgreSQL deve scansionare e scartare 9.980 righe
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Pagina 5000: molto lenta — scansiona 99.980 righe per restituirne 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

La soluzione è la paginazione keyset (chiamata anche paginazione basata su cursore):

-- Prima pagina
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Pagina successiva: usa l'ultimo id dalla pagina precedente
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

Questa è sempre veloce perché usa l'indice per saltare direttamente alla posizione giusta, indipendentemente da quale "pagina" ti trovi. Il compromesso è che non puoi saltare a un numero di pagina arbitrario, ma per lo scroll infinito o le interfacce "pagina successiva", la paginazione keyset è strettamente superiore.

Per ordinamenti complessi:

-- Paginazione keyset con colonne di ordinamento multiple
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Questo usa il confronto row-value, che PostgreSQL gestisce efficientemente con un indice composito su (price, id).

Statistiche delle Tabelle e Vacuuming#

Il planner di PostgreSQL prende decisioni basate sulle statistiche dei tuoi dati. Statistiche errate portano a piani errati. È così semplice.

ANALYZE: Aggiorna le Statistiche#

-- Analizza una singola tabella
ANALYZE orders;
 
-- Analizza l'intero database
ANALYZE;
 
-- Visualizza le statistiche correnti per una colonna
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Il valore correlation è particolarmente interessante. Varia da -1 a 1 e misura quanto bene l'ordine fisico delle righe corrisponde all'ordine logico della colonna. Una correlazione vicina a 1 o -1 significa che i dati sono fisicamente ordinati su quella colonna, rendendo le scansioni di range molto efficienti (I/O sequenziale). Una correlazione vicina a 0 significa I/O casuale per le query di range.

-- Aumenta il target delle statistiche per colonne con distribuzioni asimmetriche
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Il target delle statistiche predefinito è 100, il che significa che PostgreSQL campiona 300 * 100 = 30.000 righe. Per colonne con molti valori distinti o distribuzioni asimmetriche, aumentarlo a 500 o 1000 fornisce al planner dati migliori al costo di tempi di ANALYZE leggermente più lunghi.

Regolazione dell'Autovacuum#

L'autovacuum fa due cose: recupera spazio dalle tuple morte (righe cancellate o aggiornate) e aggiorna le statistiche. Sulle tabelle con molte operazioni, le impostazioni predefinite dell'autovacuum sono spesso troppo conservative.

-- Controlla lo stato dell'autovacuum
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Se vedi tabelle con milioni di tuple morte e l'ultimo vacuum è stato ore fa, il tuo autovacuum è in ritardo.

Per tabelle ad alto tasso di aggiornamento (come sessioni, code di lavoro o metriche), imposto le impostazioni dell'autovacuum per tabella:

-- Autovacuum più aggressivo per tabelle con molte scritture
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum all'1% di tuple morte (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze allo 0,5% di modifiche (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- vacuum più veloce (default 2ms nei PG più recenti)
);

Bloat delle Tabelle#

Quando PostgreSQL aggiorna una riga, non la modifica sul posto — crea una nuova versione e segna la vecchia come morta. Il vacuum recupera le righe morte, ma lo spazio viene riutilizzato solo da quella tabella. Il file della tabella su disco non si riduce.

Nel tempo, una tabella pesantemente aggiornata può avere un bloat significativo — la tabella è molto più grande su disco di quanto richiesto dai dati vivi. Questo significa più pagine da scansionare, più I/O, più pressione sulla cache.

-- Stima il bloat della tabella (versione semplificata)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Per tabelle con bloat grave, l'opzione nucleare è VACUUM FULL — riscrive l'intera tabella. Ma prende un lock esclusivo, quindi non puoi farlo su una tabella di produzione live senza downtime. L'approccio migliore è pg_repack, che fa la stessa cosa senza lock:

pg_repack --table orders --no-kill-backend -d mydb

Connection Pooling#

Ecco qualcosa che sorprende molti sviluppatori: le connessioni PostgreSQL sono costose. Ogni connessione genera un nuovo processo (non un thread), consuma circa 5-10 MB di memoria e ha un overhead di fork non trascurabile.

Il max_connections predefinito è 100. Se hai un application server con 20 worker, ognuno che apre 5 connessioni, sei già al limite. Aggiungi un processore di job in background, uno strumento di monitoraggio e una migrazione in esecuzione da qualche parte, e sei nei guai.

Perché Hai Bisogno di un Connection Pooler#

Senza un pooler, se la tua applicazione deve gestire 500 richieste concorrenti, hai bisogno di 500 connessioni PostgreSQL. Sono 5 GB di memoria solo per l'overhead delle connessioni, e le prestazioni di PostgreSQL degradano significativamente oltre qualche centinaio di connessioni a causa dell'overhead di gestione dei processi.

Con PgBouncer davanti a PostgreSQL, quelle 500 connessioni dell'applicazione vengono mappate su forse 20 connessioni PostgreSQL effettive. Il pooler mette in coda le richieste quando tutte le connessioni al database sono occupate.

Configurazione di PgBouncer#

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Modalità pool
pool_mode = transaction
 
; Dimensionamento del pool
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeout
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Modalità Transaction vs Session#

• Modalità transaction (pool_mode = transaction): Le connessioni vengono restituite al pool dopo ogni transazione. Questo è quello che vuoi il 95% delle volte. Massimo riutilizzo delle connessioni.
• Modalità session (pool_mode = session): Le connessioni sono mantenute per l'intera sessione del client. Usala se hai bisogno di prepared statement, comandi SET, LISTEN/NOTIFY o altre funzionalità a livello di sessione.
• Modalità statement (pool_mode = statement): Le connessioni vengono restituite dopo ogni istruzione. Troppo restrittiva per la maggior parte delle applicazioni — non puoi nemmeno usare transazioni esplicite.

Il tranello della modalità transaction: non puoi usare prepared statement (sono stato a livello di sessione), non puoi usare SET per variabili di sessione, e LISTEN/NOTIFY non funzionerà come previsto. La maggior parte degli ORM ha un modo per disabilitare i prepared statement.

Per Node.js con il driver pg:

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // Porta PgBouncer, non la porta PostgreSQL
  database: 'mydb',
  max: 20,     // corrisponde al default_pool_size di PgBouncer
  statement_timeout: 30000,
  // Disabilita i prepared statement per la modalità transaction di PgBouncer
  prepare: false,
});

Formula per il Dimensionamento del Pool#

C'è una formula comune per la dimensione ottimale del pool di connessioni PostgreSQL:

connessioni_ottimali = (numero_core * 2) + numero_effettivo_spindle

Per un server moderno con 4 core e un SSD (1 spindle effettivo):

ottimale = (4 * 2) + 1 = 9

Questo sembra controintuitivamente basso. Ma PostgreSQL è CPU-bound sulla maggior parte dell'hardware moderno (gli SSD sono abbastanza veloci da non creare raramente un collo di bottiglia I/O). Più connessioni di queste portano a un overhead di context switching che effettivamente rallenta le cose.

In pratica, di solito imposto default_pool_size a 2-3x questo numero per gestire il traffico a raffica, con la consapevolezza che al picco di carico, alcune query aspetteranno nella coda di PgBouncer piuttosto che colpire tutte PostgreSQL contemporaneamente.

Checklist Pratica: I Passi Esatti per Ogni Query Lenta#

Ecco il mio processo effettivo quando ricevo una segnalazione "questa query è lenta". Seguo questi passi in questo ordine, ogni volta.

Passo 1: Ottieni la Query Effettiva#

Non "l'endpoint è lento" — l'SQL effettivo. Se stai usando un ORM, abilita il logging delle query:

-- Logga temporaneamente tutte le query oltre i 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Oppure controlla pg_stat_statements per le query con il maggior tempo totale:

-- Top 10 query per tempo di esecuzione totale
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements è la singola estensione più preziosa per le prestazioni di PostgreSQL. Se non la stai usando, installala ora:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Passo 2: Esegui EXPLAIN (ANALYZE, BUFFERS)#

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <la query lenta>;

Cerca:

1. Stime delle righe errate — righe effettive molto diverse dalle righe stimate
2. Seq Scan su tabelle grandi — possibile indice mancante
3. Nested Loop con molte righe — dovrebbe essere un Hash Join o Merge Join
4. Molte letture dal buffer — cache fredda o tabella troppo grande
5. Operazioni di ordinamento che finiscono su disco — aumenta work_mem o aggiungi un indice per l'ordinamento

Passo 3: Controlla le Statistiche della Tabella#

-- Quando sono state aggiornate le statistiche l'ultima volta?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Se last_analyze è vecchio o n_dead_tup è alto rispetto a n_live_tup, esegui:

ANALYZE orders;

Poi riesegui EXPLAIN ANALYZE. Se il piano cambia, le statistiche obsolete erano il problema.

Passo 4: Controlla gli Indici Esistenti#

-- Quali indici esistono su questa tabella?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Forse l'indice esiste ma non viene usato. Forse l'ordine delle colonne è sbagliato per la tua query.

Passo 5: Crea o Modifica gli Indici#

Basandoti sul piano della query, crea l'indice appropriato. Testa con EXPLAIN ANALYZE prima e dopo.

-- Crea l'indice in modo concorrente (non blocca la tabella)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verifica che l'indice sia usato
EXPLAIN (ANALYZE, BUFFERS) <la query lenta>;

Usa sempre CONCURRENTLY sulle tabelle di produzione. Un normale CREATE INDEX prende un lock completo sulla tabella che blocca tutte le scritture.

Passo 6: Considera la Riscrittura delle Query#

Se l'indice esiste e le statistiche sono aggiornate ma la query è ancora lenta, guarda la query stessa:

• Una subquery può essere riscritta come JOIN?
• OFFSET sta causando problemi? Passa alla paginazione keyset
• Stai selezionando più colonne del necessario?
• Una subquery correlata può diventare una funzione window?
• Una CTE sta impedendo al planner di ottimizzare?

Passo 7: Controlla le Impostazioni del Server#

Per specifici pattern di query, le impostazioni del server contano:

-- work_mem: memoria per ordinamenti e hash join (per operazione, non per connessione)
-- Il default è 4MB, che è troppo basso per query complesse
SET work_mem = '64MB';  -- prova questo e riesegui EXPLAIN ANALYZE
 
-- effective_cache_size: dice al planner quanta cache del disco aspettarsi
-- Imposta a ~75% della RAM totale
SHOW effective_cache_size;
 
-- random_page_cost: rapporto tra I/O casuale e I/O sequenziale
-- Il default è 4.0, imposta a 1.1-1.5 per SSD
SHOW random_page_cost;

Se cambiare work_mem risolve un problema di ordinamento su disco, considera di aumentarlo globalmente. Ma fai attenzione — è per operazione, non per connessione. Una query complessa con 10 operazioni di ordinamento e work_mem = 256MB potrebbe usare 2,5 GB per una singola query.

Passo 8: Monitora Dopo la Correzione#

Non limitarti a correggere e dimenticare. Verifica che la correzione regga nel tempo:

-- Resetta pg_stat_statements per vedere dati freschi dopo le tue modifiche
SELECT pg_stat_statements_reset();
 
-- Controlla dopo un'ora/un giorno
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Bonus: Quick Win che Applico a Ogni Nuovo Database#

Queste sono impostazioni e pratiche che applico a ogni database PostgreSQL che configuro, prima che sorgano problemi di prestazioni:

-- 1. Installa pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Abilita il logging delle query lente (50ms è la mia soglia)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. Impostazioni appropriate per SSD
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Aumenta work_mem dal default anemico
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Imposta effective_cache_size al 75% della RAM
-- Per un server da 16GB:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Imposta shared_buffers al 25% della RAM
-- Per un server da 16GB:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Migliora la reattività dell'autovacuum
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Applica le modifiche
SELECT pg_reload_conf();
-- Nota: shared_buffers richiede un riavvio

E una query di monitoraggio che eseguo settimanalmente:

-- Tabelle che richiedono attenzione
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Considerazioni Finali#

L'ottimizzazione delle query PostgreSQL non è un'arte oscura. È un processo sistematico:

1. Misura — non tirare a indovinare. EXPLAIN (ANALYZE, BUFFERS) è il tuo miglior amico.
2. Comprendi il piano — impara a leggere i tipi di scansione, i tipi di join e le stime delle righe.
3. Indicizza strategicamente — l'indice giusto sulle colonne giuste, nell'ordine giusto. Gli indici parziali e gli indici covering sono superpoteri sottoutilizzati.
4. Scrivi query migliori — EXISTS invece di IN per i controlli di esistenza, paginazione keyset invece di OFFSET, funzioni window invece di subquery correlate.
5. Mantieni il database — regolazione dell'autovacuum, aggiornamento delle statistiche, connection pooling.
6. Monitora continuamente — pg_stat_statements ti dice dove il tuo database spende il suo tempo. Controllalo regolarmente.

La differenza tra una query che impiega 4 secondi e una che impiega 0,3 millisecondi è raramente l'hardware. È quasi sempre la conoscenza — sapere dove guardare e cosa modificare. E ora sai dove guardare.