Varredura sequencial, de índice e somente de índice

Quando o planejador precisa de linhas de uma tabela, ele escolhe um dos três métodos de acesso, e os entrevistadores esperam que você nomeie todos:

• Varredura sequencial, lê todas as linhas da tabela do início ao fim.
• Varredura por índice, percorre um índice para encontrar as linhas correspondentes e depois busca cada uma na tabela.
• Varredura somente pelo índice, responde inteiramente a partir do índice, sem acessar a tabela.

Saber por que o planejador escolhe cada método é o núcleo desta lição e uma pergunta garantida para candidatos experientes.

Uma varredura sequencial lê as páginas da tabela uma após a outra e aplica qualquer filtro a cada linha. Nenhum índice é consultado.

Isso parece ruim, mas geralmente é a escolha certa. Leituras sequenciais são rápidas para o disco (não há saltos aleatórios); portanto, quando uma consulta retorna uma grande parte da tabela, ler tudo é melhor do que percorrer um índice milhões de vezes.

O exemplo: varrer orders e manter as linhas em que amount > 100. Se a maioria dos pedidos ultrapassar 100, a varredura sequencial será correta.

Uma varredura por índice usa uma árvore B para saltar diretamente para as chaves correspondentes e depois lê as linhas correspondentes no armazenamento da tabela.

Ela é excelente quando o filtro é seletivo, retornando uma pequena parte da tabela. Buscar 5 linhas por meio de um índice é melhor do que ler 10 milhões.

O plano informa o índice utilizado. Cada correspondência custa uma busca no índice mais uma leitura no armazenamento da tabela (uma leitura aleatória); por isso, as varreduras por índice perdem sua vantagem quando retornam linhas demais.

O conceito único que orienta tudo isso é a seletividade: qual fração das linhas um predicado mantém.

• Alta seletividade (poucas linhas correspondem, como no caso de um identificador exclusivo) favorece uma varredura por índice.
• Baixa seletividade (muitas linhas correspondem, como em status IS NOT NULL) favorece uma varredura sequencial.

Uma regra prática comum: quando uma consulta retorna mais de aproximadamente 5 a 10 por cento de uma tabela, o planejador costuma preferir uma varredura sequencial, pois as leituras aleatórias do armazenamento causadas pelo índice se tornam mais caras do que ler tudo em ordem.

Uma varredura somente pelo índice é a mais rápida das três. Se todas as colunas necessárias para a consulta já estiverem no índice, o mecanismo nunca acessará o armazenamento da tabela.

A consulta de exemplo seleciona apenas customer_id e filtra por essa coluna, e o índice está em customer_id. Todos os dados necessários estão no índice, por isso o Postgres informa Index Only Scan.

Isso evita as leituras aleatórias do armazenamento que tornam mais lenta uma varredura comum por índice, uma grande vantagem em tabelas largas.

Os entrevistadores adoram essa nuance. Mesmo uma varredura somente pelo índice ainda precisa confirmar se cada linha está visível para a sua transação (MVCC), e o índice, por si só, não armazena informações de visibilidade.

O Postgres usa o mapa de visibilidade: se uma página estiver marcada como totalmente visível, ele ignora a tabela; caso contrário, ainda precisa buscar a linha na tabela. O plano mostra Heap Fetches: N.

Por isso, uma tabela recém-atualizada pode apresentar muitas buscas na tabela e tornar lentas as varreduras somente pelo índice até que VACUUM atualize o mapa de visibilidade.

Há um quarto método que aparece com frequência: a varredura de tabela por mapa de bits. O planejador o escolhe quando um predicado corresponde a mais linhas do que uma varredura comum por índice processaria, mas a menos linhas do que uma tabela inteira.

Primeiro, ele cria um mapa de bits das localizações das linhas correspondentes a partir do índice (varredura de índice por mapa de bits) e depois busca as páginas da tabela em ordem física, em vez de uma ordem aleatória. As buscas ordenadas são muito mais baratas do que as leituras dispersas de uma varredura comum por índice.

Uma pergunta clássica de entrevista: adicionei um índice, mas o plano ainda faz uma varredura sequencial; por quê? Motivos comuns:

• O predicado não é seletivo; fazer a varredura é realmente mais barato.
• Uma função envolve a coluna: WHERE lower(email) = ... não pode usar um índice comum em email.
• Uma incompatibilidade de tipos força uma conversão implícita que impede o uso do índice.
• Estatísticas desatualizadas; execute ANALYZE.
• A tabela é minúscula; varrer algumas páginas é melhor do que arcar com a sobrecarga do índice.

Suponha que orders tenha um índice em created_at, mas esta consulta ainda faça uma varredura sequencial:

O problema é DATE(created_at). Envolver a coluna em uma função significa que o índice na coluna original created_at não pode ser usado. A solução é reescrever a consulta como um predicado de intervalo que deixe a coluna sem alterações, ou criar um índice de expressão em DATE(created_at).

Tenha esta comparação em mente para a entrevista:

• Varredura sequencial, melhor ao retornar uma grande fração das linhas; E/S sequencial.
• Varredura por índice, melhor para buscas seletivas; percorre o índice e faz buscas aleatórias no armazenamento da tabela.
• Varredura de tabela por mapa de bits, quantidade intermediária de correspondências; transforma o índice em um mapa de bits e depois faz leituras ordenadas das páginas da tabela.
• Varredura somente pelo índice, mais rápida quando o índice contém todas as colunas necessárias e as páginas estão totalmente visíveis.

O planejador escolhe com base no custo estimado, determinado principalmente pela seletividade e pelas estatísticas.

Para comprovar uma ideia durante o desenvolvimento, você pode influenciar temporariamente o planejador: SET enable_seqscan = off; faz com que ele prefira índices, permitindo comparar os planos.

Este é um truque diagnóstico, nunca uma solução para produção. Mencione nas entrevistas que as soluções reais são estatísticas melhores, um índice adequado ou a reescrita do predicado, e não a desativação global dos recursos do planejador.

Uma consulta seleciona apenas email e filtra por email, e existe um índice de árvore B em email. O plano mostra Index Only Scan. Por que isso é mais rápido do que uma varredura comum por índice?

Principais conclusões sobre os métodos de acesso:

• A varredura sequencial é melhor para consultas de baixa seletividade; a varredura por índice é melhor para consultas seletivas.
• A varredura somente pelo índice evita o armazenamento da tabela quando o índice contém todas as colunas necessárias; observe Heap Fetches e o mapa de visibilidade.
• A varredura de tabela por mapa de bits preenche a lacuna intermediária ao buscar as páginas da tabela em ordem física.
• O planejador decide com base na seletividade e nas estatísticas; funções aplicadas às colunas, incompatibilidades de tipos e estatísticas desatualizadas são os motivos pelos quais um índice é ignorado.