SQL EXPLAIN QUERY PLAN

Quando você escreve um SELECT, você diz o que quer, não como achar. Quem decide o caminho é o banco: ler a tabela inteira, linha por linha, ou ir direto às linhas certas por um índice? Essa decisão se chama plano da consulta, e o comando EXPLAIN QUERY PLAN ("explique o plano da consulta") mostra qual foi a escolha. Nesta aula você vai aprender a ler esse plano, com as duas palavras que mais importam: SCAN e SEARCH.

Você vai aprender

O que é o plano de uma consulta, e por que o banco faz um para cada SELECT.
Pedir o plano com EXPLAIN QUERY PLAN, no editor e no terminal (com o .eqp on).
A diferença entre SCAN (ler tudo) e SEARCH (ir direto), e o que muda quando existe um índice.
O que querem dizer USING INTEGER PRIMARY KEY, USING INDEX, COVERING INDEX e USE TEMP B-TREE.
Ler o plano de um JOIN, de um GROUP BY e de uma subconsulta.
Que o EXPLAIN QUERY PLAN não roda a consulta, e o que acontece quando ela tem erro.

Os exemplos rodam de verdade no seu navegador, no SQLite 3.50 com o banco da Loja Aurora. As saídas de terminal vieram do sqlite3 3.50.4 no notebook da Ana. O plano depende da versão do SQLite: em outra versão, o texto pode mudar um pouco.

A ideia: o livro e o índice

A Ana quer achar no livro de receitas tudo sobre "cenoura". Ela tem dois caminhos:

Folhear o livro inteiro, página por página. Sempre funciona, mas, num livro de mil páginas, demora.
Ir ao índice do fim do livro, achar "cenoura: 12, 47, 80" e abrir só essas três páginas. Muito mais rápido, mas só dá se o livro tiver esse índice.

O banco faz a mesma escolha a cada consulta, e o EXPLAIN QUERY PLAN conta qual foi:

No plano	Quer dizer	No livro
`SCAN`	"varrer": ler a tabela inteira, linha por linha	folhear o livro todo
`SEARCH`	"procurar": ir direto a algumas linhas, por um índice ou pela chave	usar o índice do fim do livro

Numa tabela de 30 clientes, ler tudo é instantâneo, e tanto faz. Numa tabela de um milhão de linhas, a diferença entre os dois caminhos pode ser de segundos para milésimos (a aula Quando o Índice Ajuda mede isso). O plano é o primeiro lugar para olhar quando uma consulta está lenta.

1. O primeiro plano

Basta escrever EXPLAIN QUERY PLAN antes da consulta:

EXPLAIN QUERY PLAN
SELECT * FROM produtos WHERE id = 5;

EXPLAIN QUERY PLAN: "Explique o plano da consulta". Em vez de devolver os produtos, o SQLite devolve como ele os acharia.
SELECT * FROM produtos WHERE id = 5: A consulta de sempre. Ela não roda: o SQLite só monta o plano.

O resultado tem quatro colunas. Só a última interessa no dia a dia:

id: Um número que identifica o passo do plano.
parent: "Pai": o id do passo de que este faz parte. 0 quer dizer "está no nível de cima". Com ele, o plano vira uma árvore (seção 4).
notused: "Não usada": uso interno do SQLite. Ignore.
detail: "Detalhe": o passo, em texto. É aqui que se lê o plano.

O detalhe SEARCH produtos USING INTEGER PRIMARY KEY (rowid=?) se lê assim:

Parte	Quer dizer
`SEARCH produtos`	Vai direto a algumas linhas da tabela `produtos`, sem ler as outras.
`USING INTEGER PRIMARY KEY`	"Usando a chave primária inteira": o `id`, que é `INTEGER PRIMARY KEY` (aula PRIMARY KEY). No SQLite, a tabela já é guardada em ordem dessa chave, então achar o id 5 é como abrir o livro na página 5.
`(rowid=?)`	A condição usada para ir direto: `rowid` (o número da linha, que aqui é o próprio `id`) igual a um valor. O `?` está no lugar do 5: o plano é o mesmo para qualquer número.

2. SCAN: lendo a tabela inteira

Agora a mesma tabela, procurando pelo nome:

EXPLAIN QUERY PLAN
SELECT * FROM produtos WHERE nome = 'Estojo Duplo';

