Arquitetura dos Dados

Poucas situações são tão frustrantes quanto esta.

Você identifica uma consulta lenta, cria um índice cuidadosamente planejado, executa novamente o mesmo SQL e percebe que absolutamente nada mudou. O tempo de resposta continua praticamente o mesmo e, quando finalmente abre o EXPLAIN, encontra exatamente aquilo que esperava não ver: um Sequential Scan. O índice existe, foi criado corretamente, está íntegro, mas o PostgreSQL simplesmente decidiu ignorá-lo.

Quase todo desenvolvedor passa por essa experiência em algum momento da carreira. A reação costuma ser previsível. Primeiro vem a dúvida sobre a criação do índice. Depois surge a suspeita de algum problema no banco de dados. Em seguida aparecem perguntas em fóruns, vídeos e blogs tentando explicar por que o PostgreSQL “não usa índices”.

Curiosamente, essa própria pergunta parte de uma premissa equivocada.

O PostgreSQL nunca teve como objetivo utilizar índices.

Essa afirmação costuma causar estranhamento porque passamos boa parte da nossa vida profissional ouvindo que índices existem para acelerar consultas. Embora isso seja verdade, ela esconde um detalhe importante: índices são apenas uma das várias estratégias disponíveis para responder uma consulta. O verdadeiro objetivo do banco não é utilizá-los, mas responder ao SQL consumindo a menor quantidade possível de recursos.

Essa mudança de perspectiva parece pequena, mas transforma completamente a maneira como enxergamos o processo de execução de uma consulta. Quando entendemos que o planner está tentando minimizar trabalho — e não demonstrar preferência por determinada estrutura de dados — várias decisões que antes pareciam misteriosas passam a fazer sentido.

O interessante é que essa ideia nem sequer nasceu no PostgreSQL.

A origem de uma ideia que ainda domina os bancos modernos

Durante boa parte da década de 1970, pesquisadores tentavam responder uma pergunta que parecia simples: como um banco de dados deveria decidir a melhor forma de executar uma consulta?

Naquela época, sistemas relacionais ainda eram relativamente novos. A linguagem SQL começava a ganhar espaço, mas havia um problema fundamental. Um comando como:

SELECT *
FROM pedidos
WHERE cliente_id = 125;

não descreve como os dados devem ser encontrados.

Ele apenas descreve o resultado desejado.

Isso representa uma ruptura importante em relação a outras formas de programação. Quando escrevemos um algoritmo em uma linguagem imperativa, normalmente descrevemos passo a passo tudo o que precisa acontecer. O SQL funciona de outra maneira. Ele é declarativo. Informamos o que queremos, deixando para o banco de dados a responsabilidade de descobrir como chegar até aquele resultado.

Hoje isso parece natural, mas, no final da década de 1970, essa era uma questão aberta. Como um banco deveria escolher entre dezenas de estratégias possíveis?

Em 1979, pesquisadores da IBM liderados por Patricia Selinger publicaram um artigo que mudaria definitivamente essa discussão: Access Path Selection in a Relational Database Management System. O trabalho propunha abandonar regras fixas e substituí-las por algo muito mais sofisticado: um otimizador baseado em custo.

A ideia era elegante.

Em vez de decidir que “índices sempre são melhores” ou que “leituras sequenciais devem ser evitadas”, o banco passaria a estimar quanto trabalho cada estratégia exigiria. A melhor resposta deixaria de ser determinada por regras e passaria a ser escolhida por comparação.

Quase meio século depois, praticamente todos os grandes bancos relacionais seguem exatamente esse princípio.

PostgreSQL, SQL Server, Oracle e MySQL possuem implementações diferentes, mas compartilham a mesma filosofia introduzida naquele artigo.

Antes de executar qualquer consulta, o banco tenta responder uma única pergunta:

Perceba a palavra utilizada.

Provavelmente.

O planner não possui uma bola de cristal.

Ele também não executa todas as estratégias possíveis para descobrir qual delas é mais rápida. Isso seria absurdamente caro e tornaria o próprio processo de otimização mais lento que a consulta.

Em vez disso, ele trabalha da mesma forma que um engenheiro costuma trabalhar quando precisa tomar decisões complexas. Ele faz estimativas.

O planner trabalha com probabilidades, não com certezas

