Em uma tabela com um milhão de linhas, a mesma SELECT pode levar cinquenta milissegundos ou bem menos de um. A única diferença é se o banco de dados lê cada linha para encontrar as que você pediu, ou pula direto até elas. Um índice é o que permite esse salto, e você paga por isso em cada escrita.

Na prática, um índice é uma segunda cópia ordenada de uma ou mais colunas, guardada ao lado da tabela e atualizada automaticamente sempre que a tabela muda. O banco de dados usa essa cópia para chegar às linhas correspondentes sem tocar no resto.

Por que uma query sem índice lê a tabela inteira

Sem um índice, WHERE customer_id = 42 obriga o Postgres a ler cada linha e testar a condição em cada uma. Isso é uma varredura sequencial (Seq Scan). Em mil linhas você nem percebe. Em um milhão de linhas onde quase nada corresponde, você está lendo e descartando 999.989 linhas para obter 11.

Você pode ver isso na prática. Suba um container Postgres, configurado como em O Que É Docker Compose e Como Usar:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

1
2

docker compose up -d db
docker compose exec db psql -U postgres

Depois crie uma tabela grande o suficiente para a diferença aparecer:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_cents integer NOT NULL
);

INSERT INTO orders (customer_id, status, created_at, total_cents)
SELECT
  (random() * 50000)::int,
  (ARRAY['pending', 'paid', 'shipped', 'refunded'])[(floor(random() * 4) + 1)::int],
  now() - (random() * interval '365 days'),
  (random() * 20000)::int
FROM generate_series(1, 1000000);

ANALYZE orders;