SCAN produtos: o SQLite vai ler os 45 produtos, um por um, e conferir o nome de cada. Não existe índice no nome, então ele não tem atalho. É o "folhear o livro inteiro".

Não é um erro: SCAN é o jeito normal de ler uma tabela pequena, ou de ler uma tabela inteira quando você quer mesmo todas as linhas (um SELECT sem WHERE sempre é SCAN). Ele só vira problema quando a tabela é grande e você quer poucas linhas dela.

Missão: a consulta abaixo busca os pedidos do cliente 3. Peça o plano dela, em vez do resultado, e veja se o SQLite lê a tabela inteira ou vai direto.

SELECT * FROM pedidos WHERE cliente_id = 3;

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 3;

Deu SCAN pedidos: para achar os pedidos de um cliente, o SQLite lê os 90 pedidos. A coluna cliente_id não tem índice. Isso muda na próxima seção.

3. SEARCH com um índice

Um índice (aula Índices (CREATE INDEX)) é o "índice do fim do livro" de uma coluna. Veja o plano da mesma consulta antes e depois de criar um índice na cidade dos clientes:

EXPLAIN QUERY PLAN
SELECT * FROM clientes WHERE cidade = 'Teresina';

CREATE INDEX idx_clientes_cidade ON clientes(cidade);

EXPLAIN QUERY PLAN
SELECT * FROM clientes WHERE cidade = 'Teresina';

Antes: SCAN clientes. Depois: SEARCH clientes USING INDEX idx_clientes_cidade (cidade=?).

Parte	Quer dizer
`SEARCH clientes`	Vai direto a algumas linhas.
`USING INDEX idx_clientes_cidade`	Pelo índice que você acabou de criar (o nome aparece no plano: dê nomes claros aos índices).
`(cidade=?)`	A parte do `WHERE` que o índice resolve: cidade igual a um valor.

Com o índice, o SQLite acha no índice as 10 entradas de "Teresina" e, para cada uma, busca a linha do cliente na tabela. São dois passos (índice, depois tabela), mas os dois vão direto.

Quando o índice basta: `COVERING INDEX`

Se a consulta só pede colunas que já estão no índice, o SQLite nem precisa abrir a tabela:

CREATE INDEX idx_clientes_cidade ON clientes(cidade);

EXPLAIN QUERY PLAN
SELECT cidade FROM clientes WHERE cidade = 'Teresina';

USING COVERING INDEX: "usando um índice que cobre" a consulta. O índice idx_clientes_cidade guarda a cidade, e a consulta só quer a cidade; tudo o que ela precisa está no índice. É o caminho mais rápido de todos: como responder "quantas receitas têm cenoura?" olhando só o índice do livro, sem abrir nenhuma página.

4. Planos com vários passos

Uma consulta maior gera um plano com várias linhas. O plano se lê de cima para baixo, e a coluna parent diz quem está dentro de quem.

Um `JOIN`: um laço dentro do outro

EXPLAIN QUERY PLAN
SELECT c.nome, COUNT(*) AS pedidos
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
GROUP BY c.nome;

SCAN p: lê todos os pedidos (a tabela aparece pelo apelido, p). Isso é esperado: a consulta conta todos os pedidos.
SEARCH c USING INTEGER PRIMARY KEY (rowid=?): para cada pedido, vai direto ao cliente dele pelo id. É um laço dentro do outro: 90 pedidos, 90 buscas diretas de cliente.
USE TEMP B-TREE FOR GROUP BY: "use uma árvore B temporária para o GROUP BY". Para agrupar por nome, o SQLite monta uma lista ordenada temporária (a "árvore B" é a estrutura que ele usa para guardar coisas em ordem). Dá trabalho extra, mas numa tabela pequena não pesa.

A ordem das tabelas no plano é a ordem que o SQLite escolheu, não necessariamente a que você escreveu. Ele começou pelos pedidos porque, para cada pedido, achar o cliente é direto (pela chave). O contrário (para cada cliente, achar os pedidos dele) exigiria ler os pedidos a cada cliente, porque cliente_id não tem índice.

Ordenar também custa: `TEMP B-TREE FOR ORDER BY`

EXPLAIN QUERY PLAN
SELECT * FROM clientes ORDER BY nome;

CREATE INDEX idx_clientes_nome ON clientes(nome);

EXPLAIN QUERY PLAN
SELECT * FROM clientes ORDER BY nome;

