EXPLAIN QUERY PLAN no SQLite: como ler e otimizar

EXPLAIN QUERY PLAN mostra como sua consulta vai rodar

Antes de sair otimizando uma query lenta, você precisa entender o que o SQLite está realmente fazendo por baixo dos panos. O EXPLAIN QUERY PLAN exibe um resumo curto da estratégia que o planejador escolheu: quais tabelas serão acessadas, em que ordem e quais índices (se houver) vão entrar em ação. A consulta em si não chega a ser executada — você recebe apenas o plano de execução.

Basta colocar essas palavras-chave na frente de qualquer comando:

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

O resultado fica mais ou menos assim:

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

Essa única linha já diz muita coisa: o SQLite está fazendo um SEARCH (e não um scan) na tabela users, usando o índice único criado automaticamente para email, com email como chave de busca. Exatamente o que a gente espera ver.

SCAN vs SEARCH no SQLite: o primeiro sinal a observar

Toda linha do plano começa com SCAN ou SEARCH. Essa diferença é o sinal mais importante de toda a saída do EXPLAIN QUERY PLAN.

• SCAN <tabela> — o SQLite percorre todas as linhas da tabela (ou todas as entradas de um índice). O custo cresce junto com o tamanho da tabela.
• SEARCH <tabela> USING ... — o SQLite vai direto às linhas que interessam, usando um índice ou a chave primária. O custo cresce com o tamanho do resultado, não da tabela.

Veja a comparação lado a lado. Uma coluna tem índice, a outra não:

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

O primeiro plano mostra SEARCH orders USING INDEX idx_orders_customer. Já o segundo traz SCAN orders — como não existe índice em status, o SQLite acaba lendo linha por linha. Numa tabela pequena isso passa batido; numa tabela com um milhão de registros, é a diferença entre milissegundos e segundos.

Mas atenção: nem todo SCAN é problema. Em tabelas de apoio bem pequenas, ou em consultas que de fato precisam retornar a maior parte das linhas, varrer tudo é o plano certo. O sinal de alerta aparece quando você tem uma tabela grande com um filtro seletivo — aí sim, SCAN é o aviso de que falta um índice.

Como confirmar se o índice está sendo usado no SQLite

A expressão que você quer encontrar no plano é USING INDEX <nome> (ou USING COVERING INDEX <nome>, que veremos daqui a pouco). Se você criou um índice na esperança de que o planejador fosse aproveitá-lo, é assim que se confere:

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;

Você deve ver algo como SEARCH events USING INDEX idx_events_user (user_id=?). Se em vez disso aparecer SCAN events, é porque alguma coisa está impedindo o planejador de usar o índice — os culpados mais comuns são envolver a coluna em uma função (WHERE lower(user_id) = ...), comparar tipos diferentes ou usar LIKE '%foo%' com curinga no início.

Um teste rápido disso:

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;

Esse + 0 mata o índice — o plano volta para SCAN events. Qualquer expressão aplicada sobre a coluna indexada provoca o mesmo efeito.

Índices de cobertura aparecem de outro jeito

Quando o índice já contém todas as colunas que a consulta precisa, o SQLite consegue responder direto pelo índice, sem nem encostar na tabela. Nesses casos, o plano mostra 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';

O plano: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). A query pede price, e o índice já guarda sku e price, então o SQLite nem chega a tocar na tabela original. Índice coberto (covering index) é o plano mais rápido que você consegue numa busca pontual — vale ter isso em mente na hora de decidir quais colunas indexar juntas.

Como ler o plano de execução de JOINs no SQLite

É nos JOINs que o plano fica realmente interessante. Cada linha do plano representa uma tabela do JOIN, e a ordem das linhas é a ordem em que o SQLite percorre as tabelas. A primeira tabela é a externa (outer); as seguintes são consultadas uma vez para cada linha dessa tabela 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;

Um plano típico tem essa cara:

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