Talvez essa seja uma das ideias mais importantes para compreender qualquer banco de dados moderno. Quando vemos um plano de execução, é tentador imaginar que o banco “sabe” exatamente quantos registros serão retornados por uma consulta. Ele não sabe.

Na verdade, antes da execução começar, ninguém sabe.

Tudo o que o planner possui são informações suficientes para construir uma aproximação razoavelmente boa da realidade. Isso significa que praticamente toda decisão tomada pelo otimizador é baseada em probabilidades.

Ele tenta estimar quantas linhas determinada condição deverá retornar. Depois estima quantas páginas precisarão ser carregadas para memória. Em seguida calcula aproximadamente quanto trabalho será realizado pela CPU e quanto custará acessar o armazenamento. A partir dessas informações, compara diferentes planos de execução até encontrar aquele cujo custo estimado parece menor.

Perceba que, nesse momento, nenhum dado foi efetivamente consultado.

O banco está apenas raciocinando.

Essa talvez seja a característica mais fascinante do planner: antes de ler a primeira linha de uma tabela, ele já construiu um pequeno modelo mental sobre aquilo que acredita que encontrará.

Naturalmente, esse modelo só funciona porque existe uma enorme quantidade de informações sendo coletadas continuamente sobre os próprios dados.

O banco de dados conhece muito mais sobre suas tabelas do que imaginamos

Muita gente acredita que o comando ANALYZE apenas atualiza estatísticas genéricas sobre uma tabela.

Na realidade, o que acontece é bem mais interessante.

Quando executa um ANALYZE — seja manualmente ou por meio do autovacuum — o PostgreSQL coleta uma amostra dos registros e constrói um retrato estatístico bastante detalhado daquele conjunto de dados. Dependendo do tipo da coluna, ele estima quantos valores distintos existem, identifica os valores mais frequentes, cria histogramas aproximados da distribuição dos dados e registra informações capazes de alimentar o modelo de custo utilizado pelo planner.

Essas estatísticas representam muito mais do que simples contagens. Elas são a matéria-prima utilizada pelo banco para responder perguntas como:

• Quantas linhas provavelmente satisfazem esta condição?
• Vale a pena utilizar um índice?
• Quantas páginas precisarão ser acessadas?
• É provável que esses dados já estejam em memória?
• Esta junção produzirá poucos registros ou milhões deles?

Sem essas estimativas, o planner seria obrigado a tomar decisões praticamente às cegas. É justamente por isso que estatísticas desatualizadas costumam produzir planos ruins. O problema quase nunca está no algoritmo do planner, o problema está no retrato que ele possui da realidade.

Até aqui, falamos várias vezes em “estatísticas”, mas ainda não respondemos uma pergunta importante: que tipo de estatísticas são essas? Afinal, para decidir entre um Index Scan e um Sequential Scan, o PostgreSQL precisa estimar quantas linhas uma consulta retornará. Essa estimativa é muito mais sofisticada do que simplesmente contar registros.

Considere uma tabela de clientes contendo a coluna estado. À primeira vista, poderíamos imaginar que uma consulta procurando clientes do estado de São Paulo retornará aproximadamente um vinte e sete avos da tabela, já que o Brasil possui vinte e sete unidades federativas. Na prática, qualquer pessoa percebe que essa distribuição dificilmente será uniforme. São Paulo concentra muito mais registros do que estados menos populosos. Se o planner assumisse uma distribuição perfeitamente equilibrada, praticamente todas as estimativas seriam incorretas.

É justamente para evitar esse tipo de erro que o PostgreSQL mantém informações como os Most Common Values (MCVs), uma lista contendo os valores que aparecem com maior frequência em cada coluna analisada. Se a consulta procura exatamente um desses valores, o planner não precisa trabalhar com médias; ele já possui uma estimativa muito mais próxima da realidade.

Quando o valor pesquisado não faz parte dessa lista, entra em cena outra estrutura igualmente importante: os histogramas. Em vez de armazenar todos os valores presentes na tabela — o que seria inviável — o banco divide a distribuição dos dados em intervalos capazes de representar aproximadamente como aqueles registros estão espalhados. Essa aproximação permite estimar, por exemplo, quantos pedidos provavelmente possuem uma data entre janeiro e março ou quantos produtos têm preço superior a determinado valor.