Sem índice: SCAN clientes e depois USE TEMP B-TREE FOR ORDER BY, ou seja, ler tudo e ordenar à parte. Com um índice no nome: SCAN clientes USING INDEX idx_clientes_nome. Ainda é SCAN (a consulta quer todos os clientes), mas lendo na ordem do índice, que já está em ordem alfabética. A ordenação temporária sumiu.

Uma subconsulta: os passos de dentro

EXPLAIN QUERY PLAN
SELECT * FROM produtos
WHERE id IN (SELECT produto_id FROM itens_pedido);

Repare na coluna parent: as duas últimas linhas têm como pai o passo LIST SUBQUERY 1 (a "lista da subconsulta 1"). Elas são os passos de dentro da subconsulta: ler os itens (pelo índice da chave primária composta de itens_pedido, que o SQLite criou sozinho com o nome sqlite_autoindex_itens_pedido_1) e montar um BLOOM FILTER, um filtro rápido para descartar logo os ids que não estão na lista. No terminal, essa árvore aparece desenhada, como você vai ver na seção 5.

Os detalhes que você vai encontrar

Detalhe	Quer dizer	Preocupa?
`SCAN t`	Lê a tabela inteira	Só se a tabela for grande e você quiser poucas linhas
`SCAN t USING INDEX i`	Lê tudo, na ordem do índice	Não: evita ordenar depois
`SEARCH t USING INTEGER PRIMARY KEY (rowid=?)`	Vai direto pela chave primária	Não: é o melhor caso
`SEARCH t USING INDEX i (col=?)`	Vai direto pelo índice `i`	Não
`SEARCH t USING COVERING INDEX i (col=?)`	Responde só com o índice, sem abrir a tabela	Não: é ainda melhor
`SEARCH t USING AUTOMATIC COVERING INDEX (col=?)`	O SQLite criou um índice temporário, só para esta consulta	Às vezes: talvez falte um índice de verdade nessa coluna
`USE TEMP B-TREE FOR ORDER BY` (ou `GROUP BY`, `DISTINCT`)	Ordena à parte, numa estrutura temporária	Em tabelas grandes, um índice na coluna pode evitar
`LIST SUBQUERY n`, `CO-ROUTINE`	Passos de uma subconsulta ou de uma CTE	Leia os passos de dentro
`BLOOM FILTER`	Um filtro rápido para descartar linhas	Não: é uma otimização
`MULTI-INDEX OR`	Um `OR` resolvido com um índice para cada lado	Não

Experimente: peça o plano de outras consultas: um LEFT JOIN de clientes com pedidos, um SELECT DISTINCT cidade FROM clientes, um WHERE id BETWEEN 10 AND 20. Crie um índice e peça o plano de novo.

EXPLAIN QUERY PLAN
SELECT c.nome, p.id
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id;

5. No terminal: a árvore e o `.eqp on`

No programa sqlite3, o mesmo comando mostra o plano desenhado como uma árvore, sem as colunas id e parent: a própria indentação mostra quem está dentro de quem.

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE cidade = 'Teresina';
QUERY PLAN
`--SCAN clientes
sqlite> EXPLAIN QUERY PLAN SELECT * FROM produtos WHERE id IN (SELECT produto_id FROM itens_pedido);
QUERY PLAN
|--SEARCH produtos USING INTEGER PRIMARY KEY (rowid=?)
`--LIST SUBQUERY 1
   |--SCAN itens_pedido USING COVERING INDEX sqlite_autoindex_itens_pedido_1
   `--CREATE BLOOM FILTER
sqlite> EXPLAIN QUERY PLAN SELECT c.nome, COUNT(*) FROM pedidos p JOIN clientes c ON c.id = p.cliente_id GROUP BY c.nome;
QUERY PLAN
|--SCAN p
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR GROUP BY

Os traços |-- e `-- são os galhos da árvore; o `-- marca o último galho de cada nível. Os dois passos recuados embaixo de LIST SUBQUERY 1 são os de dentro da subconsulta, os mesmos que, no editor, tinham parent apontando para ela.

O plano de toda consulta: `.eqp on`

Escrever EXPLAIN QUERY PLAN toda vez cansa. O comando de ponto .eqp on (aula Comandos de Ponto) faz o sqlite3 mostrar o plano e depois o resultado de cada consulta, até você digitar .eqp off:

sqlite> .eqp on
sqlite> SELECT nome FROM clientes WHERE cidade = 'Picos';
QUERY PLAN
`--SCAN clientes
Camila Duarte
sqlite> .eqp off

