Predicados sargaveis: como escrever um WHERE amigavel a indices

Um predicado e sargavel (Search ARGument ABLE) quando o planejador consegue usar um indice sobre a coluna para pular direto para as linhas que combinam. Envolva essa coluna em uma funcao — EXTRACT, lower(), date() — e o indice se apaga: o banco avalia a expressao linha a linha, ou seja, um Seq Scan. Veja como evitar isso.

O que anula o indice

Um B-tree comum indexa os valores da coluna, nao os valores de uma funcao aplicada a ela. No instante em que o lado esquerdo do seu WHERE e f(coluna), o indice sobre coluna fica inutil.

-- NOT sargable: function on the column
SELECT id, email
FROM users
WHERE lower(email) = 'ann@shop.io';

-- NOT sargable: EXTRACT wraps created_at
SELECT id, amount
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024;

O sinal e simples: se voce nao consegue manter a coluna nua de um lado da comparacao, o predicado provavelmente nao e sargavel. A mesma armadilha cobre created_at::date = '...', date(created_at), amount + 0 = 100 e coalesce(status, '') = 'paid'.

Pegadinha: aritmetica sobre a coluna tambem mata o indice. WHERE salary * 12 > 100000 nao e sargavel, mas WHERE salary > 100000 / 12 e. Empurre a conta para o lado da constante.

Reescrever para intervalos

O movimento principal e trocar uma funcao sobre a coluna por um intervalo sobre a propria coluna. Um intervalo semiaberto >= ... AND < ... e exato (sem erro no ultimo instante do dia) e usa o indice.

-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
SELECT id, amount
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';

-- One specific day, half-open and index-friendly
SELECT id, amount
FROM orders
WHERE created_at >= '2024-03-15'
  AND created_at <  '2024-03-16';

Para buscas sem diferenciar maiusculas, nao escreva lower(email) = ...; normalize os dados antes ou use o tipo citext. Assim a igualdade simples continua sargavel:

-- Equality stays sargable; index on email is usable
SELECT id, email
FROM users
WHERE email = 'ann@shop.io';

LIKE e o curinga inicial

LIKE com um prefixo ancorado (texto e depois %) e sargavel: um B-tree consegue buscar pelo inicio da string. Um % inicial transforma a busca em uma varredura completa.

-- Sargable: anchored prefix, uses a B-tree index
SELECT id, name
FROM users
WHERE name LIKE 'Ann%';

-- NOT sargable: leading wildcard forces a scan
SELECT id, name
FROM users
WHERE name LIKE '%nna%';

Um detalhe do PostgreSQL: o LIKE por prefixo sob uma locale nao padrao precisa de um indice criado com text_pattern_ops. Para buscar substrings com % no inicio, nenhum B-tree comum ajuda: voce precisa de um indice GIN de trigramas (pg_trgm).

Indices de expressao: quando a funcao e necessaria

As vezes a funcao faz parte da pergunta de verdade. Entao torne o predicado sargavel indexando a propria expressao. O PostgreSQL permite isso:

-- Index the expression, then this query is sargable again
CREATE INDEX idx_users_lower_email ON users (lower(email));

SELECT id, email
FROM users
WHERE lower(email) = 'ann@shop.io';

-- Functional index for a derived "year" lookup
CREATE INDEX idx_orders_created_year
ON orders (EXTRACT(YEAR FROM created_at));

O detalhe: a expressao no indice e no WHERE precisa coincidir ao pe da letra. Uma alternativa e uma coluna gerada com um indice comum sobre ela. Nao acumule indices funcionais onde um intervalo basta: um indice sobre lower(email) custa espaco e atrasa as escritas.

MySQL e ClickHouse

No MySQL as regras sao identicas: WHERE DATE(created_at) = '2024-03-15' ignora o indice, entao reescreva como intervalo. Desde a 8.0.13 o MySQL aceita indices funcionais (CREATE INDEX ... ((lower(email)))); antes disso, uma coluna gerada dava o mesmo efeito.

No ClickHouse o "indice" e uma chave primaria esparsa mais a ordem de ordenacao. Uma funcao sobre uma coluna da chave atrapalha o descarte de granulos da mesma forma, entao filtre pela coluna de chave nua, de preferencia por intervalo.

Resumo: mantenha a coluna nua de um lado da comparacao, troque funcoes por intervalos, evite o % inicial e, onde a funcao for indispensavel, indexe a expressao.