Um milhão de linhas, sem índice em customer_id. Peça os pedidos de um cliente. Os exemplos abaixo desativam primeiro os workers paralelos (SET max_parallel_workers_per_gather = 0), para o plano ficar em um único nó. Em uma máquina multi-core, o Postgres poderia dividir a mesma varredura entre um Gather e vários workers Parallel Seq Scan, o que é a mesma ideia com mais processos rodando ao mesmo tempo:

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Seq Scan on orders  (cost=0.00..20834.00 rows=21 width=31) (actual time=1.066..53.238 rows=11 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 999989
 Planning Time: 0.301 ms
 Execution Time: 53.284 ms

Rows Removed by Filter: 999989 é a conta a pagar. O Postgres leu a tabela inteira para devolver 11 linhas.

O que um índice B-tree realmente guarda

Uma tabela sem índice é uma caixa de recibos, jogados um em cima do outro na ordem em que chegaram. Encontrar todos os recibos de um cliente significa esvaziar a caixa.

Um índice B-tree são esses mesmos recibos arquivados em um armário, ordenados por cliente. O nível mais baixo, as leaf pages, guarda cada valor indexado junto com um ponteiro para sua linha no disco. Acima das folhas ficam algumas páginas internas, cada uma com o menor valor de um intervalo e um ponteiro para ele. As páginas do Postgres têm 8 KB por padrão, e uma página interna pode apontar para centenas de filhos, então três ou quatro níveis cobrem dezenas de milhões de linhas. Uma busca lê uma página por nível e chega na folha certa. Poucas leituras de página em vez de uma passada pela tabela inteira.

Adicione o índice e rode a mesma query:

1

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5
6
7

 Bitmap Heap Scan on orders  (cost=4.59..85.69 rows=21 width=31) (actual time=0.217..0.295 rows=11 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=11
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.58 rows=21 width=0) (actual time=0.090..0.090 rows=11 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.979 ms
 Execution Time: 0.449 ms

De 53 ms para 0,45 ms, uma melhora de cerca de 120 vezes, e o custo estimado cai de 20834 para 86. O Seq Scan sumiu, e não há linha Rows Removed by Filter: não havia nada a descartar.

O Postgres escolheu uma Bitmap Heap Scan em vez de um simples Index Scan. Com cerca de 20 linhas correspondentes espalhadas pela tabela, é mais barato buscar primeiro todas as entradas correspondentes do índice (a linha Bitmap Index Scan), ordená-las por página física e então visitar cada página do heap uma única vez (Heap Blocks: exact=11), do que pular no heap separadamente para cada linha. Filtre por uma única linha (uma igualdade em uma coluna única, como id = 42) e você verá um simples Index Scan no lugar. Nos dois casos, o índice faz o mesmo trabalho: alcançar as linhas correspondentes sem ler as que não correspondem.

Como funciona a ordem de colunas em um índice composto

Um índice pode cobrir mais de uma coluna, e a ordem em que você as lista decide quais queries ele consegue atender. CREATE INDEX ON orders (customer_id, status) ordena primeiro por customer_id, depois por status dentro de cada customer_id. Assim ele consegue responder a:

  • WHERE customer_id = 42, usando a primeira coluna.
  • WHERE customer_id = 42 AND status = 'paid', usando as duas.

Ele não consegue responder de forma eficiente a WHERE status = 'paid' sozinho. status só está ordenada dentro do grupo de cada cliente, então não existe uma única sequência ordenada de linhas 'paid' para a árvore pular. Essa é a regra do prefixo mais à esquerda (leftmost prefix rule): um índice composto só ajuda uma query se ela restringir suas colunas a partir da esquerda, em ordem, sem pular nenhuma.

Duas regras para escolher a ordem. Primeiro, coloque na frente a coluna pela qual suas queries filtram sozinha, porque é a única que o índice consegue atender sozinho. Segundo, coloque as colunas que você compara com = antes de uma coluna que você compara com um intervalo (>, <, BETWEEN), porque tudo depois da coluna de intervalo para de restringir a busca. Nessa tabela, customer_id (cerca de 50.000 valores distintos) vem antes de status (quatro valores): você busca clientes o tempo todo, e nunca quer varrer todos os pedidos 'paid' sem distinguir o cliente.

Se você também precisa de buscas rápidas só por status, crie um segundo índice. Não reordene o primeiro.

Quando um index-only scan pula a tabela

A Bitmap Heap Scan acima ainda toca a tabela — é isso que Heap Blocks: exact=11 está fazendo. O índice tem customer_id e um ponteiro para cada linha; SELECT * também quer status, total_cents e created_at, e essas só existem na tabela. Uma cláusula INCLUDE coloca colunas extras dentro do próprio índice:

1
2
3

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (status, total_cents);

status e total_cents viajam como payload. Elas não fazem parte da chave de ordenação, então um WHERE status = ... não pode usá-las para buscar. Mas peça só as colunas que o índice agora carrega junto:

1

EXPLAIN ANALYZE SELECT customer_id, status, total_cents FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Index Only Scan using idx_orders_customer_covering on orders  (cost=0.42..4.79 rows=21 width=15) (actual time=0.071..0.073 rows=11 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.442 ms
 Execution Time: 0.108 ms

Index Only Scan e Heap Fetches: 0: cada linha vem direto do índice, sem nenhuma visita à tabela. Isso é um index-only scan.

Heap Fetches: 0 depende do visibility map da tabela, que marca as páginas cujas linhas são visíveis para todas as transações — uma varredura só pula o heap nas páginas marcadas. O VACUUM faz essas marcações (geralmente o autovacuum, que roda por conta própria, não algo que você dispara manualmente), e qualquer escrita em uma página as apaga de novo. Em uma tabela com muitas escritas recentes, muitas páginas não estão marcadas, Heap Fetches sobe acima de zero, e o Postgres visita o heap para essas linhas mesmo assim. Índices de cobertura compensam mais em tabelas que são lidas muito mais do que escritas.

O que um índice custa e por que o Postgres o ignora

Todo INSERT escreve uma entrada em cada índice da tabela, e um UPDATE que muda uma coluna indexada também escreve novas entradas. Cinco índices significam cinco estruturas extras para atualizar a cada uma dessas escritas. Índices também ocupam espaço em disco. Compare os dois tamanhos diretamente:

1
2

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_relation_size('idx_orders_customer_id')) AS index_size;

E às vezes você cria o índice e o planner não o usa. Raramente é um bug do Postgres. Geralmente é um destes casos:

  • Uma função envolve a coluna. WHERE lower(email) = '[email protected]' não consegue usar um índice simples em email, porque o índice guarda os valores brutos, não lower(email). Indexe a expressão (CREATE INDEX ON users (lower(email))) ou filtre pela coluna bruta.
  • Um coringa no início. WHERE name LIKE '%smith' não dá à árvore nenhum prefixo para buscar. LIKE 'smith%' dá.
  • Baixa seletividade. Um índice em um booleano dividido 50/50 não economiza nada. O Postgres ainda leria metade do índice e a maior parte das páginas da tabela, então o planner corretamente escolhe uma varredura sequencial.
  • Estatísticas desatualizadas. O planner escolhe entre tipos de varredura usando as estatísticas que o ANALYZE coleta em pg_statistic. Depois de uma carga em massa sem ANALYZE, suas estimativas de linhas podem estar bem distantes da realidade. Rode-o depois de toda carga grande, como na configuração acima.

Quando você vir um Seq Scan onde esperava um Index Scan, confira a query contra essa lista antes de mexer no índice.

Quando adicionar um índice e quando pular

Adicione um quando uma coluna aparece em uma WHERE, um JOIN ou um ORDER BY em uma tabela grande o suficiente para uma varredura custar tempo de verdade, e o filtro é seletivo: ele reduz o resultado a uma fatia pequena da tabela, não à metade. Pule quando a tabela é pequena o suficiente para uma varredura já ser rápida, quando a tabela recebe muitas escritas e poucas leituras, ou quando a coluna não consegue restringir nada.

Não adicione por palpite. Rode EXPLAIN ANALYZE na query lenta primeiro. Se ela já mostra um Index Scan e continua lenta, o índice não é o seu problema: reescreva a query, ou guarde o resultado em cache com Redis. Se ela mostra um Seq Scan em uma tabela grande com filtro seletivo, crie o índice exatamente nas colunas que a WHERE usa, na ordem que a regra do prefixo mais à esquerda pede, e rode EXPLAIN ANALYZE de novo para confirmar que o plano mudou.

Artigos relacionados