O .eqp é só do programa sqlite3: no editor do guia, comandos de ponto não existem.

6. `EXPLAIN` sem `QUERY PLAN`

Existe também o EXPLAIN sozinho. Ele mostra outra coisa: o programa que o SQLite monta para rodar a consulta, instrução por instrução, na "linguagem de máquina" interna dele (o bytecode):

EXPLAIN
SELECT * FROM produtos WHERE id = 5;

Dezesseis instruções para uma consulta de uma linha: OpenRead abre a tabela, SeekRowid pula direto para o id, Column lê cada coluna, ResultRow devolve a linha. Isso serve para quem estuda o funcionamento interno do SQLite. Para saber se a consulta usa um índice, o EXPLAIN QUERY PLAN responde muito mais fácil.

Formas diferentes de fazer a mesma coisa

Forma	Onde	Mostra	Quando usar
`EXPLAIN QUERY PLAN SELECT …`	Editor, terminal, programas	O plano (no editor, em tabela; no terminal, em árvore)	Sempre que quiser saber se a consulta usa índice
`.eqp on`	Só no terminal `sqlite3`	O plano e o resultado, a cada consulta	Numa sessão de testes, para acompanhar tudo
`EXPLAIN SELECT …`	Editor, terminal, programas	O bytecode, instrução por instrução	Quase nunca; curiosidade sobre o funcionamento interno

Quando dá errado

"Será que o EXPLAIN apagou tudo?"

A Ana queria ver o plano de um DELETE e ficou com medo de apagar os itens dos pedidos:

EXPLAIN QUERY PLAN
DELETE FROM itens_pedido;

SELECT COUNT(*) AS itens FROM itens_pedido;

O que acontece: nada foi apagado. Os 220 itens continuam lá. O EXPLAIN QUERY PLAN só planeja; a consulta não roda. Isso vale para SELECT, UPDATE e DELETE: você pode ver o plano de um comando perigoso sem medo. Mesmo assim, guarde o costume do WHERE (aula O Perigo do UPDATE e DELETE sem WHERE): o DELETE de verdade, sem EXPLAIN, apaga.

Nome errado: o plano também dá erro

EXPLAIN QUERY PLAN
SELECT * FROM clientes WHERE cidad = 'Teresina';

A mensagem: no such column: cidad, "não existe a coluna cidad". Para montar o plano, o SQLite precisa entender a consulta inteira, então um nome errado dá o mesmo erro que daria sem o EXPLAIN. No terminal, a mensagem vem com o prefixo e a seta:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE cidad = 'Teresina';
Parse error: no such column: cidad
  EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE cidad = 'Teresina';
                                    error here ---^

Como consertar: o nome certo da coluna é cidade. Antes de pensar no plano, a consulta tem de rodar.

Criou o índice e o plano continua SCAN

CREATE INDEX idx_clientes_cidade ON clientes(cidade);

EXPLAIN QUERY PLAN
SELECT * FROM clientes WHERE lower(cidade) = 'teresina';

O que acontece: sem erro, mas o índice foi ignorado: SCAN clientes. O índice guarda a cidade como ela está escrita ("Teresina"), e a consulta procura lower(cidade), um valor calculado que não está no índice. Para usar o índice, o SQLite teria de calcular lower() de todas as linhas, e aí já leu tudo.

Como consertar: compare a coluna pura: WHERE cidade = 'Teresina'. Esse e outros casos de "índice que não é usado" estão na aula Quando o Índice Ajuda.

O tutorial mostra SCAN TABLE

Em textos antigos, o plano aparece como SCAN TABLE produtos ou SEARCH TABLE produtos USING INDEX …. Não é erro seu: o formato do plano mudou ao longo das versões do SQLite, e a própria documentação avisa que ele pode mudar de novo. A ideia continua a mesma: SCAN lê tudo, SEARCH vai direto. Por isso nunca escreva um programa que dependa do texto exato do plano: ele é para gente ler.

Nos outros bancos

Todo banco mostra o plano, cada um com seu jeito. Os trechos abaixo são ilustrativos: não rodam no SQLite.

