EXPLAIN: ler o plano em vez de adivinhar

Quando uma consulta fica lenta, a tentação é adivinhar: acrescentar um índice, reescrever o join, trocar o IN por um EXISTS. O Postgres tem uma forma de responder em vez de se adivinhar — basta pedir-lhe o plano.

Os exemplos que se seguem saíram da base deste site, com os números reais que ela devolveu.

1. Duas coisas diferentes com nomes parecidos

EXPLAIN SELECT ...           -- o plano que ele tenciona seguir
EXPLAIN ANALYZE SELECT ...   -- corre mesmo, e diz o que aconteceu

A diferença não é cosmética: o ANALYZE executa a consulta. Num SELECT é inofensivo; num UPDATE ou DELETE escreve a sério. Para esses, envolve-se numa transação e desfaz-se no fim.

BEGIN;
EXPLAIN ANALYZE DELETE FROM posts WHERE id = 1;
ROLLBACK;

2. Lê-se de dentro para fora

O plano é uma árvore escrita ao contrário: os nós mais indentados correm primeiro e alimentam os de cima. Este é o da listagem da página inicial — vinte artigos por data:

Limit (actual rows=20 loops=1)
  Buffers: shared hit=19
  ->  Incremental Sort (actual rows=20 loops=1)
        Sort Key: published_on DESC, id DESC
        Presorted Key: published_on
        ->  Index Scan using posts_published_on_idx on posts (actual rows=21 loops=1)
              Filter: published
              Buffers: shared hit=10
Execution Time: 0.119 ms

Lendo de baixo para cima: o índice de datas entrega as linhas já ordenadas, o Incremental Sort só tem de desempatar pelo id, e o Limit manda parar.

Repare no actual rows=21 do índice. Para devolver vinte, leu vinte e uma — e parou. É isso que um LIMIT em cima de um índice ordenado faz, e é a razão de a paginação da primeira página ser barata mesmo com a tabela a crescer.

3. Os três números que interessam

• actual rows contra a estimativa. Se o planeador previu 10 e apareceram 100 000, ele decidiu com informação errada — e a correção não é mexer na consulta, é correr ANALYZE na tabela.

• loops. Multiplica tudo o que está naquele nó. Um rows=1 loops=7 são sete linhas, não uma.

• Buffers. Quantas páginas de 8 kB foram tocadas. É a melhor medida de custo que há ali, melhor do que o tempo — o tempo muda conforme o que esteja em cache, as páginas lidas não.

O BUFFERS não vem por omissão nas versões mais antigas. Vale sempre a pena pedi-lo.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

4. A linha que costuma ser o alarme

Esta consulta procura um artigo pelo título, e não há índice nenhum nessa coluna:

Seq Scan on posts (actual rows=1 loops=1)
  Filter: ((title)::text = 'Skyfall'::text)
  Rows Removed by Filter: 313
  Buffers: shared hit=48

O Rows Removed by Filter: 313 é o que se procura. Leu a tabela inteira — 314 linhas — para devolver uma. Tudo o que foi lido e deitado fora é trabalho a mais.

E agora a parte honesta: nesta base isso está perfeitamente bem. Com 314 linhas, percorrer a tabela toda custa 48 páginas e 0,2 ms — um índice seria mais lento, porque obrigaria a ler o índice e depois a tabela.

O planeador não se enganou. O que o plano dá não é uma lista de coisas a corrigir: é saber qual das consultas deixa de servir quando a tabela tiver trezentas mil linhas. Essa, e não outra.

5. Quando o loops conta a história

Os artigos de uma etiqueta, com duas junções:

Nested Loop (actual rows=7 loops=1)
  ->  Nested Loop (actual rows=7 loops=1)
        ->  Index Scan using post_tags_slug_key on post_tags t (actual rows=1 loops=1)
              Index Cond: ((slug)::text = 'react'::text)
        ->  Bitmap Index Scan on post_tag_links_tag_id_idx (actual rows=7 loops=1)
              Index Cond: (tag_id = t.id)
  ->  Index Scan using posts_pkey on posts p (actual rows=1 loops=7)
        Index Cond: (id = l.post_id)

O último nó diz rows=1 loops=7: sete acessos ao índice da chave primária, um por cada ligação encontrada. É um Nested Loop, e com sete linhas é a escolha certa.

O mesmo plano com loops=50000 seria outra conversa — e é o N+1 visto do lado da base, com o planeador a ter subestimado o lado de fora da junção.

6. Por onde começar a olhar

• Rows Removed by Filter alto — leu muito para devolver pouco. É onde um índice costuma entrar (ver o artigo sobre índices).

• Estimativa muito diferente do real — as estatísticas estão velhas. ANALYZE na tabela.

• Sort Method: external merge Disk — a ordenação não coube em memória e foi ao disco. É o work_mem.

• Um Nested Loop com milhares de loops — quase sempre uma estimativa errada a montante.

O resumo

O plano não diz o que está mal. Diz o que a base fez — quantas linhas leu, quantas deitou fora, quantas vezes repetiu cada passo. A partir daí deixa de se adivinhar, e essa é a diferença toda.