Achei que era só um filtro de busca. O PostgreSQL me mostrou que não.
A motivação
Assisti a um vídeo do Renato Augusto sobre o impacto de buscas usando ILIKE '%texto%'. Na hora lembrei que tinha implementado, num sistema uma consulta que usava exatamente isso, um campo de busca livre numa tela de ocorrências. Isso me instigou a verificar se a gente tinha problema ali.
O problema
O código por trás do campo de busca era isso (resumido):
public function scopeFilterBySearch($query, $search)
{
return $query->when($search, function ($query) use ($search) {
$query->where(function ($q) use ($search) {
if (ctype_digit($search)) {
$q->orWhere('id', intval($search));
}
$q->orWhere('external_id', 'ILIKE', "%{$search}%");
$q->orWhereRaw("data_snapshot->'gateway'->>'mac' ILIKE ?", ["%{$search}%"]);
$q->orWhereRaw("data_snapshot->'remote_management_module'->>'mac' ILIKE ?", ["%{$search}%"]);
$q->orWhereRaw("data_snapshot->'public_lighting_point'->>'tag' ILIKE ?", ["%{$search}%"]);
});
});
}
Um único campo de texto tentando adivinhar, na mesma consulta, se o que a pessoa digitou é um ID, um MAC de gateway, um MAC de módulo ou uma TAG três dessas comparações feitas dentro de uma coluna jsonb (data_snapshot), extraindo o valor a cada linha.
A base de desenvolvimento era pequena (pouco mais de 2 mil registros), então tudo parecia rápido. Isso não respondia à pergunta que importava: como essa consulta se comporta com centenas de milhares de ocorrências? Gerei uma massa sintética de 300 mil registros pra descobrir.
A investigação
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM occurrences
WHERE
id = 5
OR external_id ILIKE '%5%'
OR data_snapshot->'gateway'->>'mac' ILIKE '%5%'
OR data_snapshot->'remote_management_module'->>'mac' ILIKE '%5%'
OR data_snapshot->'public_lighting_point'->>'tag' ILIKE '%5%'
ORDER BY status DESC, date_time_registration DESC;
Gather Merge (cost=47182.28..76351.91 rows=250008 width=319) (actual time=316.257..500.050 rows=268352 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5410 read=7217, temp read=10535 written=10558
-> Sort (cost=46182.25..46494.76 rows=125004 width=319) (actual time=307.565..377.160 rows=89451 loops=3)
Sort Key: status DESC, date_time_registration DESC
Sort Method: external merge Disk: 29712kB
Buffers: shared hit=5410 read=7217, temp read=10535 written=10558
-> Parallel Seq Scan on occurrences (cost=0.00..17224.66 rows=125004 width=319) (actual time=0.120..134.128 rows=89451 loops=3)
Filter: ((id = 5) OR ((external_id)::text ~~* '%5%'::text) OR ...)
Rows Removed by Filter: 10553
Buffers: shared hit=5320 read=7217
Planning Time: 0.357 ms
Execution Time: 515.701 ms
Alguém buscando "o ID 5", esperando 1 resultado, recebia 268.352 registros — porque ILIKE '%5%' bate em qualquer MAC ou TAG que tenha o dígito/hex 5 em qualquer posição. O primeiro instinto foi "falta índice". Não era bem isso.
O detalhe mais contraintuitivo: id tem chave primária. Isolado (WHERE id = 5), o Postgres usa Index Scan em 0.07ms. Mas dentro desse OR, junto com predicados não-indexáveis (ILIKE em JSON), o Postgres não usa o índice nem pra essa parte. Quando um ramo do OR não tem estratégia de índice, o planner desiste de otimizar o OR inteiro e cai pra Seq Scan mesmo o predicado que sozinho seria instantâneo.
Achado extra, no ORDER BY: com 268 mil linhas pra ordenar, o Sort Method: external merge Disk: 29712kB mostra que a ordenação nem coube em memória cada worker gravou e leu ~25-28MB em arquivo temporário no disco. Não era só CPU lendo linha por linha; tinha I/O de disco genuíno rolando no meio da consulta.
E tem um terceiro custo, esse só visível olhando a camada Laravel: como a listagem usa paginate(), cada busca dispara duas consultas completas um COUNT(*) com o mesmo Seq Scan, mais o SELECT da página. O tempo médio por request (via Eloquent) ficou perto do dobro do tempo do EXPLAIN isolado.
As soluções
A causa raiz não era "falta de índice" era um campo único fazendo três coisas ao mesmo tempo: misturando tipos de dado num OR heterogêneo, buscando dentro de JSON, e usando ILIKE '%...%' (que exige índice trigram, não um índice comum). Dava pra criar índice em cima de índice que nenhum resolveria sozinho enquanto essas três camadas continuassem empilhadas.
E vale marcar uma coisa: nada disso era bug. O código fazia exatamente o que tinha sido pedido o problema era que o pedido original ("um campo de busca genérico") nunca tinha sido pensado a fundo o suficiente pra virar um requisito sustentável. Resolver de verdade significava voltar a conversar com o cliente sobre o que aquele campo deveria fazer, não só mexer no banco.
O primeiro passo, na real, foi mostrar ao cliente que a consulta já era ineficiente mesmo com poucos dados o problema só ia aparecer quando o volume crescesse, e nessa hora já seria tarde pra resolver com calma. E a solução técnica correta mexia direto na tela: o campo de busca livre virou um campo com seletor de tipo obrigatório (ID, TAG, MAC do módulo, MAC do gateway). Isso muda o layout o cliente precisou topar que o usuário agora informe o que está buscando antes de digitar. Combinado isso, o retrabalho no fluxo foi:
- Colunas dedicadas (
tag,mac_module,mac_gateway) na própria tabelaoccurrences, escritas uma vez no momento em que a ocorrência é criada em vez de extraídas do JSON a cada busca. - Índices GIN com
pg_trgmnessas colunas, pra suportar de fato busca por "contém em qualquer posição". search_typeobrigatório (id | tag | mac_module | mac_gateway) sem ele, nenhum índice resolve a ambiguidade de um campo que tenta ser cinco coisas ao mesmo tempo. É breaking change: buscar sem informar o tipo agora retorna 422.
Resultado, no mesmo volume de teste (300 mil linhas):
| Cenário | Antes | Depois |
|---|---|---|
| Busca por tag exata (seletiva) | 289ms (Seq Scan) | 0.99ms (Bitmap Index Scan) |
| Busca por "ID 5" | 515.7ms, 268.352 resultados errados | 0.075ms, resultado correto (exato) |
Achado à parte: a listagem padrão, sem busca nenhuma, sofria do mesmo problema
Enquanto testava, reparei que até a tela de abertura — "Ocorrências", sem nenhum filtro digitado — fazia Seq Scan pra ordenar. A listagem sempre ordena por status DESC, date_time_registration DESC, com ou sem filtro de status aplicado, e não existia índice nenhum cobrindo esse padrão de acesso.
Antes, filtro Status = Aberta:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM occurrences
WHERE status = 1
ORDER BY status DESC, date_time_registration DESC
LIMIT 25;
Limit (cost=46357.89..46360.81 rows=25 width=351) (actual time=96.124..98.298 rows=25 loops=1)
Buffers: shared hit=5219 read=37776
-> Gather Merge (cost=46357.89..53587.06 rows=61960 width=351) (actual time=96.122..98.293 rows=25 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=45357.87..45435.32 rows=30980 width=351) (actual time=88.313..88.316 rows=19 loops=3)
Sort Key: date_time_registration DESC
Sort Method: top-N heapsort Memory: 45kB
-> Parallel Seq Scan on occurrences (cost=0.00..44483.64 rows=30980 width=351) (actual time=0.053..78.701 rows=24998 loops=3)
Filter: (status = 1)
Rows Removed by Filter: 75007
Buffers: shared hit=5143 read=37776
Planning Time: 0.260 ms
Execution Time: 98.342 ms
Antes, sem nenhum filtro (Status = Todos):
Limit (cost=48702.97..48705.88 rows=25 width=351) (actual time=125.040..127.423 rows=25 loops=1)
-> Gather Merge (cost=48702.97..77911.57 rows=250342 width=351) (actual time=125.038..127.419 rows=25 loops=1)
-> Sort (cost=47702.94..48015.87 rows=125171 width=351) (actual time=120.337..120.339 rows=20 loops=3)
Sort Key: status DESC, date_time_registration DESC
Sort Method: top-N heapsort Memory: 43kB
-> Parallel Seq Scan on occurrences (cost=0.00..44170.71 rows=125171 width=351) (actual time=0.042..82.216 rows=100005 loops=3)
Buffers: shared hit=5242 read=37677
Planning Time: 0.165 ms
Execution Time: 127.461 ms
Criei o índice composto (status DESC, date_time_registration DESC) e os dois cenários passaram a usar Index Scan:
Depois, filtro Status = Aberta:
Limit (cost=0.42..53.87 rows=25 width=351) (actual time=0.070..0.479 rows=25 loops=1)
-> Index Scan using idx_occurrences_status_date on occurrences (cost=0.42..159236.77 rows=74484 width=351) (actual time=0.066..0.467 rows=25 loops=1)
Index Cond: (status = 1)
Execution Time: 0.530 ms
Depois, sem filtro (Status = Todos):
Limit (cost=0.42..15.88 rows=25 width=351) (actual time=0.115..0.644 rows=25 loops=1)
-> Index Scan using idx_occurrences_status_date on occurrences (cost=0.42..184997.36 rows=299254 width=351) (actual time=0.112..0.632 rows=25 loops=1)
Execution Time: 0.687 ms
| Cenário | Antes | Depois |
|---|---|---|
| Status = Aberta | 98.3ms (Seq Scan) | 0.53ms (Index Scan) |
| Status = Todos | 127.5ms (Seq Scan) | 0.69ms (Index Scan) |
Um único índice resolveu os dois casos porque a listagem sempre ordena pelas mesmas duas colunas, com ou sem filtro de status o Postgres para assim que acha as 25 primeiras linhas, sem tocar no resto da tabela.
O aprendizado
O maior aprendizado não foi criar um índice. Foi ver, na prática, que índice não é mágica é uma decisão de custo que o próprio Postgres faz linha a linha.
A prova mais clara veio de um teste que eu esperava que desse certo e não deu: busquei pelo dígito 9 na coluna tag, já com o índice trigram criado. Esse dígito aparece em quase metade da tabela (142 mil de 300 mil linhas). Resultado: o Postgres ignorou o índice e voltou a fazer Seq Scan de propósito, porque acessar 142 mil páginas de forma aleatória via índice custa mais do que simplesmente ler tudo sequencialmente. O índice existia, estava correto, e o planner decidiu não usá-lo porque a consulta não era seletiva o suficiente pra justificar.
Isso não é bug nem falha da solução. É o comportamento esperado de qualquer banco relacional: índice ajuda buscas seletivas, não buscas genéricas. Só que eu só entendi isso de verdade quando vi acontecer com meus próprios dados, não quando li sobre isso.
Teve também um aprendizado que não é sobre banco de dados: a causa raiz de tudo isso não era um bug de código era um requisito que nunca tinha sido pensado até o fim. Resolver de verdade exigiu voltar pro cliente e alinhar o que aquele campo de busca deveria fazer, não só otimizar a consulta que já existia.
Desde então, antes de escrever uma consulta nova, a primeira pergunta que faço é: como o Postgres provavelmente vai executar isso? e, se der, provar com EXPLAIN em vez de assumir.
Curioso se mais gente já passou por algo parecido uma tarefa pequena que virou uma investigação de arquitetura no meio do caminho. Compartilha aí embaixo.