Docs ejecutables de SQLite: EXPLAIN QUERY PLAN

Q: ¿Qué diferencia hay entre SCAN y SEARCH en la salida?

SCAN significa que SQLite lee todas las filas de una tabla o índice — algo asumible en tablas pequeñas, pero caro en tablas grandes. SEARCH indica que salta directamente a las filas que coinciden mediante un índice o la clave primaria. En tablas grandes casi siempre quieres ver SEARCH sobre las columnas por las que filtras.

Q: ¿Cómo compruebo si mi consulta está usando un índice?

Lanza EXPLAIN QUERY PLAN sobre la consulta y busca en la salida USING INDEX o USING COVERING INDEX . Si solo aparece SCAN sin mención a ningún índice, la consulta está haciendo un escaneo completo y muy probablemente te convenga crear un índice.

Q: ¿En qué se diferencian EXPLAIN y EXPLAIN QUERY PLAN?

EXPLAIN te muestra el bytecode de bajo nivel que genera la máquina virtual de SQLite — útil para entender el motor por dentro, pero rara vez práctico para optimizar consultas. EXPLAIN QUERY PLAN ofrece un resumen legible del acceso a tablas y del uso de índices. Para trabajo de rendimiento, casi siempre quieres EXPLAIN QUERY PLAN.

EXPLAIN QUERY PLAN te dice cómo se va a ejecutar una consulta

Antes de optimizar una consulta lenta en SQLite, necesitas saber qué está haciendo realmente el motor por dentro. EXPLAIN QUERY PLAN te muestra un resumen breve de la estrategia que eligió el planificador: qué tablas toca, en qué orden y qué índices usa (si es que usa alguno). La consulta no llega a ejecutarse; solo obtienes el plan de ejecución.

Basta con anteponer estas palabras clave a cualquier sentencia:

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';

El resultado se ve más o menos así:

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

Esa única línea ya te dice mucho: SQLite está haciendo un SEARCH (no un scan) sobre la tabla users, usando el índice único que se crea automáticamente para email, con email como clave de búsqueda. Justo lo que querrías ver.

SCAN vs SEARCH: lo primero que hay que mirar

Cada línea del plan de ejecución empieza por SCAN o por SEARCH. Esa diferencia es la señal más importante de toda la salida.

SCAN <tabla> — SQLite recorre todas las filas de la tabla (o todas las entradas de un índice). El coste crece con el tamaño de la tabla. Esto es lo que se conoce como un full table scan.
SEARCH <tabla> USING ... — SQLite salta directamente a las filas que coinciden, apoyándose en un índice o en la clave primaria. El coste crece con el tamaño del resultado, no con el de la tabla.

Vamos a verlo en paralelo. Una columna tiene índice y la otra 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';

El primer plan muestra SEARCH orders USING INDEX idx_orders_customer. El segundo muestra SCAN orders: como no hay índice sobre status, SQLite recorre todas las filas. En una tabla pequeña ni te enteras; en una de un millón de filas, es la diferencia entre milisegundos y segundos.

Que aparezca un SCAN no siempre es malo. Para tablas pequeñas de consulta, o cuando la query devuelve de verdad la mayoría de las filas, escanear es el plan correcto. Pero en una tabla grande con un filtro selectivo, ver un SCAN es la señal clara para añadir un índice.

Cómo comprobar si una consulta usa índice en SQLite

Lo que tienes que buscar es la frase USING INDEX <name> (o USING COVERING INDEX <name>, que veremos en un momento). Si creaste un índice esperando que el planificador lo aprovechara, así es como lo confirmas:

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;

Deberías ver SEARCH events USING INDEX idx_events_user (user_id=?). Si en cambio el plan dice SCAN events, algo está impidiendo que el planificador use el índice. Las causas más habituales: envolver la columna en una función (WHERE lower(user_id) = ...), comparar tipos distintos o usar LIKE '%foo%' con un comodín al principio.

Una prueba rápida de esto:

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;

El + 0 rompe el uso del índice: el plan vuelve a SCAN events. Cualquier expresión aplicada sobre la columna indexada provoca lo mismo.

Cómo se ven los índices cubrientes en el plan

Cuando un índice contiene todas las columnas que necesita la consulta, SQLite puede resolverla directamente desde el índice, sin tocar la tabla. En ese caso el plan muestra 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';

El plan: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). La consulta pide price y el índice ya guarda sku y price, así que SQLite nunca llega a tocar la tabla original. Un índice cubridor (covering index) es el plan más rápido al que puedes aspirar en una búsqueda — vale la pena tenerlo presente cuando decides qué columnas conviene indexar juntas.

Cómo leer el plan de ejecución de un JOIN en SQLite

Los JOIN son donde los planes se ponen interesantes. Cada línea del plan se corresponde con una tabla del JOIN, y el orden de las líneas es el orden en que SQLite las visita. La primera tabla es la externa (outer); las siguientes se consultan una vez por cada fila de la externa.

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 plan típico se ve así:

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

Léelo de arriba hacia abajo: SQLite localiza al cliente por su clave primaria y, a partir de ahí, busca los pedidos que le corresponden usando el índice sobre customer_id. Las dos líneas marcan SEARCH (no hay escaneos completos), que es justo lo que buscamos.

Si en la segunda línea apareciera SCAN o, cada búsqueda de cliente dispararía un recorrido completo de orders. En una tabla grande, eso es un desastre. La solución casi siempre pasa por crear un índice sobre la columna del join.

Consultas compuestas y subconsultas

