Índices Parciais no SQLite: CREATE INDEX ... WHERE

Um índice parcial cobre apenas algumas linhas

Um índice comum tem uma entrada para cada linha da tabela. Já um índice parcial no SQLite só guarda entradas para as linhas que satisfazem a cláusula WHERE que você informa na hora de criá-lo. Resultado: índice menor, menos páginas para percorrer e menos trabalho a cada INSERT ou UPDATE que não mexe na fatia indexada.

A sintaxe é um CREATE INDEX normal com um WHERE no final:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending só guarda entradas das linhas em que status = 'pending'. Pedidos enviados, cancelados e reembolsados ficam de fora. Se 95% da sua tabela orders é histórico e você quase sempre consulta os pedidos em aberto, isso significa um índice 20× menor com a mesma velocidade de consulta.

Quando o planner realmente vai usar o índice parcial

Um índice parcial só pode ser aproveitado quando o SQLite consegue provar que sua query está restrita às mesmas linhas cobertas pelo índice. A forma mais segura de garantir isso é repetir a cláusula WHERE do índice na própria consulta:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

O plano deve mencionar USING INDEX idx_orders_pending. Tire o status = 'pending' da consulta e o planner volta pro full table scan — ele não tem como saber que a query continua dentro do subconjunto indexado.

A regra de bolso: o WHERE da consulta precisa implicar o WHERE do índice. Igualdade na mesma coluna e no mesmo valor é o caso óbvio e seguro. Desigualdades e OR complicam um pouco; confira com EXPLAIN QUERY PLAN.

Por que vale a pena — os três ganhos

Três motivos concretos pra usar índice parcial no SQLite:

1. Ocupa menos disco. Só as linhas que casam com o filtro entram no índice. Num cenário em que "1% da tabela é quente", o índice fica em torno de 1% do tamanho de um índice completo.
2. Escrita mais barata. Inserts e updates só mexem no índice quando a linha bate com o filtro. Um insert com status = 'shipped' na tabela acima nem encosta no idx_orders_pending.
3. Busca na mesma velocidade. Lookup em B-tree é logarítmico no tamanho do índice. Índice menor, busca um pouquinho mais rápida — mas o ganho maior está no entorno: menos cache miss, menos I/O.

Quando uma coluna é muito enviesada — a maioria das linhas tem um valor só e você só se importa com os outros valores raros — esse é o caso clássico pra um índice parcial.

Índice único parcial (o recurso matador)

Um UNIQUE comum vale pra toda linha da tabela. Isso vira problema na hora que você implementa soft delete:

-- Falha: existem duas linhas com email = 'a@x.com', mesmo que uma esteja excluída.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Um índice parcial único permite garantir unicidade somente nas linhas que realmente interessam:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- exclusão lógica
    ('a@x.com', '2026-01-05'),  -- exclusão lógica novamente
    ('a@x.com', NULL);          -- uma linha ativa, permitida

SELECT email, deleted_at FROM users;

Três linhas, o mesmo e-mail e nenhuma violação de constraint — porque só a linha em que deleted_at IS NULL entra na checagem de unicidade. Tente inserir uma segunda linha ativa com o mesmo e-mail e o SQLite levanta UNIQUE constraint failed.

Esse padrão aparece em todo lugar: uma assinatura ativa por cliente, um endereço principal por usuário, uma fatura em aberto por pedido. O índice parcial único expressa isso de forma direta.

Indexando em torno de NULL

NULL se comporta de um jeito esquisito dentro de índices. Um objetivo comum é "ignorar os NULLs por completo" — imagine uma coluna external_id esparsa, em que a maioria das linhas é NULL, mas os valores preenchidos precisam ser únicos:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Engrenagem',  'EXT-001'),
    ('Aparelho',  NULL),
    ('Bugiganga', NULL),
    ('Roda Dentada', 'EXT-002');

SELECT name, external_id FROM products;

Dois NULLs convivem em paz, e as linhas EXT-001 e EXT-002 continuam garantidamente únicas. De quebra, o índice fica menor — linhas com NULL simplesmente não entram nele —, então buscas por external_id permanecem rápidas mesmo quando a tabela cresce.

O que o filtro pode referenciar

A cláusula WHERE de um índice parcial é bem restritiva. Ela pode referenciar:

• Colunas da própria tabela que está sendo indexada.
• Constantes literais.
• Um conjunto pequeno de funções embutidas determinísticas.

O que ela não pode referenciar:

• Outras tabelas.
• Subqueries.
• Funções não determinísticas, como random() ou CURRENT_TIMESTAMP.
• Parâmetros ou variáveis.

Faz sentido: o SQLite precisa avaliar esse filtro a cada INSERT e UPDATE, e o resultado tem que ser estável. Então isto funciona:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

Mas WHERE created_at > date('now') não funciona — date('now') muda com o tempo, então o conjunto de linhas indexadas ficaria mudando debaixo dos pés do SQLite.

Um fluxo rápido para validar o índice

Sempre que criar um índice parcial, passe por três checagens:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. O planejador escolhe o índice?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Ele ignora consultas fora do filtro?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Ele dispensa consultas que não têm o filtro?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

A Query 1 deve usar idx_jobs_runnable. Já as Queries 2 e 3 devem cair em um scan (ou em outro índice, se houver). Se o planejador escolher o índice parcial em uma consulta que você não esperava, releia o filtro — provavelmente ele é mais abrangente do que parece.

Quando não usar um índice parcial

Índice parcial é faca afiada. Motivos para não usar:

• O filtro pega a maior parte da tabela. Se "ativo" representa 90% das linhas, o índice parcial vira um índice comum com passos a mais. Indexe a coluna direto.
• Suas consultas não repetem o filtro literalmente. Se o código usa um ORM que monta WHERE status IN (?, ?, ?) ou calcula o filtro de forma dinâmica, o planejador frequentemente não reconhece a correspondência. Teste com EXPLAIN QUERY PLAN, não suponha.
• O subconjunto quente muda com o tempo. Um índice parcial em "pedidos dos últimos 30 dias" parece tentador, mas não dá para expressar — o filtro precisa ser determinístico. Você teria que reconstruir o índice ou repensar o esquema (uma tabela separada recent_orders ou um booleano archived que você vira durante a noite).

Quando o filtro é estável e atinge uma fatia pequena de uma tabela grande, o índice parcial é um dos ajustes de maior retorno que você consegue fazer no SQLite.

A seguir: lendo planos de consulta

Boa parte desta página dependeu do EXPLAIN QUERY PLAN para confirmar que um índice foi de fato usado. Essa ferramenta merece uma página só dela — como interpretar a saída, o que cada palavra-chave significa e como diferenciar uma busca tranquila por índice de um full scan disfarçado. É o próximo tema.