PostgreSQL: EXPLAIN SELECT …. O SCAN do SQLite lá se chama Seq Scan (leitura sequencial) e o SEARCH por índice, Index Scan. O EXPLAIN ANALYZE roda a consulta de verdade e mostra os tempos; num UPDATE ou DELETE, a documentação recomenda fazer isso dentro de uma transação desfeita com ROLLBACK.
MySQL: EXPLAIN SELECT … mostra uma tabela; EXPLAIN FORMAT=TREE, uma árvore; e EXPLAIN ANALYZE roda a consulta e mostra os tempos.
SQL Server: SET SHOWPLAN_TEXT ON faz os comandos seguintes devolverem o plano sem rodar; lá aparecem nomes como Clustered Index Scan e Clustered Index Seek.

-- PostgreSQL (ilustrativo)
EXPLAIN SELECT * FROM clientes WHERE cidade = 'Teresina';

-- MySQL (ilustrativo)
EXPLAIN FORMAT=TREE SELECT * FROM clientes WHERE cidade = 'Teresina';

Referência rápida

Escrita	O que faz
`EXPLAIN QUERY PLAN consulta`	Mostra o plano, sem rodar a consulta
`EXPLAIN consulta`	Mostra o bytecode (o programa interno)
`.eqp on` / `.eqp off`	No terminal: mostra o plano antes de cada resultado
`SCAN t`	Lê a tabela inteira
`SEARCH t USING …`	Vai direto a algumas linhas
`USING INTEGER PRIMARY KEY (rowid=?)`	Pela chave primária
`USING INDEX i (col=?)`	Pelo índice `i`
`USING COVERING INDEX i`	Só com o índice, sem abrir a tabela
`USE TEMP B-TREE FOR ORDER BY`	Ordenação à parte (também para `GROUP BY` e `DISTINCT`)
colunas `id`, `parent`	Montam a árvore do plano (`parent` = de quem o passo faz parte)

Exercícios

1. Pedir o plano

Complete para ver o plano, e não o resultado:

SELECT * FROM produtos WHERE preco > 50;

2. A palavra do "ler tudo"

Que palavra aparece no começo do plano quando o SQLite lê a tabela inteira?

produtos

3. O plano diz SEARCH clientes USING COVERING INDEX idx_clientes_cidade (cidade=?). O que isso quer dizer?

Que o SQLite vai ler todos os clientes Que a consulta é respondida só com o índice, sem abrir a tabela Que o índice está quebrado Que o SQLite criou um índice temporário

4. A Ana rodou EXPLAIN QUERY PLAN DELETE FROM clientes;. Quantos clientes sobraram?

Nenhum Só os que têm pedidos Todos os 30: o EXPLAIN QUERY PLAN não roda o comando Dá erro: não existe plano de DELETE

5. O que USE TEMP B-TREE FOR ORDER BY no plano quer dizer?

Que o SQLite vai ordenar o resultado à parte, numa estrutura temporária Que a consulta tem um erro no ORDER BY Que o resultado vai sair sem ordem Que a tabela foi apagada e recriada

Missão final: do SCAN ao SEARCH

Missão: o plano abaixo mostra SCAN pedidos: para achar os pedidos de um cliente, o SQLite lê todos. Antes do EXPLAIN QUERY PLAN, crie um índice na coluna certa para o plano virar SEARCH pedidos USING INDEX ….

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 3;

CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 3;

Agora troque o SELECT * por SELECT cliente_id e veja o plano virar COVERING INDEX. Depois tente SELECT id, data: a coluna data não está no índice, e o plano volta a ser só USING INDEX.

Resumo

O plano é o caminho que o banco escolhe para achar as linhas. EXPLAIN QUERY PLAN antes da consulta mostra esse caminho, sem rodar a consulta.
SCAN = ler a tabela inteira; SEARCH = ir direto, pela chave primária ou por um índice.
USING INDEX i (col=?) diz qual índice e qual parte do WHERE ele resolve. COVERING INDEX = o índice basta, sem abrir a tabela.
USE TEMP B-TREE FOR ORDER BY/GROUP BY/DISTINCT = ordenação extra, que um índice às vezes evita.
Leia o plano de cima para baixo; num JOIN, cada linha de baixo roda uma vez para cada linha de cima. A coluna parent (ou a árvore do terminal) mostra os passos de dentro de uma subconsulta.
No terminal, .eqp on mostra o plano de toda consulta. O texto do plano muda entre versões: é para gente ler, não para programas.