Guida SQLite interattiva: EXPLAIN QUERY PLAN

Q: Che differenza c'è tra SCAN e SEARCH nell'output?

SCAN significa che SQLite legge tutte le righe di una tabella o di un indice: va bene per le tabelle piccole, costa caro su quelle grandi. SEARCH significa che salta direttamente alle righe corrispondenti usando un indice o la chiave primaria. Su una tabella grande vuoi quasi sempre vedere SEARCH sulle colonne che usi per filtrare.

Q: Come verifico se la mia query usa un indice?

Esegui EXPLAIN QUERY PLAN sulla query e cerca nell'output USING INDEX o USING COVERING INDEX . Se vedi solo SCAN senza nessun indice citato, la query sta facendo una scansione completa della tabella e con ogni probabilità un indice aiuterebbe.

Q: Che differenza c'è tra EXPLAIN ed EXPLAIN QUERY PLAN?

EXPLAIN mostra il bytecode di basso livello generato dalla macchina virtuale di SQLite: utile per studiare il motore, raramente utile per ottimizzare le query. EXPLAIN QUERY PLAN mostra un riepilogo leggibile dell'accesso alle tabelle e dell'uso degli indici. Per il lavoro sulle prestazioni ti serve quasi sempre EXPLAIN QUERY PLAN.

EXPLAIN QUERY PLAN ti dice come verrà eseguita una query

Prima di ottimizzare una query lenta devi sapere cosa sta facendo davvero SQLite. EXPLAIN QUERY PLAN stampa un breve riepilogo della strategia scelta dal planner: quali tabelle tocca, in che ordine e quali indici usa (se ne usa). La query in sé non viene eseguita: ottieni solo il piano.

Metti queste parole chiave davanti a qualsiasi istruzione:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

L'output è più o meno questo:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Quella sola riga dice molto: SQLite sta facendo una SEARCH (non una scansione) sulla tabella users, usando l'indice univoco creato automaticamente per email, con email come chiave di ricerca. Esattamente quello che speravi.

SCAN e SEARCH: la prima cosa da leggere

Ogni riga del piano comincia con SCAN oppure con SEARCH. Questa distinzione è il segnale più importante di tutto l'output.

SCAN <table>: SQLite legge tutte le righe della tabella (o tutte le voci di un indice). Il costo cresce con la dimensione della tabella.
SEARCH <table> USING ...: SQLite salta direttamente alle righe corrispondenti tramite un indice o la chiave primaria. Il costo cresce con la dimensione del risultato, non della tabella.

Ecco un confronto diretto. Una colonna ha un indice, l'altra no:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Il primo piano riporta SEARCH orders USING INDEX idx_orders_customer. Il secondo riporta SCAN orders: non c'è un indice su status, quindi SQLite legge ogni riga. Su una tabella piccola non te ne accorgi; su una tabella da un milione di righe è la differenza tra millisecondi e secondi.

Uno SCAN non è sempre sbagliato. Per le piccole tabelle di lookup, o per le query che restituiscono davvero la maggior parte delle righe, scansionare è il piano giusto. Ma su una tabella grande con un filtro selettivo, SCAN è il segnale che serve un indice.

Verificare che un indice venga usato

La frase da cercare è USING INDEX <name> (oppure USING COVERING INDEX <name>, ne parliamo più sotto). Se hai creato un indice sperando che il planner lo usasse, ecco come controllarlo:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Dovresti vedere SEARCH events USING INDEX idx_events_user (user_id=?). Se invece il piano dice SCAN events, qualcosa impedisce al planner di usare l'indice. Le cause più comuni sono avvolgere la colonna in una funzione (WHERE lower(user_id) = ...), confrontare tipi diversi o usare LIKE '%foo%' con un carattere jolly iniziale.

Una prova veloce:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

Quel + 0 rende inutilizzabile l'indice: il piano torna a SCAN events. Qualsiasi espressione sulla colonna indicizzata ha lo stesso effetto.

Gli indici di copertura appaiono in modo diverso

Quando un indice contiene tutte le colonne di cui la query ha bisogno, SQLite può rispondere usando solo l'indice, senza toccare la tabella. Il piano riporta USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

Il piano: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). La query chiede price, l'indice contiene già sku e price, quindi SQLite non legge mai la tabella sottostante. Un indice di copertura (covering index) è il piano più veloce che puoi ottenere per una ricerca: vale la pena tenerlo presente quando scegli quali colonne indicizzare insieme.

Leggere il piano di una join

È con le join che i piani diventano interessanti. Ogni riga del piano corrisponde a una tabella della join, e l'ordine delle righe è l'ordine in cui SQLite le visita. La prima tabella è quella esterna (outer); le tabelle successive vengono consultate una volta per ogni riga di quella esterna.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Un piano tipico:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Leggilo dall'alto in basso: SQLite trova il cliente tramite la chiave primaria, poi per quel cliente cerca gli ordini corrispondenti con l'indice su customer_id. Entrambe le righe sono SEARCH, niente scansioni complete: è quello che vuoi.

Se invece nella seconda riga vedessi SCAN o, ogni ricerca di un cliente scatenerebbe un passaggio completo su orders. Su una tabella grande è un disastro. La soluzione è quasi sempre un indice sulla colonna della join.

