Limites da busca com LIKE

Table scan, custo e concorrência

O que EXPLAIN revela sobre sequential/table scan e por que o custo explode com usuários simultâneos.

Intermediário 42 min 32 pontos Leitura 0%

Nesta aula você vai

  • Ler planos EXPLAIN que mostram sequential scan / type ALL em buscas LIKE
  • Relacionar custo unitário da consulta com carga concorrente
  • Argumentar tecnicamente por que full-text com índice invertido muda o perfil de I/O

Table scan, custo e concorrência

Objetivos

Nesta aula você vai:

  • Interpretar EXPLAIN / EXPLAIN ANALYZE em consultas LIKE '%...%'
  • Estimar como o custo se multiplica com usuários simultâneos
  • Conectar o sintoma (latência, lock wait, CPU de I/O) à ausência de índice invertido

Introdução

Custo de consulta não é só “quantos milissegundos no meu notebook”. Em produção, cada busca compete por buffers, CPU e I/O com escritas, relatórios e outras leituras. Quando o plano escolhe varrer a tabela inteira, você paga o preço de ler páginas que não interessam — e paga de novo a cada sessão concorrente.

No TicketFlow, um SaaS de atendimento, a tabela tickets guarda assunto, corpo e tags textuais. Há 3,2 milhões de tickets históricos. Agentes digitam timeout gateway pagamento na caixa de busca global. Com LIKE, o banco percorre enormes fatias da heap; com dezenas de agentes no mesmo turno, o padrão vira fila de espera e p95 de API acima do SLA.

Conteúdo

O que o plano costuma revelar

-- TicketFlow (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, assunto, status
FROM tickets
WHERE assunto LIKE '%timeout gateway%'
   OR corpo LIKE '%timeout gateway%'
ORDER BY aberto_em DESC
LIMIT 25;

Trecho ilustrativo de plano (números fictícios, forma típica):

Limit  (cost=185432.10..185432.16 rows=25 width=120)
  ->  Sort  (cost=185432.10..185440.88 rows=3512 width=120)
        Sort Key: aberto_em DESC
        ->  Seq Scan on tickets  (cost=0.00..185310.00 rows=3512 width=120)
              Filter: ((assunto ~~ '%timeout gateway%'::text)
                    OR (corpo ~~ '%timeout gateway%'::text))
              Rows Removed by Filter: 3196488
              Buffers: shared hit=1200 read=88400

Sinais importantes:

Campo / sintoma Leitura
Seq Scan / type: ALL Varredura da tabela (ou fatia grande)
Rows Removed by Filter alto Quase tudo foi lido para quase nada servir
Buffers: ... read= alto I/O real; cache frio dói mais
cost elevado no nó de scan O otimizador já prevê caro 
-- TicketFlow (MySQL)
EXPLAIN
SELECT id, assunto, status
FROM tickets
WHERE assunto LIKE '%timeout gateway%'
   OR corpo LIKE '%timeout gateway%'
ORDER BY aberto_em DESC
LIMIT 25;

Em MySQL, espere type = ALL (ou range inútil), rows na casa dos milhões e Extra com Using where; Using filesort se houver ordenação sem índice adequado.

Concorrência: o multiplicador esquecido

Suponha que uma busca sozinha leve 180 ms de CPU+I/O em média. Com 40 agentes buscando ao mesmo tempo, o trabalho agregado não é “180 ms”; é dezenas de varreduras competindo. Efeitos cascata:

  1. Buffer pool / shared buffers saturam com páginas frias da mesma tabela
  2. Autovacuum / writers atrasam porque leitores pesados ocupam I/O
  3. Timeouts de aplicação geram retries, que pioram a fila
-- Carga sintética de estudo (não rode em produção)
-- Objetivo: sentir o p95 sob N sessões, não “passar no teste local”
SELECT count(*) FROM tickets
WHERE corpo LIKE '%gateway%';

Mesmo um count(*) com LIKE é um bom estresse controlado em staging: ele força o scan sem mascarar o custo com LIMIT precoce.

Por que full-text muda o perfil

Com índice invertido (FULLTEXT no MySQL, GIN/tsvector no PostgreSQL), a consulta começa pelos termos e chega a um conjunto pequeno de documentos candidatos. O plano deixa de ser “leia quase tudo e filtre” e passa a ser “procure o termo → liste postings → ranqueie → limite”.

-- Antecipação (PostgreSQL): após GIN em search_vector
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, assunto
FROM tickets
WHERE search_vector @@ plainto_tsquery('portuguese', 'timeout gateway pagamento')
ORDER BY ts_rank(search_vector, plainto_tsquery('portuguese', 'timeout gateway pagamento')) DESC
LIMIT 25;

Você deve ver Bitmap Index Scan / Bitmap Heap Scan (ou equivalente) com rows candidatas muito menores e Buffers read drasticamente reduzidos em cache quente.

Problema comum e solução

Problema: o time vê “CPU alta no banco” e aumenta máquina verticalmente, sem mudar o plano de busca.

Solução: use EXPLAIN ANALYZE como evidência, meça p95 sob concorrência em staging e migre a busca textual para full-text indexado. Escala vertical só adia a fatura.

Como analisar

Checklist mínimo antes de abrir PR de migração:

  1. Capture o plano LIKE com ANALYZE e anote rows, tipo de scan e buffers
  2. Repita em horário de cache quente e cache frio (ou após DISCARD controlado em lab)
  3. Rode a mesma carga com 1, 10 e 40 clientes virtuais; compare p50/p95
  4. Depois do índice full-text, repita os quatro passos e arquive os dois planos lado a lado
-- MySQL: comparar rows e type
EXPLAIN FORMAT=JSON
SELECT id FROM tickets WHERE MATCH(assunto, corpo) AGAINST ('timeout gateway' IN NATURAL LANGUAGE MODE)
LIMIT 25;

Resumo

  • LIKE '%...%' tipicamente produz sequential/table scan e alto descarte por filtro
  • Concorrência multiplica I/O e transforma latência aceitável em SLA quebrado
  • Vertical scaling não corrige um plano O(n) por consulta
  • Full-text com índice invertido reduz candidatos antes do ranking — e o EXPLAIN mostra isso