Há ainda outra informação que costuma passar despercebida, mas exerce enorme influência sobre o plano de execução: a cardinalidade.

É comum encontrar textos utilizando cardinalidade e seletividade como sinônimos, mas elas descrevem aspectos diferentes dos dados. A cardinalidade representa, de maneira simplificada, a quantidade de valores distintos existentes em uma coluna. Um CPF possui cardinalidade extremamente alta, porque praticamente cada linha contém um valor diferente. Já uma coluna contendo apenas “Ativo” e “Inativo” possui cardinalidade muito baixa. A seletividade, por outro lado, mede o quanto uma condição consegue reduzir o conjunto de resultados.

Esses conceitos costumam caminhar juntos, mas não são exatamente a mesma coisa. Uma coluna de alta cardinalidade normalmente produz consultas bastante seletivas. Procurar um CPF específico tende a retornar apenas um registro. Já consultar uma coluna cujo valor é compartilhado por milhões de linhas dificilmente reduzirá o conjunto de dados de maneira significativa. É justamente por isso que índices sobre colunas de baixa cardinalidade costumam ser ignorados com frequência. O problema não está no índice. O problema é que ele deixou de representar um ganho real em relação à leitura sequencial da tabela.

Nesse ponto, vale observar algo interessante. O planner não tenta descobrir exatamente quantos registros serão retornados. Isso seria impossível sem executar a própria consulta. O que ele procura é uma aproximação suficientemente boa para comparar estratégias diferentes.

Essa diferença é fundamental. O planner não precisa acertar o número exato de linhas. Ele precisa apenas estimar corretamente qual estratégia tende a custar menos.

O custo que aparece no EXPLAIN não representa tempo

Essa talvez seja uma das interpretações equivocadas mais comuns sobre planos de execução.

Quando executamos um comando como:

EXPLAIN
SELECT *
FROM pedidos
WHERE status = 'ENTREGUE';

é comum encontrar uma saída semelhante a esta:

Seq Scan on pedidos
(cost=0.00..18452.33 rows=978231 width=84)

Muita gente olha imediatamente para o Seq Scan, ignorando completamente o restante da linha. Curiosamente, o planner faz exatamente o contrário. Para ele, a informação mais importante não é o tipo de operação escolhida, mas o custo estimado que aparece entre parênteses.

Esse custo, entretanto, costuma gerar uma interpretação equivocada, ele não representa milissegundos, também não representa segundos. Na verdade, ele não representa nenhuma unidade física de tempo. O banco de dados trabalha com uma espécie de moeda interna, utilizada exclusivamente para comparar planos de execução. Em vez de medir quanto tempo uma consulta realmente levará para terminar, o planner calcula uma estimativa abstrata da quantidade de trabalho envolvida em cada estratégia.

Essa decisão foi extremamente inteligente.

Se o planner trabalhasse diretamente com tempo, seria necessário prever exatamente o desempenho do processador, do sistema operacional, da memória, do armazenamento e até da concorrência existente naquele instante. Em vez disso, o PostgreSQL compara apenas custos relativos. Não importa se o plano A custará 200 unidades e o plano B custará 350. O único aspecto relevante é que o primeiro parece exigir menos trabalho do que o segundo.

Essa ideia também surgiu no trabalho clássico de Patricia Selinger e seus colegas da IBM. Desde então, praticamente todos os bancos relacionais adotaram algum tipo de otimização baseada em custo, ainda que cada implementação possua seu próprio modelo interno.

Como o PostgreSQL calcula esse custo?

A resposta curta seria: combinando CPU, memória e operações de entrada e saída. Mas essa resposta esconde uma quantidade enorme de engenharia.

Internamente, o planner utiliza diversos parâmetros configuráveis que representam o custo relativo de diferentes operações. Entre eles, alguns merecem destaque.

O seq_page_cost representa o custo de ler uma página de forma sequencial. Já o random_page_cost estima quanto custa acessar páginas espalhadas pelo armazenamento. Existem ainda parâmetros relacionados ao processamento da CPU, como cpu_tuple_cost, cpu_index_tuple_cost e cpu_operator_cost, que procuram estimar o trabalho realizado pelo processador ao manipular registros, percorrer índices e avaliar operadores.