Query composte e subquery

I piani per UNION, EXCEPT e le subquery sono annidati. Ogni ramo compare indentato sotto il suo genitore:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

Vedrai due righe figlie sotto l'intestazione COMPOUND QUERY, una per ramo. Le subquery e le CTE funzionano in modo simile: ognuna ha il suo nodo del piano indentato, e le leggi tutte con lo stesso criterio SCAN contro SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

La subquery diventa un nodo del piano separato ("LIST SUBQUERY" o simile), con la sua strategia di accesso. Applica gli stessi controlli a ogni livello.

EXPLAIN ed EXPLAIN QUERY PLAN

Sono due cose diverse, e spesso vengono confuse.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (senza QUERY PLAN) stampa il bytecode che la macchina virtuale di SQLite eseguirà: decine di opcode di basso livello come OpenRead, SeekRowid, Column, ResultRow. Utile se stai facendo il debug del motore stesso. Quasi mai utile per ottimizzare.

EXPLAIN QUERY PLAN è il riepilogo leggibile che ti serve davvero. Nel dubbio, usa sempre EXPLAIN QUERY PLAN.

Un metodo per le query lente

Quando una query è lenta, il ciclo di lavoro è questo:

Esegui EXPLAIN QUERY PLAN sulla query.
Per ogni riga di tabella chiediti: è SCAN o SEARCH? Su una tabella grande, il sospettato è SCAN.
Se uno SCAN filtra su una colonna, valuta un indice su quella colonna.
Per le join, verifica che le tabelle del ciclo interno usino SEARCH USING INDEX sulla colonna della join.
Esegui di nuovo EXPLAIN QUERY PLAN dopo aver aggiunto l'indice. Il piano dovrebbe cambiare. Se non cambia, il planner ha deciso che il tuo indice non valeva la pena, di solito perché la tabella è piccola o il filtro non è abbastanza selettivo.

Un esempio concreto del passo 5:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

Il piano è passato da SCAN a SEARCH. È il segnale che l'indice sta facendo il suo lavoro. (Su una tabella appena creata e quasi vuota il planner potrebbe comunque scansionare, perché non ci sono abbastanza dati per giustificare l'indice: riempi la tabella o esegui ANALYZE e spesso la scelta cambia.)

Cosa il piano non ti dice

EXPLAIN QUERY PLAN descrive la strategia, non il costo. Non ti dirà che la query ha impiegato 800 ms o ha restituito 50.000 righe. Per quello ti servono i tempi (.timer on nella CLI) e il conteggio delle righe. Piano e tempi si completano: il piano ti dice perché una query è lenta, il timer ti dice se lo è.

Altri due limiti da conoscere:

Il piano può cambiare man mano che i dati crescono. Una query che scansionava tranquillamente una tabella da 100 righe avrà bisogno di un indice quando la tabella arriverà a un milione di righe. Ricontrolla i piani su dati di dimensioni reali, non sulle fixture di sviluppo.
Il planner usa le statistiche raccolte da ANALYZE. Senza di esse ricorre a valori predefiniti che non sono sempre ottimi. Statistiche vecchie o assenti sono una causa frequente di piani sorprendenti.

Prossimo passo: ANALYZE e VACUUM

Il query planner prende le sue decisioni in base alle statistiche su tabelle e indici. Se quelle statistiche mancano o sono vecchie, anche uno schema indicizzato alla perfezione può produrre un piano pessimo. ANALYZE serve a tenerle aggiornate, e VACUUM è il comando che lo accompagna per recuperare spazio e deframmentare il file del database. È il prossimo argomento.

Domande frequenti

A cosa serve EXPLAIN QUERY PLAN in SQLite?

Chiede a SQLite di descrivere come eseguirebbe una query, senza eseguirla davvero. L'output mostra quali tabelle vengono lette, quali indici vengono usati e in che ordine vengono risolte le join. Basta mettere EXPLAIN QUERY PLAN davanti a qualsiasi SELECT, INSERT, UPDATE o DELETE per vedere il piano.

Che differenza c'è tra SCAN e SEARCH nell'output?

SCAN significa che SQLite legge tutte le righe di una tabella o di un indice: va bene per le tabelle piccole, costa caro su quelle grandi. SEARCH significa che salta direttamente alle righe corrispondenti usando un indice o la chiave primaria. Su una tabella grande vuoi quasi sempre vedere SEARCH sulle colonne che usi per filtrare.

Come verifico se la mia query usa un indice?

Esegui EXPLAIN QUERY PLAN sulla query e cerca nell'output USING INDEX <name> o USING COVERING INDEX <name>. Se vedi solo SCAN <table> senza nessun indice citato, la query sta facendo una scansione completa della tabella e con ogni probabilità un indice aiuterebbe.

Che differenza c'è tra EXPLAIN ed EXPLAIN QUERY PLAN?

EXPLAIN mostra il bytecode di basso livello generato dalla macchina virtuale di SQLite: utile per studiare il motore, raramente utile per ottimizzare le query. EXPLAIN QUERY PLAN mostra un riepilogo leggibile dell'accesso alle tabelle e dell'uso degli indici. Per il lavoro sulle prestazioni ti serve quasi sempre EXPLAIN QUERY PLAN.

Concetti correlati