CS Fundamentals Trainer · Guias
Como funciona um planejador de consultas: planos, estimativas e por que erram
Ler um plano de execucao e consertar um plano ruim: os caminhos de acesso e os tres algoritmos de juncao entre os quais um planejador escolhe, de onde vem os numeros de custo, como as contagens de linhas sao estimadas e por que a suposicao de independencia e as expressoes escondidas as arruinam, a ordem das juncoes, o que o executor faz com um plano e uma escada de diagnostico - com cada plano e tempo produzidos por uma instancia PostgreSQL 16.
50 min de leitura
O SQL não diz como responder a uma pergunta. Ele diz o que a resposta deve conter, e outra coisa decide se uma tabela será lida do começo ao fim, qual índice usar, qual dos três algoritmos de junção executar e em que ordem combinar cinco tabelas. Essa outra coisa é o planejador de consultas, e o que ele de fato otimiza não é a sua consulta: é uma estimativa dos seus dados. Quase todo mistério de desempenho em banco de dados — a consulta que foi rápida por um ano e desabou na terça, o índice que é ignorado, a junção que leva quatro minutos em vez de quatro milissegundos — é a distância entre essa estimativa e a realidade.
Este guia é sobre encurtar essa distância. Ele cobre como ler um plano, os caminhos de acesso e os algoritmos de junção entre os quais um planejador escolhe, de onde vêm os números de custo, como as contagens de linhas são estimadas e por que erram, como a ordem das junções é buscada, o que o executor faz com o plano que recebe, e um procedimento ordenado para diagnosticar um plano ruim.
Como ler.
| Você quer | Leia |
|---|---|
| Consertar uma consulta lenta hoje | A seção 2 (ler um plano), depois a escada da seção 12, depois a seção que ela indicar |
| O modelo, em meia hora | As seções 1 a 4, depois o Resumo rápido |
| Entender por que as estimativas falham | As seções 6 e 7, que são o coração do guia |
| O quadro completo | Em ordem. As seções 8 (ordem das junções) e 10 (modelos de execução) são os aprofundamentos |
Sobre as medições. Cada plano, contagem de linhas e tempo citado aqui foi produzido no PostgreSQL 16.15, na máquina que escreveu este guia, contra o conjunto de dados que o apêndice constrói: 100.000 clientes em 20 cidades, 500.000 pedidos com uma coluna status deliberadamente desbalanceada e 1.500.000 itens de pedido (52 MB e 96 MB de tabela mais índices). Os dados estavam em cache, o servidor usou as configurações padrão salvo onde uma linha diz o contrário, e o script está no apêndice para você executar.
Que tipo de evidência há neste guia.
| Tipo | Onde | Quanto confiar |
|---|---|---|
| Comportamento documentado | a documentação do PostgreSQL e os artigos em Fontes | tanto quanto a fonte; o comportamento do planejador muda entre versões maiores |
| Planos e contagens produzidos aqui | cada saída de EXPLAIN abaixo | são reais, e são o que esta versão escolheu para estes dados; os seus serão diferentes |
| Tempos produzidos aqui | os números em milissegundos | úteis como razão entre dois planos em uma máquina, sem sentido como valores absolutos |
| Aritmética | a tabela de espaço de busca da seção 8 | decorre das definições |
| Comparação de desempenho entre motores | nenhuma | nada aqui compara a velocidade do PostgreSQL com a de outro banco |
A maior parte do que segue é PostgreSQL, porque ele mostra o seu raciocínio de graça. As ideias — modelos de custo, estimativa de cardinalidade, os três algoritmos de junção, a busca de planos — são as mesmas em MySQL, SQL Server, Oracle e nos motores analíticos; a seção 14 mapeia as diferenças.
1. Para que serve o planejador
Em resumo. O planejador transforma uma consulta declarativa em um programa, escolhendo um caminho de acesso por tabela, um algoritmo por junção e uma ordem para as junções. Ele escolhe a combinação de menor custo estimado, e a estimativa é um modelo dos seus dados, não uma medição deles.
Um planejador faz três trabalhos, e cada seção abaixo é um deles em detalhe:
| Trabalho | As escolhas | Seção |
|---|---|---|
| Caminho de acesso: como tirar linhas de uma tabela | varredura sequencial, index scan, index-only scan, bitmap scan | 3 |
| Algoritmo de junção: como combinar duas entradas | nested loop, hash join, merge join | 4 |
| Ordem das junções: qual par combinar primeiro | qualquer ordem que as reescritas permitam, e isso cresce fatorialmente | 8 |
Para escolher, ele precisa de duas coisas: um modelo de custo que diga quanto o trabalho custa (seção 5) e uma estimativa de cardinalidade que diga quantas linhas cada passo vai produzir (seção 6). O modelo de custo é a parte estável — é aritmética sobre leituras de página e custos de CPU por linha. A estimativa de cardinalidade é a parte frágil, porque é extrapolada de estatísticas coletadas na última vez que alguém rodou ANALYZE.
Essa assimetria é a coisa mais útil que se pode saber sobre planejadores. Quando um plano é ruim, a fórmula de custo quase nunca está errada; a contagem de linhas que a alimentou está. O estudo clássico disso é o de Leis e colegas, "How good are query optimizers, really?", que desmontou a maquinaria de ordenação de junções de vários sistemas e concluiu que são os erros de estimativa, não a busca nem o modelo de custo, que produzem planos ruins — e que os erros crescem conforme as junções se acumulam.
Uma segunda coisa que vale internalizar logo: o planejador otimiza para o resultado inteiro, a menos que você diga o contrário. O LIMIT muda a aritmética, porque um plano que devolve a primeira linha rapidamente pode ser a forma mais barata de obter dez linhas e a mais cara de obter todas. A seção 5 tem isso medido.
2. Ler um plano
Em resumo. O EXPLAIN mostra o plano; o EXPLAIN ANALYZE executa e mostra o que realmente aconteceu. O único hábito que importa é comparar a contagem de linhas estimada com a real em cada nó, de baixo para cima.
Este é o plano mais simples possível, com o conjunto de dados do apêndice:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE id = 424242;
Index Scan using orders_pkey on orders (cost=0.42..8.44 rows=1 width=27)
(actual time=0.026..0.026 rows=1 loops=1)
Index Cond: (id = 424242)
Buffers: shared hit=4
Planning Time: 0.378 ms
Execution Time: 0.062 ms
Vale saber nomear tudo o que está ali:
| O que você vê | O que significa |
|---|---|
Index Scan using orders_pkey | o tipo do nó e, aqui, o índice que ele escolheu |
cost=0.42..8.44 | custo de partida estimado, depois custo total estimado, em unidades arbitrárias (seção 5) |
rows=1 width=27 | a estimativa de linhas produzidas e o tamanho médio delas em bytes |
actual time=0.026..0.026 | milissegundos reais até a primeira linha, depois até a última |
rows=1 loops=1 | linhas que o nó realmente produziu por laço, e quantas vezes ele rodou |
Index Cond | a condição que o próprio índice avaliou |
Buffers: shared hit=4 | páginas de 8 kB encontradas em cache; read= significaria buscadas no sistema operacional |
Planning Time / Execution Time | quanto tempo levou escolher o plano, e executá-lo |
Quatro hábitos transformam isso em ferramenta de diagnóstico.
Leia de dentro para fora. O nó mais profundo e mais indentado roda primeiro, e cada pai consome as linhas dos seus filhos. O nó em que o custo ou o tempo explode é o que precisa ser entendido, e normalmente ele está abaixo daquele que parece lento.
Multiplique por loops. Um nó que informa rows=1 loops=400000 produziu 400.000 linhas no total e rodou 400.000 vezes. O actual time médio também é por laço, então uma busca por índice de 0,002 ms repetida 400.000 vezes são 0,8 segundo de trabalho informados como um nó muito rápido. A seção 7 tem exatamente esse plano.
Compare rows= com actual rows. É aí que está o diagnóstico. A razão entre estimativa e realidade, no nó mais baixo em que ela erra, diz se o problema são as estatísticas, um predicado que o planejador não consegue estimar ou uma correlação que ele não conhece. Tudo acima de uma estimativa ruim foi escolhido com informação ruim.
Peça mais ao ANALYZE. O BUFFERS mostra acertos e leituras de cache; o TIMING OFF remove a sobrecarga do relógio por linha quando você só precisa das contagens; o SETTINGS informa parâmetros de planejamento fora do padrão; e o WAL mostra o volume de log de escrita antecipada em uma escrita. E lembre que o EXPLAIN ANALYZE executa o comando, o que para um UPDATE ou DELETE significa envolvê-lo em uma transação que você desfaz.
Três linhas que aparecem em planos reais e respondem a uma pergunta antes de você fazê-la:
Rows Removed by Filter: 80000— o nó leu 100.000 linhas e jogou 80.000 fora. Um predicado que poderia ser uma condição de índice está sendo aplicado depois do fato.Sort Method: external merge Disk: 3712kB— a ordenação não caiu nowork_meme foi para o disco.Buckets: 4096 Batches: 64 Memory Usage: 128kB— um hash join se dividiu em 64 passagens sobre os dados porque a tabela de hash não caiu. Um lote é o que você quer.
3. Tirar linhas de uma tabela
Em resumo. Há quatro formas de entrar, e o planejador escolhe pela quantidade de linhas que acha que vai manter. Contar linhas e buscar linhas viram em pontos muito diferentes, e é por isso que "ele usa o índice no count(*) mas não no select *" é normal, e não um defeito.
| Caminho de acesso | Como funciona | Ganha quando |
|---|---|---|
| Varredura sequencial | lê todas as páginas em ordem | uma grande fração da tabela se qualifica, ou a tabela é pequena; leituras sequenciais são baratas por página |
| Index scan | percorre o índice e busca cada linha na tabela | poucas linhas se qualificam, ou as linhas são necessárias na ordem do índice |
| Index-only scan | responde só com o índice, sem acesso à tabela | o índice contém todas as colunas de que a consulta precisa e as páginas estão visíveis |
| Bitmap heap scan | reúne as localizações das linhas de um ou mais índices, ordena-as e lê cada página uma vez | uma fração intermediária se qualifica, ou dois índices precisam ser combinados |
O bitmap scan é o que as pessoas esquecem, e ele existe por uma razão precisa: um index scan visita páginas da tabela na ordem do índice, o que é E/S aleatória e pode visitar a mesma página várias vezes, enquanto um bitmap scan visita cada página uma vez em ordem física. É por isso que a preferência do planejador caminha de index scan para bitmap scan e para varredura sequencial conforme o conjunto de resultados cresce. (Por que uma árvore B dá ordem de índice em primeiro lugar é o assunto de motores de armazenamento.)
Aqui está essa progressão medida. A mesma forma de consulta, SELECT * FROM orders WHERE created_at em uma janela que se alarga, com um índice em created_at:
| Janela | Linhas | Fração da tabela | Plano escolhido |
|---|---|---|---|
| 1 dia | 714 | 0,1% | Bitmap Heap Scan |
| 7 dias | 5.004 | 1,0% | Bitmap Heap Scan |
| 30 dias | 21.449 | 4,3% | Bitmap Heap Scan |
| 120 dias | 85.799 | 17,2% | Bitmap Heap Scan |
| 240 dias | 171.560 | 34,3% | Bitmap Heap Scan |
| 700 dias | 500.000 | 100% | Seq Scan |
E as mesmas janelas, quando a consulta só precisa contar linhas, de modo que o índice sozinho pode respondê-la:
| Janela | Linhas | Fração | Plano escolhido |
|---|---|---|---|
| 1 dia | 714 | 0,1% | Index Only Scan |
| 180 dias | 128.699 | 25,7% | Index Only Scan |
| 360 dias | 257.240 | 51,4% | Index Only Scan |
| 700 dias | 500.000 | 100% | Parallel Seq Scan |
Duas lições. Primeiro, um index-only scan continua atraente com metade da tabela, porque não há acesso à tabela a pagar — o índice é menor que ela e é lido em ordem. Segundo, quando as linhas precisam ser buscadas, o ponto de virada é muito mais cedo, e o ponto exato depende de quão bem a ordem do índice combina com a ordem física. O PostgreSQL mede isso como a estatística de correlation por coluna: para a coluna desbalanceada status ela é 0,67 aqui, para created_at é praticamente zero.
Índices de cobertura, e a visita à tabela que você evita
EXPLAIN (ANALYZE) SELECT sum(total_cents) FROM orders WHERE status = 'cancelled';
Index Only Scan using orders_status_total_idx on orders (actual rows=9500 loops=1)
Heap Fetches: 0
Acrescentar total_cents ao índice em status transformou uma varredura que precisava visitar a tabela em uma que não precisa: Heap Fetches: 0. É toda a ideia de um índice de cobertura. O custo é um índice mais largo para manter em cada escrita, então ele compensa para consultas que você roda constantemente, não para toda consulta que lhe vier à cabeça.
Um índice composto ganha de dois índices de uma coluna, quase sempre
Com índices separados em status e created_at, uma consulta com os dois predicados usa um índice e filtra o resto:
Bitmap Heap Scan on orders (actual rows=24276 loops=1)
Recheck Cond: (status = 'open')
Heap Blocks: exact=3677
-> Bitmap Index Scan on orders_status_idx (actual rows=90000 loops=1)
Note a forma: o índice produziu 90.000 candidatos, a visita à tabela custou 3.677 páginas e 24.276 linhas sobreviveram. Com um índice em (status, created_at) a mesma consulta se torna:
Index Only Scan using orders_status_created_idx on orders (actual rows=24276 loops=1)
Heap Fetches: 0
Medido em cinco execuções de cada um nesta máquina: 3,24 ms com o índice composto contra 27,62 ms com os dois separados, e o índice composto não tocou nenhuma página da tabela. O PostgreSQL consegue combinar dois índices com um BitmapAnd, e o fará quando nenhum dos dois for seletivo sozinho, mas um índice composto na ordem certa de colunas é a ferramenta mais forte. A ordem importa: (status, created_at) atende uma consulta só por status, enquanto (created_at, status) não atende uma consulta só por status.
O guia companheiro índices de banco de dados cobre tipos de índice e ordem de colunas em detalhe; este guia é sobre por que o planejador os procura ou os ignora.
4. Unir duas entradas
Em resumo. Há três algoritmos, eles têm formas de custo completamente diferentes, e o planejador escolhe por contagens estimadas de linhas. Um nested loop sobre algumas centenas de linhas é excelente e sobre algumas centenas de milhares é um desastre, e é por isso que uma estimativa errada aqui é o tipo mais caro.
| Algoritmo | Como funciona | Forma do custo | Escolhido quando |
|---|---|---|---|
| Nested loop | para cada linha da entrada externa, busca as correspondências na interna | linhas externas × custo de uma busca interna | o lado externo é pequeno e o interno tem índice na chave de junção |
| Hash join | constrói uma tabela de hash na entrada menor e a consulta com a maior | lê as duas entradas uma vez, mais a memória da tabela | os dois lados são grandes e a junção é por igualdade |
| Merge join | ordena as duas entradas pela chave e as percorre juntas | custo da ordenação, ou zero se as duas já chegam ordenadas | as entradas já estão ordenadas, ou se quer um resultado ordenado |
A mesma forma de consulta em duas seletividades mostra a escolha sendo feita corretamente. Primeiro, 500 pedidos disputados unidos aos seus clientes:
Nested Loop (actual time=0.032..2.756 rows=500 loops=1)
-> Index Scan using orders_status_idx on orders o (actual rows=500 loops=1)
Index Cond: (status = 'disputed')
-> Index Scan using customers_pkey on customers c (actual rows=1 loops=500)
Index Cond: (id = o.customer_id)
Execution Time: 3.008 ms
Note o loops=500 no nó interno: são 500 buscas por chave primária, cada uma custando cerca de 0,002 ms. Agora a mesma junção com status = 'shipped', que são 400.000 linhas:
Hash Join (actual time=64.504..169.585 rows=133333 loops=3)
Hash Cond: (o.customer_id = c.id)
Três laços porque o plano foi paralelo, com dois trabalhadores. Média de três execuções: 190,8 ms. Depois, a mesma consulta com os hash joins e merge joins desligados, de modo que o planejador tem de usar um nested loop:
Nested Loop (actual time=0.101..312.842 rows=133333 loops=3)
-> Parallel Seq Scan on orders o (actual rows=133333 loops=3)
-> Index Scan using customers_pkey on customers c (actual rows=1 loops=202532)
Execution Time: 356.269 ms
Média de três execuções: 397,1 ms, cerca de 2,1 vezes mais lento. Duas coisas merecem atenção. O nested loop não é catastrófico aqui, porque a tabela interna é pequena e está toda em cache, então 400.000 buscas por índice custam 0,002 ms cada. E é exatamente essa a armadilha: em uma tabela grande demais para o cache, essas buscas se tornam leituras aleatórias, e o mesmo plano não é duas vezes mais lento, e sim cem. A seção 7 mostra essa falha acontecendo de verdade.
O work_mem decide se um hash join cabe na memória
Um hash join constrói a sua tabela no work_mem (4 MB por padrão, por nó e por trabalhador — não por consulta). Quando o lado de construção não cabe, ele é dividido em lotes gravados em arquivos temporários e lidos de volta:
work_mem | Lotes | Tempo médio de execução |
|---|---|---|
| 64 kB | 64 | 137,7 ms |
| 1 MB | 1 | 124,1 ms |
| 4 MB | 1 | 120,9 ms |
| 64 MB | 1 | 118,1 ms |
Uma penalidade de 14% por 64 passagens parece branda, e é, por uma razão que vale dizer com honestidade: este conjunto de dados é pequeno e está inteiramente em cache, então "gravar em arquivo temporário" significou gravar no cache de páginas do sistema operacional. Em dados que não cabem na memória, o mesmo despejo é a diferença entre uma consulta e um incidente. O que se transfere desta tabela é o diagnóstico, não o número: Batches: 1 significa que o hash caiu, qualquer valor maior significa que não, e Sort Method: external merge Disk: … é o mesmo sinal para uma ordenação.
O risco complementar é o oposto: o work_mem é por nó e por trabalhador, então um plano com três hash joins e dois trabalhadores pode usar nove vezes o valor configurado. É por isso que elevá-lo globalmente para um número que soa confortável é como servidores ficam sem memória.
5. De onde vêm os números de custo
Em resumo. Um custo é uma estimativa aritmética em unidades de uma leitura sequencial de página. As constantes são configurações que você pode inspecionar, duas delas importam, e o erro mais comum é ler um custo como milissegundos.
Estes são os padrões com que este guia rodou, direto do pg_settings:
| Configuração | Valor | O que ela precifica |
|---|---|---|
seq_page_cost | 1.0 | ler uma página de 8 kB sequencialmente — a unidade em que tudo o mais é medido |
random_page_cost | 4.0 | ler uma página em uma posição aleatória |
cpu_tuple_cost | 0.01 | processar uma linha |
cpu_index_tuple_cost | 0.005 | processar uma entrada de índice |
cpu_operator_cost | 0.0025 | avaliar um operador ou função |
effective_cache_size | 4 GB | quanto cache o planejador supõe existir, para a aritmética de index scans |
work_mem | 4 MB | memória por nó de ordenação ou hash, por trabalhador |
default_statistics_target | 100 | buckets de histograma e valores mais comuns por coluna |
Então um plano que mostra cost=0.42..8.44 afirma que o nó custa mais ou menos como ler oito páginas sequenciais. Não é uma previsão de milissegundos, não pode ser comparado entre servidores, e os custos de dois planos só são comparáveis dentro de um mesmo planejamento.
Duas constantes merecem atenção em produção. O random_page_cost = 4.0 codifica um disco rotacional, em que uma leitura aleatória custava cerca de quatro vezes uma sequencial. Em SSD e NVMe a razão real fica muito mais perto de 1, e o conselho habitual é reduzi-lo (1,1 a 2 é comum), o que deixa o planejador mais disposto a usar índices. O effective_cache_size não é uma alocação — nada é reservado — ele só diz ao planejador qual a chance de buscas repetidas por índice encontrarem páginas em cache; defini-lo em algo entre metade e três quartos da memória do sistema é o ponto de partida normal.
Custo de partida, custo total, e por que o LIMIT muda tudo
Todo nó informa dois custos: o que custa produzir a primeira linha e o que custa produzir todas. O planejador compara os custos totais, a menos que um LIMIT diga que só as primeiras linhas importam, e então o custo de partida passa a dominar. Isso vira planos:
SELECT * FROM orders WHERE status = 'open' ORDER BY created_at LIMIT 10;
Limit (actual time=4.139..4.144 rows=10 loops=1)
-> Index Scan using orders_created_idx on orders (actual rows=10 loops=1)
(cost=0.42..25158.01 rows=90667)
Execution Time: ~4.1 ms
O custo total do index scan é 25.158 — enorme — mas o seu custo de partida é 0,42, e com LIMIT 10 o plano para depois de dez linhas. Remova o LIMIT e a mesma consulta se torna:
Sort (actual time=36.757..44.479 rows=90000 loops=1)
Sort Key: created_at
Sort Method: external merge Disk: 3712kB
-> Bitmap Heap Scan on orders (actual rows=90000 loops=1)
Execution Time: ~44 ms
Os dois planos são escolhas corretas para a pergunta que foi feita. É também o mecanismo por trás de uma surpresa familiar em produção: uma consulta com LIMIT escolhe o plano ordenado por índice e é instantânea enquanto as linhas que casam estão perto do começo do índice, e fica muito lenta quando não estão, porque ela precisa caminhar cada vez mais para achar dez delas.
6. Estimar quantas linhas haverá
Em resumo. O planejador estima a seletividade a partir de estatísticas por coluna: uma lista dos valores mais comuns, um histograma, uma contagem de distintos. Dentro de uma coluna as estimativas são boas. Entre duas colunas ele supõe independência, e é essa suposição que mata planos.
Isto é o que o ANALYZE deixou para a coluna desbalanceada status:
attname | status
n_distinct | 4
most_common_vals | {shipped,open,cancelled,disputed}
most_common_freqs | {0.7980667,0.18133333,0.019433333,0.0011666666}
null_frac | 0
correlation | 0.668676
Quatro valores distintos, cada um com a sua frequência medida, então uma estimativa para status = 'disputed' é 0,00117 × 500.000 = 583. O plano da seção 3 estimou 467 e encontrou 500. Para uma coluna com muitos valores, a lista guarda os 100 mais comuns (que é o default_statistics_target) e um histograma cobre o resto; para customer_id, a saída acima mostra n_distinct = -0.199, em que um número negativo significa "os valores distintos são 19,9% da contagem de linhas" — uma razão em vez de um absoluto, de modo que ela continua correta conforme a tabela cresce.
As estimativas de uma coluna só, a partir disso, costumam errar por poucos por cento. O problema é o que acontece com duas.
A suposição de independência
No conjunto de dados, cada cidade pertence a exatamente um estado: Recife está sempre em PE. Uma pessoa lê city = 'Recife' AND state = 'PE' e vê uma condição. O planejador multiplica duas seletividades:
-- city = 'Recife' alone
Index Only Scan ... (cost=0.29..109.49 rows=5097) (actual rows=5000)
-- city = 'Recife' AND state = 'PE'
Bitmap Heap Scan on customers (cost=58.78..811.23 rows=1027) (actual rows=5000)
A primeira estimativa é excelente: 5.097 contra 5.000. A segunda erra por um fator de cinco — 1.027 contra 5.000 — puramente porque 5.097/100.000 foi multiplicado pela seletividade de state = 'PE', que é 20%. Nada está errado com as estatísticas; o modelo está.
O PostgreSQL 10 e posteriores podem ser avisados da relação:
CREATE STATISTICS customers_city_state (dependencies, ndistinct)
ON city, state FROM customers;
ANALYZE customers;
Bitmap Heap Scan on customers (cost=59.66..811.86 rows=5080) (actual rows=5000)
A estimativa agora é 5.080 contra 5.000. O próprio objeto registra o que aprendeu, legível em pg_stats_ext:
dependencies | {"2 => 3": 1.000000, "3 => 2": 0.049700}
A coluna 2 (city) determina a coluna 3 (state) com coeficiente 1,0 — perfeitamente — enquanto o inverso vale apenas 5% das vezes. Este é um dos recursos de maior valor e menor uso do PostgreSQL: uma correção de uma linha para uma família inteira de planos ruins, em qualquer par de colunas que um leitor chamaria de "obviamente relacionadas" (cidade e estado, país e moeda, modelo e fabricante, status e motivo do status).
Os erros se acumulam para cima
Um erro isolado de 5× no fundo de um plano é sobrevivível. A razão de planos ruins serem geralmente planos profundos é que os erros se multiplicam. Aqui está uma junção de três tabelas em que os dois filtros são levemente mal estimados:
Nested Loop (cost=... rows=22440) (actual rows=40000 loops=3)
-> Hash Join (cost=... rows=7480) (actual rows=13333 loops=3)
-> Parallel Bitmap Heap Scan on orders o (rows=37778) (actual rows=30000 loops=3)
Recheck Cond: (status = 'open')
-> Seq Scan on customers c (rows=19800) (actual rows=20000 loops=3)
Filter: (state = 'PE')
Rows Removed by Filter: 80000
As duas varreduras estão perto. A junção acima delas erra por 1,8×, e a junção acima dessa herda o erro. Neste plano nada quebra, porque os números absolutos continuam pequenos. Com cinco junções a mais, essa mesma deriva é o que transforma uma estimativa de 1.000 linhas em 10.000.000 reais, e nessa altura o planejador já escolheu um nested loop.
7. As estimativas que dão muito errado
Em resumo. Três situações fazem o planejador adivinhar em vez de estimar: um predicado que ele não consegue atravessar, um parâmetro que ele ainda não conhece e um valor fora das estatísticas que ele tem. A primeira é a que você conserta em um minuto, e é a que produz os piores planos.
Um predicado escondido atrás de uma função
Envolver uma coluna em uma função joga as estatísticas no lixo. O planejador tem frequências para status, mas não para upper(status), então ele recorre a um palpite fixo:
-- the planner knows this column
SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped';
Parallel Hash Join (rows=166264) (actual rows=133333 loops=3)
-- the same rows, behind a function call
WHERE upper(o.status) = 'SHIPPED';
Nested Loop (cost=0.29..7585.17 rows=1042) (actual time=0.084..376.922 rows=133333 loops=3)
-> Parallel Seq Scan on orders o (rows=1042) (actual rows=133333 loops=3)
-> Index Only Scan using customers_pkey on customers c (actual rows=1 loops=400000)
Execution Time: 395.759 ms
É o modo de falha inteiro em um plano. A estimativa é de 1.042 linhas contra 400.000 reais, errada por 384×. Com essa estimativa, um nested loop parece barato, então o planejador escolheu um, e o nó interno rodou 400.000 vezes (loops=400000). A consulta que levou 190 ms como hash join levou 396 ms aqui — e só isso porque a tabela interna é minúscula e está em cache. É esse o plano que se torna uma consulta de quatro minutos em dados do tamanho da produção.
A correção é dar ao planejador estatísticas para a expressão, o que um índice sobre a expressão faz como efeito colateral:
CREATE INDEX orders_upper_status_idx ON orders (upper(status));
ANALYZE orders;
Parallel Hash Join (rows=167104) (actual rows=133333 loops=3)
Execution Time: 207.386 ms
A estimativa agora é 167.104 contra 133.333 por trabalhador, o plano voltou a ser um hash join e o tempo voltou ao normal. O mesmo índice de expressão também ajuda diretamente o caso seletivo:
WHERE upper(o.status) = 'DISPUTED'
Index Scan using orders_upper_status_idx on orders o (rows=417) (actual rows=500 loops=1)
Execution Time: 4.000 ms
No PostgreSQL 14 e posteriores você pode obter as estatísticas sem o índice, com CREATE STATISTICS … ON (upper(status)) FROM orders. De um jeito ou de outro, a lição é a regra que todo administrador repete: mantenha as colunas nuas no lado esquerdo de um predicado. WHERE created_at >= '2025-06-01' é estimável; WHERE date_trunc('month', created_at) = '2025-06-01' não é, a menos que você tenha indexado ou analisado essa expressão.
Um valor que as estatísticas não cobrem
Dois parentes próximos do mesmo problema:
- Valores fora da faixa. Estatísticas são um retrato. Depois de uma carga em massa com as linhas de hoje,
created_at >= now() - interval '1 hour'cai além do fim do histograma, e o planejador estima quase nenhuma linha onde há milhões. É o defeito "estava rápido até a carga noturna", e a correção éANALYZEdepois da carga, em vez de esperar o autovacuum. - Chaves de junção desbalanceadas. Uma lista de 100 valores mais comuns descreve bem o desbalanceamento até 100 valores. Além disso, uma chave que aparece em 40% das linhas dentro de uma longa cauda é estimada como se fosse média, e um nested loop é escolhido para 40% da tabela. Elevar
ALTER TABLE … ALTER COLUMN … SET STATISTICS 1000para aquela coluna é a resposta dirigida.
Parâmetros: o plano genérico
Um prepared statement é planejado antes de os valores chegarem. O PostgreSQL planeja as cinco primeiras execuções com os parâmetros reais (um plano personalizado), compara o custo delas com um plano genérico construído com seletividades médias e então, se o genérico não for mais caro, continua a usá-lo. Essa é a troca certa na maioria dos casos — ela economiza tempo de planejamento — e é errada em exatamente um: quando um valor de parâmetro é raro e outro cobre 80% da tabela, como o status aqui. Um plano genérico feito para a média fica então muito errado para os dois.
Sintomas: o mesmo comando é rápido pelo psql e lento pela aplicação, ou rápido para a maioria das entradas e lento para uma. O EXPLAIN (ANALYZE) sobre a consulta com literais não reproduz isso, porque esse é o plano personalizado. Os controles são plan_cache_mode = force_custom_plan (por sessão, ou por função), dividir o comando para cada forma ser planejada separadamente, ou não prepará-lo.
8. Escolher a ordem das junções
Em resumo. O número de ordens possíveis explode fatorialmente, então nenhum planejador examina todas. O PostgreSQL usa programação dinâmica no estilo Selinger até um limite e uma busca genética além dele, e os dois limites são configurações que você pode ver e mover.
Três tabelas podem ser unidas de poucas formas; doze não podem ser enumeradas de forma alguma. A aritmética:
| Tabelas | Ordens à esquerda (n!) | Todas as formas de árvore | Subproblemas de programação dinâmica (2ⁿ) |
|---|---|---|---|
| 2 | 2 | 2 | 4 |
| 3 | 6 | 12 | 8 |
| 4 | 24 | 120 | 16 |
| 5 | 120 | 1.680 | 32 |
| 8 | 40.320 | 17.297.280 | 256 |
| 10 | 3.628.800 | 17.643.225.600 | 1.024 |
| 12 | 479.001.600 | 28.158.588.057.600 | 4.096 |
A terceira coluna é a ideia do artigo do System R de 1979, de Selinger e colegas, que continua sendo a forma de todo planejador relacional: em vez de enumerar ordens, trabalhe para cima sobre subconjuntos de tabelas, guarde apenas o plano mais barato de cada subconjunto (mais o mais barato que por acaso produza uma ordenação útil, as "ordens interessantes" deles) e construa subconjuntos maiores a partir dos menores. Isso transforma um problema fatorial em 2ⁿ, que é tratável até cerca de uma dúzia de tabelas.
O que isso custa na prática, medido planejando uma junção em cadeia sobre um número crescente de tabelas de 1.000 linhas:
| Tabelas na junção | Tempo de planejamento |
|---|---|
| 2 | 0,5 ms |
| 4 | 1,1 ms |
| 6 | 3,6 ms |
| 8 | 19,5 ms |
| 10 | 230,1 ms |
| 12 | 103,9 ms |
O tempo de planejamento cresce de forma acentuada — e então cai com doze tabelas. Não é ruído: o geqo_threshold é 12 por padrão, e com doze relações o PostgreSQL para de fazer a busca exaustiva e roda uma busca genética, trocando qualidade de plano por tempo de planejamento. Desligar isso torna o custo visível:
| Junção de 12 tabelas | Tempo de planejamento (três execuções) |
|---|---|
geqo = on (padrão) | 69 ms, 71 ms, 88 ms |
geqo = off | 1.647 ms, 1.252 ms, 1.350 ms |
Antes disso, porém, vem um segundo par de limites. O join_collapse_limit e o from_collapse_limit (os dois 8) controlam quantas tabelas o planejador vai achatar em um problema de junção. Abaixo do limite, a sintaxe JOIN explícita é tratada como sugestão e o planejador reordena livremente; acima dele, ele para de fundir e planeja as partes separadamente, na ordem escrita. Você pode ver a reordenação colocando o limite em 1:
-- default: the planner joins orders to customers first, then items
Aggregate
-> Nested Loop
-> Hash Join
Hash Cond: (o.customer_id = c.id)
-> Index Scan using orders_status_idx on orders o
-> Hash
-> Bitmap Heap Scan on customers c
-> Index Only Scan using items_order_idx on items i
-- join_collapse_limit = 1: the written order is kept
Aggregate
-> Hash Join
Hash Cond: (o.customer_id = c.id)
-> Nested Loop
-> Index Scan using orders_status_idx on orders o
-> Index Only Scan using items_order_idx on items i
-> Hash
-> Bitmap Heap Scan on customers c
Os dois planos respondem à mesma consulta. O primeiro filtra orders e customers antes de tocar a tabela items, de 1,5 milhão de linhas, que é o que você teria escolhido à mão.
Três consequências práticas:
- Escrever as junções em uma ordem esperta normalmente não consegue nada, porque o planejador as reordena de todo jeito. Abaixo do limite de achatamento, a ordem das junções no SQL é documentação, não instrução.
- Uma consulta de relatório com vinte tabelas é um problema de planejamento, não apenas de execução. Quando o tempo de planejamento em si é a reclamação, as respostas são dividir a consulta em etapas (uma tabela temporária ou um CTE materializado para a primeira junção), reduzir o número de tabelas em um comando, ou aceitar um plano pior da busca genética.
join_collapse_limit = 1é um instrumento bruto que funciona. É como você fixa uma ordem de junção que provou ser melhor, e ele pertence a uma sessão ou a uma função, não à configuração do servidor.
9. O que acontece antes do custeio: as reescritas
Em resumo. Antes de qualquer plano ser custeado, a consulta é reescrita em uma forma equivalente, normalmente mais simples. Saber quais reescritas acontecem diz quais otimizações manuais são inúteis e quais formas de SQL realmente travam o planejador.
As reescritas com que você pode contar em um planejador moderno:
| Reescrita | O que faz | Por que importa para você |
|---|---|---|
| Expansão de visões e funções embutíveis | o corpo da visão substitui o nome dela | predicados de fora podem então ser empurrados para dentro da visão |
| Empurrar predicados | um filtro se move o mais perto possível da varredura | um WHERE sobre o resultado de uma subconsulta pode se tornar condição de índice |
| Achatamento de subconsultas | IN (SELECT …) se torna semijunção; NOT EXISTS se torna antijunção | os algoritmos e as ordens de junção da seção 8 passam a estar disponíveis; IN e EXISTS normalmente planejam igual |
| Dobragem de constantes | WHERE total_cents > 100 * 5 se torna > 500 | expressões sobre constantes não custam nada em tempo de execução |
| Redução de força de junção | uma junção externa cujas linhas nulas são filtradas se torna interna | mais ordens de junção se tornam legais |
| Embutir CTEs | um CTE não recursivo usado uma vez é tratado como subconsulta (PostgreSQL 12 e posteriores) | o famoso conselho de que "CTEs são barreiras de otimização" está obsoleto |
Vale ver esse último, porque o comportamento antigo moldou uma geração de conselhos:
WITH recent AS (SELECT * FROM orders WHERE created_at >= '2025-06-01')
SELECT count(*) FROM recent WHERE status = 'open';
Aggregate
-> Bitmap Heap Scan on orders
Recheck Cond: (status = 'open')
Filter: (created_at >= '2025-06-01')
-> Bitmap Index Scan on orders_status_idx
O CTE desapareceu: os dois predicados foram aplicados a uma varredura, e o planejador escolheu o índice em status em vez daquele que o corpo do CTE sugeria. Acrescentar MATERIALIZED traz a barreira de volta:
WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at >= '2025-06-01')
SELECT count(*) FROM recent WHERE status = 'open';
Aggregate
CTE recent
-> Bitmap Heap Scan on orders
Recheck Cond: (created_at >= '2025-06-01')
Os dois são legítimos. Embutir costuma ser mais rápido; MATERIALIZED é a escolha deliberada quando um CTE é caro e usado várias vezes, ou quando você quer impedir o planejador de empurrar um predicado para onde não deveria — uma barreira feita à mão, com o custo de o resultado intermediário ser construído por inteiro.
10. O que o executor faz com o plano
Em resumo. Um plano é uma árvore de operadores que puxam linhas uns dos outros, uma por vez. O modelo é simples e geral, e custa por linha, e é por isso que motores analíticos trabalham em lotes, que o PostgreSQL compila expressões para consultas caras, e que trabalhadores paralelos ajudam varreduras mais do que ajudam buscas pequenas.
O modelo de iteradores. Todo nó de um plano do PostgreSQL implementa a mesma interface: abrir, produzir a próxima linha, fechar. Um Sort pede linhas ao filho até não haver mais, ordena-as e as entrega para cima, uma a uma. O trabalho de Graefe sobre o Volcano é onde esse desenho está descrito, e a sua virtude é que qualquer operador se compõe com qualquer outro, que é como um motor suporta SQL arbitrário. O seu custo é a sobrecarga por linha: algumas chamadas de função e uma desmontagem de tupla para cada linha em cada nível, o que quase não importa para uma busca de 10 linhas e importa muito para uma agregação de 500 milhões.
Execução em lotes e vetorizada é a resposta que os sistemas analíticos escolheram: passar um bloco de alguns milhares de valores entre operadores em vez de uma linha, manter as colunas contíguas para que os laços fiquem compactos e previsíveis, e deixar a CPU trabalhar sobre muitos valores por desvio. DuckDB, ClickHouse, Snowflake e os motores colunares são construídos assim; o PostgreSQL não é, e essa é uma razão honesta de uma agregação de 100 milhões de linhas ser mais rápida no DuckDB que no PostgreSQL, e de uma busca de uma linha por chave primária não ser.
Compilação é a outra resposta: gerar código de máquina para as expressões desta consulta em particular, em vez de interpretar uma árvore. O PostgreSQL faz isso com LLVM para consultas caras, e os padrões importam mais que o recurso. Forçá-lo para uma consulta que não precisa:
SET jit = on; SET jit_above_cost = 0; SET jit_optimize_above_cost = 0; SET jit_inline_above_cost = 0;
SELECT sum(total_cents), avg(total_cents), max(created_at) FROM orders WHERE total_cents > 1000;
JIT:
Functions: 17
Timing: Generation 3.1 ms, Inlining 618.5 ms, Optimization 285.3 ms, Emission 288.5 ms, Total 1195.5 ms
Execution Time: 2338.5 ms
SET jit = off; -- the same query
Execution Time: 66.6 ms
Compilar 17 funções levou 1,2 segundo para acelerar uma consulta que roda em 67 milissegundos, e o resultado foi 35 vezes mais lento. É por isso que o jit_above_cost tem o padrão 100.000: a compilação só compensa quando a execução domina. Se você vê tempos de JIT em um plano cuja execução é curta, isso é má configuração, não otimização — e em uma carga de muitas consultas pequenas, jit = off é um padrão defensável.
Consulta paralela aparece nos planos deste guia como loops=3 com Workers Planned: 2: o líder e dois trabalhadores varrem cada um uma parte da tabela, e um nó Gather junta a saída deles. O paralelismo ajuda varreduras sequenciais, hash joins e agregações sobre muitas linhas; não faz nada por um plano cujo custo é uma busca por índice, e o max_parallel_workers_per_gather (2 por padrão) o limita. Note que cada trabalhador recebe o seu próprio work_mem, que é a armadilha de dimensionamento de memória da seção 4.
11. Estatísticas na prática
Em resumo. Tudo nas seções 6 e 7 depende de as estatísticas estarem frescas e detalhadas o bastante. Três hábitos cobrem quase tudo: analisar após mudanças em massa, elevar o alvo nas colunas que importam e criar estatísticas estendidas para pares correlacionados.
| O que fazer | Quando | Por quê |
|---|---|---|
ANALYZE tabela | depois de uma carga em massa, de uma grande exclusão ou de uma nova partição | o limiar padrão do autovacuum é 10% da tabela mais 50 linhas, então uma carga que fica em 9% é invisível, e o limite superior do histograma fica desatualizado exatamente para as consultas sobre as linhas mais novas |
ALTER TABLE … ALTER COLUMN … SET STATISTICS 1000 | em chaves de junção desbalanceadas e colunas de filtro com longa cauda | os 100 valores mais comuns padrão não descrevem desbalanceamento espalhado por milhares de valores |
CREATE STATISTICS … (dependencies, ndistinct) | em pares de colunas que uma pessoa chamaria de relacionadas | corrige a suposição de independência, que é a causa mais comum de uma estimativa 10× errada (seção 6) |
CREATE STATISTICS … ON (expressão) | quando um predicado precisa envolver uma coluna em uma função | dá estatísticas próprias à expressão sem um índice (PostgreSQL 14 e posteriores) |
VACUUM | regularmente, via autovacuum | linhas mortas inflam a tabela, e o mapa de visibilidade é o que torna possíveis os index-only scans: Heap Fetches: 0 depende dele (MVCC e isolamento) |
Dois números para guardar. default_statistics_target = 100 significa 100 valores mais comuns e 100 buckets de histograma por coluna, amostrados de aproximadamente 30.000 linhas — esse tamanho de amostra é a razão de as estatísticas de uma tabela de um bilhão de linhas serem aproximadas por projeto. E o limiar de análise do autovacuum é proporcional, então quanto maior a tabela, mais tempo leva para ele notar que um décimo dela mudou.
Uma forma rápida de ver se as estatísticas são o seu problema:
SELECT relname, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 10;
Se o n_mod_since_analyze for uma fração grande do n_live_tup para a tabela da sua consulta lenta, pare de ler planos e rode ANALYZE primeiro.
12. Uma escada para diagnosticar um plano ruim
Em resumo. Trabalhe da verificação mais barata e reversível para a mais invasiva. Nove vezes em dez você para nos quatro primeiros degraus, e a única coisa que diz onde parar é a razão entre estimativa e realidade.
| Degrau | Verificação | Como | Se for isso |
|---|---|---|---|
| 1 | Esta consulta é realmente o problema? | pg_stat_statements por tempo total, não só médio; o tempo está no planejamento, na execução ou esperando uma trava? | conserte a coisa certa; uma espera por trava não é problema do planejador |
| 2 | Qual é o plano real? | EXPLAIN (ANALYZE, BUFFERS); para um prepared statement use o auto_explain ou prepare você mesmo, porque a consulta com literais dá outro plano | agora você tem estimativas e valores reais lado a lado |
| 3 | Onde a estimativa erra primeiro? | leia de baixo para cima, ache o nó mais profundo em que rows= e actual rows diferem por mais de cerca de 10×, lembrando de multiplicar por loops | tudo acima daquele nó foi escolhido com informação ruim |
| 4 | As estatísticas estão desatualizadas? | n_mod_since_analyze contra n_live_tup em pg_stat_user_tables | rode ANALYZE na tabela e verifique de novo. Grátis, imediato, sem risco |
| 5 | Dois predicados sobre colunas relacionadas? | o nó mal estimado filtra por duas colunas de uma tabela | CREATE STATISTICS … (dependencies, ndistinct) e ANALYZE (seção 6) |
| 6 | Uma coluna está envolvida em uma função? | olhe as linhas Filter e Index Cond procurando upper(…), date_trunc(…), uma conversão, uma concatenação | reescreva para manter a coluna nua, ou indexe a expressão (seção 7) |
| 7 | O índice está errado, em vez de faltando? | um índice cobre as colunas do predicado em uma ordem utilizável; a consulta busca colunas que forçam acesso à tabela | um índice composto ou de cobertura (seção 3); meça, não acrescente por fé |
| 8 | É um comando sensível a parâmetros? | rápido com literais, lento pela aplicação, ou lento para uma entrada | plan_cache_mode = force_custom_plan, ou dividir o comando (seção 7) |
| 9 | É memória? | Batches: 64, Sort Method: external merge Disk: … | eleve o work_mem naquela sessão ou naquele comando, não globalmente (seção 4) |
| 10 | O planejador está sem espaço? | muitas tabelas em um comando, Planning Time grande | divida a consulta, ou fixe a ordem com join_collapse_limit = 1 (seção 8) |
| 11 | Force | SET enable_nestloop = off e companhia, ou pg_hint_plan | último recurso: registre o motivo e revise depois de cada atualização maior |
Os degraus 1 a 6 são gratuitos e reversíveis. O degrau 7 acrescenta custo de escrita em cada inserção. Os degraus 10 e 11 prendem um comportamento que o planejador melhoraria sozinho, e envelhecem mal: um plano fixado em 2019 é um passivo em um servidor de 2026 com outros dados.
Os interruptores enable_* merecem uma nota própria. Eles são ferramentas de diagnóstico, não configuração: desligar o enable_hashjoin em uma sessão é como você pergunta "quanto a alternativa teria custado?", que é exatamente como a comparação de 190 ms contra 397 ms da seção 4 foi produzida. Deixar um deles desligado no postgresql.conf remove um algoritmo de todas as consultas do servidor.
13. O que você controla, e o que não controla
Em resumo. No PostgreSQL você dirige o planejador dando a ele informação melhor, índices melhores ou outra consulta; você não pode dar um hint no núcleo. Isso é uma posição deliberada de projeto, e existe uma extensão muito usada para quem discorda.
| Alavanca | O que faz | Quando usar |
|---|---|---|
Estatísticas (ANALYZE, SET STATISTICS, CREATE STATISTICS) | muda a estimativa, então o planejador escolhe diferente por conta própria | sempre primeiro: a correção sobrevive a atualizações e ao crescimento dos dados |
| Índices, inclusive de expressão e de cobertura | muda quais caminhos de acesso existem | quando o problema é o caminho de acesso, não a estimativa |
| Reescrever a consulta | remove um predicado não estimável, um DISTINCT desnecessário, uma subconsulta correlacionada que não pode ser achatada | quando a forma do SQL está bloqueando uma reescrita da seção 9 |
Barreiras de otimização (MATERIALIZED, OFFSET 0) | impede o planejador de fundir uma subconsulta no pai | quando você provou que a fusão é o problema |
Parâmetros de planejamento (work_mem, random_page_cost, effective_cache_size, jit) | muda a aritmética para todas as consultas, ou para uma sessão | random_page_cost e effective_cache_size uma vez por servidor, em armazenamento moderno; o resto por sessão |
join_collapse_limit, enable_* | fixa uma ordem de junção ou remove um algoritmo | diagnóstico, e como correção de último recurso em uma função |
pg_hint_plan | acrescenta hints no estilo Oracle em um comentário: /*+ HashJoin(a b) IndexScan(a idx) */ | quando você precisa de um plano fixado e não pode mudar a consulta; uma extensão, não o núcleo |
Vale entender, em vez de apenas reclamar, a recusa do PostgreSQL em colocar hints no núcleo: um hint escrito hoje é aplicado para sempre, inclusive depois que a distribuição dos dados muda, as estatísticas melhoram e o planejador ganha um algoritmo melhor. A posição do projeto é que o planejador deve ser corrigido ou informado. O contra-argumento — que uma indisponibilidade em produção às 2 da manhã é um péssimo momento para corrigir um planejador — é por que o pg_hint_plan existe e por que todo motor comercial tem hints e gestão de planos.
Ferramentas que vale ter antes de precisar: pg_stat_statements (quais consultas custam mais no agregado), auto_explain com log_min_duration e log_analyze (o plano real da execução lenta, inclusive o plano genérico que um prepared statement de fato usou), EXPLAIN (FORMAT JSON) para processamento por máquina, e um visualizador de planos como o pev2 ou o explain.depesz.com para planos grandes demais para ler como texto.
14. As mesmas ideias em outros motores
Em resumo. Todo motor relacional popular é baseado em custo, com estatísticas por coluna e os mesmos três algoritmos de junção. O que difere é quanto controle você recebe e quanto o sistema se corrige em tempo de execução.
| Estatísticas | Hints e controle de plano | Notável | |
|---|---|---|---|
| PostgreSQL | valores mais comuns e histograma por coluna; estatísticas estendidas para correlação e expressões | nenhum no núcleo; extensão pg_hint_plan; interruptores enable_* | mostra o seu raciocínio de graça; planos personalizados contra genéricos para prepared statements |
| MySQL 8 / InnoDB | estatísticas de índice mais histogramas opcionais; constantes de custo em tabelas | hints de otimizador em /*+ … */, hints de índice (FORCE INDEX), optimizer_switch | ganhou hash join e EXPLAIN ANALYZE no 8.0.18 |
| SQL Server | estatísticas criadas e atualizadas automaticamente, multicoluna | hints de consulta, plan guides, Query Store com planos forçados | junções adaptativas e execução intercalada; otimização sensível a parâmetros no 2022 |
| Oracle | histogramas, estatísticas estendidas, amostragem dinâmica | um vocabulário grande de hints, baselines de SQL Plan Management | planos adaptativos e retorno de estatísticas: ele pode corrigir um plano em uma reexecução |
| DuckDB, ClickHouse | estatísticas leves; disposição colunar | pouco a ajustar | execução vetorizada e poda de colunas fazem a maior parte do trabalho (seção 10) |
| BigQuery, Snowflake | mantidas pelo serviço | praticamente nenhum | reotimização dinâmica e poda de partições ou micropartições em vez de índices |
Qual carga pertence a qual deles é outra pergunta, e tem o seu próprio guia: escolher um banco de dados.
Duas generalizações valem para todos. O erro de estimativa, e não a busca, é a causa habitual de um plano ruim — o estudo de Leis testou isso em vários sistemas. E quanto mais profundo o plano, pior ficam as estimativas, e é por isso que todo motor está acrescentando alguma forma de correção em tempo de execução: junções adaptativas, retorno de estatísticas, reotimização entre etapas.
15. Erros comuns
- Ler
cost=como milissegundos, ou comparar custos entre dois servidores. - Olhar o
EXPLAINsemANALYZEe concluir que a consulta está bem; a estimativa é justamente o que está sob suspeita. - Ignorar o
loops=e, assim, ler um nó que rodou 400.000 vezes como um nó rápido. - Comparar
rows=com a contagem final do resultado, em vez de com oactual rowsdo mesmo nó. - Acrescentar um índice porque uma varredura parece lenta, sem verificar se a estimativa estava errada antes.
- Envolver uma coluna em uma função no
WHEREe então se perguntar por que o índice é ignorado e a estimativa é 1.042 em vez de 400.000. - Rodar
EXPLAIN ANALYZEna consulta com literais para depurar um prepared statement lento, e ver um plano que a aplicação nunca usou. - Elevar o
work_memglobalmente para um número confortável, esquecendo que ele é por nó e por trabalhador. - Deixar
enable_nestloop = offna configuração do servidor depois de uma sessão de depuração. - Confiar em um plano obtido em um banco de homologação com um décimo das linhas e outras estatísticas.
- Esperar que o
ANALYZEseja gratuito em uma tabela enorme, ou que o autovacuum tenha notado uma carga em massa de 9%. - Tratar um CTE como barreira de otimização no PostgreSQL 12 ou posterior, ou supor que
MATERIALIZEDé sempre mais lento. - Forçar o JIT para tudo, e pagar um segundo de compilação por uma consulta de 67 ms.
- Citar uma tabela de tempos de um guia como este como se ela previsse o seu servidor.
16. O que mencionar em uma entrevista
- Um planejador faz três trabalhos: caminho de acesso, algoritmo de junção, ordem das junções. Ele precisa de um modelo de custo e de uma estimativa de cardinalidade, e o modelo de custo quase nunca é o que está errado.
- O hábito que encontra o defeito: ler o plano de baixo para cima e comparar
rows=comactual rows × loopsem cada nó. O nó mais profundo em que eles divergem é a causa. - Quatro caminhos de acesso: sequencial, índice, index-only, bitmap. O bitmap scan existe para transformar acesso aleatório à tabela em uma passagem ordenada, e é por isso que a preferência caminha de índice para bitmap e para sequencial conforme o resultado cresce.
- Nested loop, hash join, merge join, e as formas de custo deles. Um nested loop escolhido com uma subestimativa de 384× é o desastre clássico em produção, e o
loops=do plano é onde você o vê. - Custos estão em unidades de uma leitura sequencial de página.
random_page_cost = 4supõe um disco rotacional; em SSDs as pessoas o reduzem para tornar os index scans mais atraentes. - Custo de partida contra custo total é por que o
LIMITmuda o plano, e por que "coloque um LIMIT" pode transformar uma consulta de 44 ms em uma de 4 ms — ou em uma muito lenta, quando as linhas que casam estão tarde no índice. - As estimativas são boas por coluna e ruins entre colunas, porque as seletividades são multiplicadas sob uma suposição de independência. O
CREATE STATISTICScomdependenciescorrige um par correlacionado, e é o truque de maior valor que a maioria das equipes nunca usou. upper(col) = 'X'joga as estatísticas no lixo. Mantenha as colunas nuas, ou indexe a expressão — o que também cria estatísticas para ela.- A ordenação de junções é o algoritmo de programação dinâmica do System R, de 1979: 2ⁿ subconjuntos em vez de n! ordens, guardando o plano mais barato por subconjunto mais o mais barato com uma ordenação útil. O PostgreSQL troca para uma busca genética com doze relações, e o
join_collapse_limitdecide quanto ele achata antes. - Prepared statements podem acabar em um plano genérico, e é por isso que um comando é rápido no
psqle lento pela aplicação. - O modelo de iteradores custa por linha; lotes e vetorização são o que os motores analíticos fazem no lugar, e a compilação (JIT) só compensa quando a execução domina o planejamento e a compilação.
- E a resposta de julgamento: antes de acrescentar um índice ou um hint, descubra se o planejador estava errado ou mal informado. Mal informado é barato de corrigir e fica corrigido.
Resumo rápido
- O planejador otimiza uma estimativa dos seus dados, não a sua consulta; a maioria dos planos ruins são estimativas ruins.
- Leia planos de baixo para cima e compare linhas estimadas com reais, multiplicadas por
loops. - Quatro caminhos de acesso e três algoritmos de junção; a escolha entre eles é guiada quase inteiramente por contagens estimadas de linhas.
- Custos são aritmética em unidades de uma leitura sequencial de página, então só são comparáveis dentro de um mesmo plano.
- O
LIMITmuda qual plano ganha, porque o custo de partida passa a importar mais que o total. - Estatísticas por coluna são boas; a suposição de independência entre colunas é a falha comum, e estatísticas estendidas a corrigem.
- Uma função em volta de uma coluna remove as estatísticas dela; um índice de expressão ou estatísticas de expressão as trazem de volta.
- A ordem das junções é buscada com programação dinâmica até um limite e depois geneticamente; o tempo de planejamento é um custo real em consultas largas.
- As reescritas acontecem antes do custeio: CTEs são embutidos, subconsultas são achatadas, predicados são empurrados, então arrumar formas de SQL à mão raramente ajuda.
- O executor puxa linhas uma a uma; lotes, vetorização, compilação e trabalhadores paralelos são as formas de contornar o custo por linha.
- Corrija estimativas antes de acrescentar índices, e acrescente índices antes de forçar planos.
Apêndice: o conjunto de dados e os experimentos
Tudo o que foi medido neste guia veio de uma instância PostgreSQL 16.15 com configurações padrão, salvo onde uma linha diz o contrário, e de um conjunto de dados construído para ter três propriedades que um planejador acha difíceis: uma coluna desbalanceada, um par de colunas correlacionadas e uma coluna cuja seletividade pode ser variada suavemente.
-- 100,000 customers across 20 cities; each city belongs to exactly one state,
-- so city and state are perfectly correlated (section 6).
CREATE TABLE customers (id int PRIMARY KEY, city text, state text, segment text);
-- 500,000 orders with a deliberately skewed status column:
-- shipped 80%, open 18%, cancelled 1.9%, disputed 0.1% (section 3)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int REFERENCES customers(id),
status text, total_cents int, created_at timestamptz);
-- 1,500,000 items, three per order, to give the join-order section something large
CREATE TABLE items (order_id int REFERENCES orders(id), sku text, qty int);
Os experimentos, na ordem em que o guia os cita:
EXPLAIN (ANALYZE, BUFFERS)de uma busca por chave primária, para a anatomia do plano.- A mesma coluna em quatro seletividades, estimativa contra real.
- Uma faixa de datas que se alarga, contando linhas e depois buscando-as, para achar onde cada caminho de acesso ganha.
city = 'Recife' AND state = 'PE'antes e depois doCREATE STATISTICS.- A mesma junção em duas seletividades, para o nested loop e o hash join.
- A mesma consulta com
enable_hashjoin = off, para precificar o algoritmo errado. - A mesma junção em quatro valores de
work_mem, para osBatches. - Um índice de cobertura, para o
Heap Fetches: 0. - Uma consulta com e sem
LIMIT 10. - Uma junção de três tabelas com e sem
join_collapse_limit = 1. - Uma junção de três tabelas com
ANALYZE, para ver a estimativa derivar para cima. pg_statspara três colunas deorders.- Um CTE, embutido e
MATERIALIZED. - Uma agregação com JIT forçado ligado e desligado.
- Uma junção em cadeia sobre 2 a 12 tabelas, para o tempo de planejamento, e a mesma junção de 12 tabelas com
geqodesligado.
Cada um é um único EXPLAIN, então a forma de usar este apêndice é reexecutá-lo nos seus próprios dados: os planos serão diferentes, e as diferenças são a parte interessante.
Fontes
- P. Griffiths Selinger e colegas, Access Path Selection in a Relational Database Management System (SIGMOD 1979) — o modelo de custo, as ordens interessantes e a busca de junções por programação dinâmica que todo planejador ainda usa.
- Goetz Graefe, Volcano: An Extensible and Parallel Query Evaluation System (IEEE TKDE 1994) — o modelo de iteradores para execução.
- Goetz Graefe e William McKenna, The Volcano Optimizer Generator (ICDE 1993), o ancestral do framework Cascades usado pelo SQL Server e outros.
- Viktor Leis e colegas, How Good Are Query Optimizers, Really? (VLDB 2015) — o estudo que isola a estimativa de cardinalidade como a fonte dominante de planos ruins.
- Surajit Chaudhuri, An Overview of Query Optimization in Relational Systems (PODS 1998).
- Documentação do PostgreSQL: Using EXPLAIN, Planner Statistics, Extended Statistics, Planner Cost Constants e Genetic Query Optimizer.
- Peter Bailis, Joseph M. Hellerstein e Michael Stonebraker (editores), Readings in Database Systems, 5th Edition — o capítulo de otimização de consultas põe esses artigos em contexto e é a razão de este guia existir.
Pratique este tema: Databases