Esses valores não foram escolhidos aleatoriamente. Eles refletem uma observação importante sobre o comportamento do hardware.

Durante décadas, leituras aleatórias em discos rígidos eram muito mais caras do que leituras sequenciais. Em um HDD tradicional, mover o braço mecânico até diferentes regiões do disco representava um custo enorme. Ler páginas consecutivas era significativamente mais eficiente.

Esse comportamento moldou boa parte dos algoritmos utilizados pelos bancos relacionais. Mesmo hoje, com SSDs NVMe reduzindo drasticamente essa diferença, a ideia permanece válida: acessar regiões dispersas do armazenamento ainda costuma ser mais caro do que percorrer páginas já organizadas de maneira sequencial. É justamente por isso que um índice pode ser ignorado mesmo existindo, o planner não está apenas comparando duas estruturas de dados. Ele está estimando como cada uma delas interagirá com memória, cache, CPU e armazenamento, e essa estimativa nos leva de volta ao assunto do artigo anterior.

No texto sobre páginas vimos que um banco de dados não trabalha com registros individuais, mas com blocos de memória de tamanho fixo. Essa característica aparece novamente aqui. O planner raramente está interessado em quantas linhas serão lidas. O que realmente importa é quantas páginas precisarão ser carregadas, quantas provavelmente já estarão no cache e quantas exigirão acesso ao armazenamento.

Essa mudança de perspectiva explica por que duas consultas retornando exatamente o mesmo número de linhas podem possuir custos completamente diferentes. Elas talvez estejam acessando quantidades muito diferentes de páginas e, para um banco de dados, páginas representam trabalho.

Por que, afinal, um índice perfeitamente válido pode ser ignorado? A resposta não está no índice. Está na forma como os dados estão distribuídos na memória e no armazenamento.

O planner não lê registros. Ele estima páginas.

No artigo anterior vimos que bancos de dados não armazenam registros individualmente. A unidade fundamental de armazenamento é a página. Esse detalhe reaparece aqui porque praticamente todo o modelo de custo do PostgreSQL foi construído em torno dessa ideia.

Imagine uma tabela contendo dez milhões de registros distribuídos em aproximadamente um milhão de páginas de dados.

Agora imagine duas consultas diferentes.

A primeira procura um único pedido.

SELECT *
FROM pedidos
WHERE id = 9283714;

A segunda procura todos os pedidos entregues.

SELECT *
FROM pedidos
WHERE status = 'ENTREGUE';

Em ambos os casos existe um índice disponível, a diferença é que o trabalho necessário para utilizar esse índice é completamente diferente.

Na primeira consulta, o planner estima que apenas uma ou duas páginas precisarão ser carregadas. A árvore B+ Tree conduz rapidamente até a folha correspondente, localiza o ponteiro para o registro e a consulta termina após um número muito pequeno de leituras.

Na segunda situação, o cenário muda completamente. Se noventa e cinco por cento dos pedidos possuem o status “ENTREGUE”, utilizar o índice significa percorrer praticamente todas as folhas da árvore, visitar milhões de referências para registros espalhados pela tabela e carregar uma enorme quantidade de páginas em ordem quase aleatória.

Nesse momento, a pergunta deixa de ser “existe um índice?” e passa a ser “qual estratégia movimentará menos páginas entre armazenamento e memória?”.

Essa é exatamente a pergunta que o planner tenta responder.

Nós pensamos em registros, o banco pensa em páginas.

Essa diferença de perspectiva explica boa parte das decisões aparentemente estranhas que encontramos em um plano de execução.

O cache muda completamente a conversa

Existe outro elemento importante que costuma desaparecer quando falamos apenas de índices: o cache.

Nenhum banco de dados moderno consulta o armazenamento a cada leitura. Grande parte das páginas acessadas com frequência permanece em memória, seja no shared buffers do PostgreSQL, seja no cache do próprio sistema operacional.

Isso significa que duas consultas aparentemente idênticas podem apresentar custos completamente diferentes dependendo do estado do cache.

Se as páginas necessárias já estiverem em memória, utilizar um índice pode ser extremamente barato. Se cada acesso exigir uma leitura física no armazenamento, o cenário muda completamente.