Leia de cima para baixo: o SQLite localiza o cliente pela chave primária e, para esse cliente, busca os pedidos correspondentes via índice em customer_id. As duas linhas são SEARCH — sem full scan — que é exatamente o que queremos.

Se na segunda linha aparecesse SCAN o, cada busca de cliente dispararia uma varredura completa em orders. Numa tabela grande, isso é desastroso. A solução, quase sempre, é criar um índice na coluna do join.

Consultas compostas e subconsultas

Planos de UNION, EXCEPT e subconsultas são aninhados. Cada ramo aparece indentado sob seu pai:

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

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

Você verá duas linhas filhas sob o título COMPOUND QUERY, uma para cada ramo. Subqueries e CTEs seguem a mesma lógica — cada uma ganha seu próprio nó de plano indentado, e você lê todos com a mesma ótica 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);

A subconsulta vira um nó separado no plano (algo como "LIST SUBQUERY"), com sua própria estratégia de acesso. Vale aplicar a mesma análise em cada nível.

EXPLAIN vs EXPLAIN QUERY PLAN

São duas coisas diferentes, e tem muita gente que confunde.

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

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (sem o QUERY PLAN) despeja o bytecode que a máquina virtual do SQLite vai executar — dezenas de opcodes de baixo nível como OpenRead, SeekRowid, Column, ResultRow. Útil se você estiver depurando a própria engine. Quase nunca útil para tunar consulta.

Já o EXPLAIN QUERY PLAN é o resumo legível que você realmente quer ver. Na dúvida, vá direto de EXPLAIN QUERY PLAN.

Um fluxo para consultas lentas no SQLite

Quando uma query está lenta, o ciclo de trabalho é mais ou menos assim:

1. Rode EXPLAIN QUERY PLAN em cima dela.
2. Para cada linha de tabela, pergunte: é SCAN ou SEARCH? Em tabela grande, SCAN é o principal suspeito.
3. Se um SCAN estiver filtrando por alguma coluna, pense em criar um índice nessa coluna.
4. Em joins, confirme que as tabelas do laço interno usam SEARCH USING INDEX na coluna de junção.
5. Rode o EXPLAIN QUERY PLAN de novo depois de criar o índice. O plano deve mudar. Se não mudou, o planejador decidiu que seu índice não valia a pena — normalmente porque a tabela é pequena ou o filtro não é seletivo o bastante.

Veja um exemplo prático do 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=?)

O plano deixou de ser SCAN e virou SEARCH. Esse é o sinal de que o índice está cumprindo o papel dele. (Numa tabela recém-criada e quase vazia, o planejador ainda pode optar por scan porque não há dados suficientes para valer a pena usar o índice — popule a tabela ou rode ANALYZE que, na maioria das vezes, a escolha muda.)

O que o plano de execução não te conta

O EXPLAIN QUERY PLAN mostra a estratégia, não o custo. Ele não vai te dizer que a consulta levou 800 ms nem que retornou 50.000 linhas. Para isso, você precisa de tempo de execução (.timer on no CLI) e contagem de linhas. Plano e tempo se completam — o plano te diz por que a consulta está lenta, o timer te diz se ela está.

Mais duas limitações que vale ter em mente:

• O plano pode mudar conforme os dados crescem. Uma query que fazia scan tranquilo numa tabela de 100 linhas vai precisar de índice quando ela chegar a um milhão. Revise os planos com dados em volume de produção, não com as fixtures do seu ambiente de dev.
• O planejador usa estatísticas coletadas pelo ANALYZE. Sem elas, ele cai em valores padrão que nem sempre são bons. Estatísticas desatualizadas ou ausentes são uma causa comum de planos surpreendentes.

A seguir: ANALYZE e VACUUM

O query planner decide com base nas estatísticas das suas tabelas e índices. Se essas estatísticas estiverem ausentes ou velhas, até um schema com índices perfeitos pode gerar um plano ruim. O ANALYZE é o comando que mantém isso em dia — e o VACUUM é o complemento, usado para liberar espaço e desfragmentar o arquivo do banco. É o que vem na sequência.