Los planes de ejecución para UNION, EXCEPT y subconsultas se anidan. Cada rama aparece indentada bajo su elemento padre:

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

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

Verás dos filas hijas bajo el encabezado COMPOUND QUERY, una por cada rama. Las subconsultas y los CTE funcionan igual: cada uno tiene su propio nodo del plan indentado, y los lees aplicando el mismo criterio de SCAN vs 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 subconsulta aparece como un nodo de plan aparte ("LIST SUBQUERY" o similar), con su propia estrategia de acceso. Aplica las mismas comprobaciones en cada nivel.

EXPLAIN vs EXPLAIN QUERY PLAN en SQLite

Son dos cosas distintas, y mucha gente las confunde.

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

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (sin el QUERY PLAN) te suelta el bytecode que la máquina virtual de SQLite va a ejecutar: decenas de opcodes de bajo nivel como OpenRead, SeekRowid, Column, ResultRow. Te sirve si estás depurando el motor en sí. Para optimizar consultas, casi nunca.

EXPLAIN QUERY PLAN es el resumen legible que realmente quieres ver. Ante la duda, tira siempre de EXPLAIN QUERY PLAN.

Flujo de trabajo para optimizar una consulta lenta en SQLite

Cuando una consulta va lenta, el ciclo es más o menos así:

Ejecútale un EXPLAIN QUERY PLAN.
Por cada línea de tabla, pregúntate: ¿esto es SCAN o SEARCH? En una tabla grande, el sospechoso siempre es el SCAN.
Si un SCAN está filtrando por alguna columna, plantéate crear un índice sobre esa columna.
En los joins, comprueba que las tablas del bucle interno usan SEARCH USING INDEX sobre la columna del join.
Vuelve a lanzar EXPLAIN QUERY PLAN después de añadir el índice. El plan debería cambiar. Si no cambió, es que el planificador decidió que tu índice no merecía la pena, normalmente porque la tabla es pequeña o el filtro no es lo bastante selectivo.

Un ejemplo concreto del paso 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=?)

El plan pasó de SCAN a SEARCH. Esa es la señal de que el índice está cumpliendo su función. (En una tabla recién creada y casi vacía, el planificador puede seguir haciendo scan porque no hay suficientes datos como para molestarse con el índice — llena la tabla o ejecuta ANALYZE y la decisión suele cambiar.)

Lo que el plan de ejecución no te va a decir

EXPLAIN QUERY PLAN describe la estrategia, no el coste. No te va a decir que la consulta tardó 800 ms ni que devolvió 50.000 filas. Para eso necesitas medir tiempos (.timer on en la CLI) y contar filas. El plan y los tiempos se complementan: el plan te dice por qué una consulta es lenta, y el cronómetro te dice si realmente lo es.

Dos limitaciones más que conviene tener en cuenta:

El plan puede cambiar a medida que crecen los datos. Una consulta que recorría tan ricamente una tabla de 100 filas va a necesitar un índice cuando la tabla llegue al millón. Revisa los planes con datos de tamaño real (producción), no con tus fixtures de desarrollo.
El planificador usa las estadísticas que recoge ANALYZE. Sin ellas, recurre a valores por defecto que no siempre son los mejores. Estadísticas desactualizadas o inexistentes son una causa habitual de planes sorprendentes.

Siguiente paso: ANALYZE y VACUUM

El planificador toma sus decisiones basándose en estadísticas sobre tus tablas e índices. Si esas estadísticas faltan o están desactualizadas, hasta un esquema perfectamente indexado puede acabar generando un mal plan. ANALYZE es la forma de mantenerlas al día, y VACUUM es su comando compañero para recuperar espacio y desfragmentar el archivo de la base de datos. Eso lo vemos a continuación.

Preguntas frecuentes

¿Para qué sirve EXPLAIN QUERY PLAN en SQLite?

Le pide a SQLite que describa cómo ejecutaría una consulta sin llegar a ejecutarla. La salida muestra qué tablas recorre, qué índices utiliza y en qué orden resuelve los joins. Basta con anteponer EXPLAIN QUERY PLAN a cualquier SELECT, INSERT, UPDATE o DELETE para ver el plan.

¿Qué diferencia hay entre SCAN y SEARCH en la salida?

SCAN significa que SQLite lee todas las filas de una tabla o índice — algo asumible en tablas pequeñas, pero caro en tablas grandes. SEARCH indica que salta directamente a las filas que coinciden mediante un índice o la clave primaria. En tablas grandes casi siempre quieres ver SEARCH sobre las columnas por las que filtras.

¿Cómo compruebo si mi consulta está usando un índice?

Lanza EXPLAIN QUERY PLAN sobre la consulta y busca en la salida USING INDEX <nombre> o USING COVERING INDEX <nombre>. Si solo aparece SCAN <tabla> sin mención a ningún índice, la consulta está haciendo un escaneo completo y muy probablemente te convenga crear un índice.

¿En qué se diferencian EXPLAIN y EXPLAIN QUERY PLAN?

EXPLAIN te muestra el bytecode de bajo nivel que genera la máquina virtual de SQLite — útil para entender el motor por dentro, pero rara vez práctico para optimizar consultas. EXPLAIN QUERY PLAN ofrece un resumen legible del acceso a tablas y del uso de índices. Para trabajo de rendimiento, casi siempre quieres EXPLAIN QUERY PLAN.

Conceptos relacionados

EXPLAIN QUERY PLAN en SQLite: leer el plan y detectar consultas lentas