O planner tenta levar essa possibilidade em consideração por meio de parâmetros como effective_cache_size. Esse parâmetro não define a quantidade de memória utilizada pelo PostgreSQL. Ele informa ao otimizador aproximadamente quanto do conjunto de dados provavelmente estará disponível em cache, permitindo que o modelo de custo produza estimativas mais próximas da realidade.

Novamente, não existe certeza. Existe uma aproximação suficientemente boa para comparar alternativas. Essa talvez seja uma das características mais elegantes do banco de dados relacional moderno. Em vez de tentar prever exatamente o comportamento do sistema, ele trabalha continuamente com probabilidades.

SSDs mudaram parte da equação — mas não toda ela

Durante muitos anos, uma leitura aleatória em disco era uma operação extremamente cara. Discos rígidos mecânicos precisavam mover cabeças de leitura fisicamente até diferentes regiões do prato, tornando acessos dispersos muito mais lentos do que leituras sequenciais.

Foi nesse contexto que parâmetros como random_page_cost surgiram.

Historicamente, o PostgreSQL assumia que uma leitura aleatória custava aproximadamente quatro vezes mais do que uma leitura sequencial. Essa diferença fazia bastante sentido quando praticamente todos os servidores utilizavam HDDs.

A chegada dos SSDs alterou parte desse cenário. Como não existe movimentação mecânica, a diferença entre leituras sequenciais e aleatórias diminuiu significativamente. Em muitos ambientes modernos, administradores reduzem o valor de random_page_cost para refletir melhor o comportamento do hardware disponível.

É importante notar, entretanto, que SSDs não eliminaram o problema. Mesmo em dispositivos NVMe extremamente rápidos, acessar milhares de páginas espalhadas continua sendo mais caro do que percorrer páginas já organizadas de forma contínua. Além disso, entram em cena outros fatores como prefetch, comportamento do sistema operacional, largura de banda da memória e eficiência do cache.

Ou seja, a tecnologia evoluiu, o princípio de engenharia permaneceu praticamente o mesmo.

O planner não é perfeito — e nunca pretendeu ser

Talvez exista uma expectativa injusta em relação ao planner. Quando um plano de execução produz uma consulta lenta, nossa tendência é concluir que o banco “escolheu errado”.

Às vezes isso realmente acontece, mas vale lembrar que o planner toma decisões utilizando estimativas. Se as estatísticas estiverem desatualizadas, se a distribuição dos dados tiver mudado drasticamente ou se existir uma correlação que não foi corretamente capturada pelas estatísticas disponíveis, o plano escolhido poderá deixar de ser o ideal.

Isso não representa um defeito do algoritmo, representa uma consequência inevitável de qualquer sistema baseado em estimativas.

Na prática, o planner faz exatamente o mesmo que um engenheiro faria diante de informações incompletas: utiliza os melhores dados disponíveis para tomar a decisão mais provável naquele momento.

Essa perspectiva costuma gerar um pouco mais de empatia com o banco de dados. Ele não está tentando adivinhar, está tentando estimar e existe uma enorme diferença entre essas duas coisas.

Talvez estejamos fazendo a pergunta errada

Voltando à pergunta que deu origem a este artigo, talvez possamos reformulá-la.

Em vez de perguntar: “Por que o PostgreSQL ignorou meu índice?”, talvez a pergunta mais interessante seja: “Por que o planner concluiu que outra estratégia custaria menos?”. A diferença parece sutil, mas muda completamente a investigação.

Agora deixamos de olhar apenas para o índice. Passamos a observar estatísticas, distribuição dos dados, cardinalidade, seletividade, cache, páginas, parâmetros de custo e comportamento do hardware. Em outras palavras, começamos a enxergar o banco da mesma forma que seus engenheiros o enxergam.

Essa talvez seja a principal mudança de perspectiva que um profissional de dados pode desenvolver. Um banco de dados não executa consultas. Antes disso, ele interpreta, estima, compara possibilidades e toma decisões.

O SQL que escrevemos não é uma sequência de instruções. É apenas uma descrição do resultado esperado. Todo o restante fica por conta de um sistema de otimização cuja origem remonta a pesquisas realizadas há mais de quarenta anos — e que continua sendo refinado até hoje.

É por isso que um índice pode ser ignorado.

E é exatamente por isso que entender o planner é muito mais importante do que decorar quando criar um índice.