---
id: postgres-indexing-and-tuning
domain: data
agents: [data-engineer]
when: "ao otimizar performance de queries no PostgreSQL"
---

# PostgreSQL — indexação e tuning que muda o plano

A maioria dos "índices" que se cria no PostgreSQL é B-tree em coluna única — e isso resolve o caso fácil.
O craft está no que B-tree não resolve: `jsonb @> '{}'`, `tags && ARRAY[...]`, busca geográfica, tabelas de
bilhões de linhas append-only, filtro em `lower(email)`, `WHERE deleted_at IS NULL`. Trocar o tipo de
índice, a ordem das colunas ou um parâmetro de memória vira `Seq Scan` de 3 segundos em `Index Scan` de 2ms.
A régua deste pack: **toda decisão de índice se justifica lendo o `EXPLAIN (ANALYZE, BUFFERS)`** — não no
chute. Se você não rodou o EXPLAIN antes e depois, não otimizou, adivinhou.

## O problema (os tells de quem não lê o plano)

Sinais de que a otimização foi no chute, não no plano:

1. **B-tree em tudo.** Criar B-tree numa coluna `jsonb`, `text[]` ou `tsvector` — o planner ignora e faz
   `Seq Scan`. Esses tipos pedem GIN, não B-tree.
2. **Índice composto na ordem errada.** `CREATE INDEX ON pedidos (status, cliente_id)` quando a query
   filtra só por `cliente_id`. A coluna líder não é usada → o índice não serve à query.
3. **Índice em `coluna` mas a query usa `lower(coluna)` ou `coluna::date`.** Qualquer função/cast na
   coluna invalida o índice simples. Precisa de índice de expressão.
4. **Indexar a tabela inteira quando 95% das linhas nunca são consultadas** (ex.: `WHERE deleted_at IS
   NULL` em soft-delete). Índice parcial é menor, mais rápido e mais barato de manter.
5. **Otimizar `EXPLAIN` sem `ANALYZE`** — você só vê a estimativa do planner, não o tempo real nem os
   buffers. Custo em "unidades de página", não em ms, não prova nada sozinho.
6. **`shared_buffers`/`work_mem` no default de instalação** (128MB / 4MB) num servidor de 32GB — sorts
   derramam pra disco (`external merge Disk`), cache mínimo, tudo lento sem aparecer no plano de índice.
7. **Adicionar índice e nunca medir o custo de escrita.** Todo índice é overhead em `INSERT`/`UPDATE`.
   Índice que ninguém usa (cheque `pg_stat_user_indexes.idx_scan = 0`) só atrasa escrita.

## O conhecimento / os princípios

### 1. Escolha do tipo de índice pelo operador, não pelo hábito

O planner só usa um índice se ele suporta o **operador** da sua cláusula `WHERE`. Cada tipo cobre um
conjunto de operadores (fonte: PostgreSQL docs, *11.2 Index Types*):

| Tipo | Operadores suportados | Casos reais |
|---|---|---|
| **B-tree** (default) | `<  <=  =  >=  >`, `BETWEEN`, `IN`, `IS NULL`, `LIKE 'foo%'` (ancorado no início) | igualdade e range em qualquer tipo ordenável; PK/FK; `ORDER BY` (retorna ordenado) |
| **Hash** | `=` apenas | igualdade pura; útil em coluna grande onde só se compara `=` |
| **GIN** | `@>  <@  =  &&` (e `@@` para FTS, `?`/`@?` para jsonb) | `jsonb`, `array`, `tsvector`, `hstore` — múltiplos valores numa coluna |
| **GiST** | `<<  &<  &>  @>  <@  &&` e vizinhança `<->` | dados geométricos/`geometry` (PostGIS), ranges, nearest-neighbor (`ORDER BY ponto <-> '(x,y)'`) |
| **SP-GiST** | `<<  >>  ~=  <@` | dados não-balanceados: quadtree, k-d tree, roteamento IP, `inet` |
| **BRIN** | `<  <=  =  >=  >` (linear) | tabelas enormes onde o valor é **correlacionado com a ordem física** (timestamps append-only, IDs sequenciais) |

Sintaxe de seleção do método:

```sql
-- B-tree é o default; os outros são explícitos:
CREATE INDEX ON eventos USING gin  (payload jsonb_path_ops);   -- jsonb com @>
CREATE INDEX ON posts   USING gin  (to_tsvector('portuguese', corpo));  -- FTS
CREATE INDEX ON lugares USING gist (geom);                     -- geo + <->
CREATE INDEX ON logs    USING brin (criado_em);                -- append-only gigante
CREATE INDEX ON contas  USING hash (token);                    -- só =
```

Regra de leitura: se a query usa `@>`, `&&`, `@@` ou nearest-neighbor `<->`, **não é B-tree** — é GIN
(contém/sobrepõe em jsonb/array/FTS) ou GiST (geo/range/vizinhança).

**GIN x GiST para o mesmo dado:** GIN é mais rápido para **ler** e maior/mais lento para **escrever**;
GiST é menor e mais rápido de atualizar, com leitura "lossy" (pode gerar falsos positivos que o
re-check elimina). FTS de alto volume de leitura → GIN. Dado que muda muito → GiST.

### 2. BRIN: o índice de 99% menos espaço para tabelas append-only

BRIN guarda só o **mínimo e máximo de cada faixa de blocos físicos** — não uma entrada por linha. Só
funciona se os valores estiverem **correlacionados com a ordem física** das linhas (ex.: `criado_em`
numa tabela que só cresce por append). Numa tabela de 500M linhas, um BRIN em `criado_em` ocupa
kilobytes onde um B-tree ocuparia gigabytes.

```sql
-- tabela de telemetria, 1 bilhão de linhas, inserida em ordem temporal:
CREATE INDEX ON telemetria USING brin (criado_em);
-- query de range temporal:
SELECT * FROM telemetria WHERE criado_em BETWEEN '2026-06-01' AND '2026-06-02';
```

Se os dados **não** forem fisicamente ordenados pela coluna, BRIN é inútil — use B-tree.

### 3. Ordem das colunas em índice composto: leftmost prefix

Em índice B-tree multicoluna, vale a regra exata da doc (*11.5 Multicolumn Indexes*):

> "Equality constraints on leading columns, plus any inequality constraints on the first column that
> does not have an equality constraint, will always be used to limit the portion of the index scanned."

Traduzindo: **a coluna mais à esquerda manda.** Para `CREATE INDEX ON pedidos (cliente_id, status, criado_em)`:

| Query `WHERE` | Usa o índice eficientemente? |
|---|---|
| `cliente_id = 7` | Sim (prefixo líder) |
| `cliente_id = 7 AND status = 'pago'` | Sim (igualdade nas líderes) |
| `cliente_id = 7 AND status = 'pago' AND criado_em > '...'` | Sim — ideal |
| `cliente_id = 7 AND criado_em > '...'` (pula `status`) | Parcial: usa `cliente_id`, mas `criado_em` vira filtro pós-scan |
| `status = 'pago'` (sem `cliente_id`) | Não eficiente — pode até virar `Seq Scan` |

Regra prática de ordenação das colunas:
1. Colunas de **igualdade primeiro** (`=`), na ordem de uso.
2. A coluna de **range/`ORDER BY` por último**.
3. Da mais **seletiva** para a menos, entre as de igualdade.

Da doc: índices com mais de 3 colunas raramente compensam; índice multicoluna deve ser usado com
parcimônia — em geral um índice de coluna única já basta. Para GIN e BRIN, a ordem das colunas **não**
importa ("effectiveness is the same regardless of which column the query uses").

### 4. Covering index (INCLUDE) → index-only scan

Um `Index Scan` ainda visita o heap para buscar colunas que não estão no índice. Se **todas** as colunas
do `SELECT` e do `WHERE` estiverem no índice, o planner faz **Index-Only Scan** — zero acesso ao heap.
Use `INCLUDE` para carregar colunas de payload sem inflar a chave de busca:

```sql
-- query: SELECT total FROM pedidos WHERE cliente_id = 7;
CREATE INDEX pedidos_cliente_inc ON pedidos (cliente_id) INCLUDE (total);
```

Pontos da doc (*11.9 Index-Only Scans*):
- Colunas em `INCLUDE` **não** fazem parte da chave; em índice único, a unicidade vale só nas colunas-chave.
- Só **B-tree, GiST e SP-GiST** suportam `INCLUDE`. **GIN não** suporta index-only scan.
- O index-only scan depende do **visibility map**: só é vantagem se "uma fração significativa das heap
  pages estiver com o bit all-visible setado". Tabela com muito `UPDATE`/`DELETE` recente → rode
  `VACUUM` para o bit valer, senão volta a visitar o heap.

### 5. Índice parcial: indexe só as linhas que importam

`CREATE INDEX ... WHERE` constrói o índice sobre um **subconjunto** das linhas. Menor, mais rápido de
escanear, mais barato em escrita. Exemplos da doc (*11.8 Partial Indexes*):

```sql
-- soft-delete: 95% das queries só querem linhas vivas
CREATE INDEX pedidos_ativos ON pedidos (cliente_id) WHERE deleted_at IS NULL;

-- só pedidos não-faturados são consultados com frequência:
CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed IS NOT TRUE;

-- unicidade parcial: só 1 "sucesso" por (subject, target), mas N falhas:
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target) WHERE success;
```

Pegadinha: o planner só usa o índice parcial se a `WHERE` da query **implica logicamente** o predicado
do índice. `WHERE deleted_at IS NULL` casa; `WHERE deleted_at = $1` (parametrizado) **não** casa.

### 6. Índice de expressão: quando a query usa função/cast na coluna

`lower(email)`, `criado_em::date`, `(first || ' ' || last)` — qualquer transformação invalida o índice
da coluna crua. Indexe a **expressão** (*11.7 Indexes on Expressions*):

```sql
-- query: SELECT * FROM users WHERE lower(email) = 'a@b.com';
CREATE INDEX users_lower_email ON users (lower(email));

-- nome completo concatenado:
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
```

Custo: a expressão é recalculada a cada `INSERT`/`UPDATE` não-HOT (mais caro em escrita) — mas **não**
durante a busca (já está armazenada). Vale quando leitura > escrita.

### 7. Tuning de memória — os 4 parâmetros que mexem no plano

Defaults de instalação são minúsculos. Em servidor dedicado (fonte: *PostgreSQL Wiki — Tuning Your
PostgreSQL Server*):

| Parâmetro | Recomendação | O que faz / sintoma do default |
|---|---|---|
| `shared_buffers` | **25% da RAM** (≥1GB de RAM) | cache de páginas do Postgres. Default ~128MB; baixo → mais `read` de disco. Exige restart. |
| `effective_cache_size` | **50–75% da RAM** | só uma *dica* ao planner do cache total (SO+PG). Baixo → planner subestima e evita índice. Sem restart. |
| `work_mem` | `(RAM − shared_buffers) / max_connections`, conservador | memória por operação de sort/hash. Default ~4MB → sort derrama pra disco (`external merge Disk` no EXPLAIN). É **por operação**: 50MB × 30 conexões × N sorts = GBs reais. Ajustável por sessão. |
| `maintenance_work_mem` | bem maior que `work_mem` (ex.: 256MB–1GB) | usado por `CREATE INDEX`, `VACUUM`, `ALTER TABLE`. Default 64MB. Maior = índice cria mais rápido. |

`work_mem` é a alavanca mais comum: se o EXPLAIN ANALYZE mostra `Sort Method: external merge Disk:
NkB`, subir `work_mem` (na sessão) transforma em `quicksort Memory`.

### 8. Ler o EXPLAIN (ANALYZE, BUFFERS) — onde mora a verdade

Sempre meça com `EXPLAIN (ANALYZE, BUFFERS)` — executa de verdade e mostra tempo real + I/O:

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100;
```

Anatomia de uma linha (*14.1 Using EXPLAIN*):

```
Index Scan using tenk1_unique1 on tenk1
  (cost=0.29..8.30 rows=1 width=244) (actual time=0.005..0.007 rows=1.00 loops=1)
```

- `cost=start..total` — unidades arbitrárias do planner (page fetches), **não ms**. Só comparáveis entre planos.
- `rows=` (em `cost`) = **estimativa**; `rows=` (em `actual`) = **real**. Discrepância grande (ex.: estima
  1, retorna 50.000) → estatística desatualizada → rode `ANALYZE tabela`.
- `actual time=start..end` em **ms**. `loops=N` → multiplique `actual time × loops` para o tempo total
  do nó (crítico em Nested Loop).
- `Buffers: shared hit=N read=M` → `hit` = veio do cache; `read` = leu do disco. `read` alto = I/O caro;
  alvo é maximizar `hit`.

**Os três scans e o que cada um diz:**

| Nó | Significa | Quando é bom / ruim |
|---|---|---|
| `Seq Scan` | lê a tabela inteira | OK em tabela pequena ou query que retorna >~5–10% das linhas; **ruim** se há filtro seletivo e existe índice ignorado |
| `Index Scan` | navega o índice, busca cada linha no heap | ótimo para queries muito seletivas (1 ou poucas linhas) ou que casam `ORDER BY` |
| `Bitmap Index Scan` + `Bitmap Heap Scan` | índice monta um bitmap de localizações, depois lê o heap em ordem física | melhor que Index Scan quando há **muitas** linhas (centenas/milhares): minimiza saltos de disco; combina vários índices com `BitmapAnd`/`BitmapOr` |

Pistas no plano:
- `Recheck Cond` no Bitmap Heap Scan: re-checagem após o bitmap (normal).
- `Filter: (...)` + `Rows Removed by Filter: N`: condição que **não** virou `Index Cond` — candidata a
  entrar num índice composto ou de expressão.
- `Heap Blocks: exact=N` / `lossy=N`: `lossy` alto sugere subir `work_mem` (bitmap não coube na memória).
- `Index Cond` vs `Filter`: o que está em `Index Cond` foi resolvido pelo índice; o que está em `Filter`
  passou linha a linha.

Diagnóstico típico: query lenta → EXPLAIN ANALYZE mostra `Seq Scan` + `Filter` + `Rows Removed by
Filter` enorme → criar índice no campo do filtro → re-rodar → vira `Index Scan`/`Bitmap Heap Scan` com
`actual time` menor e `Buffers shared read` menor.

## Checklist

Antes de dar por otimizada uma query, responda — qualquer "não" é trabalho a fazer:

- [ ] Rodei `EXPLAIN (ANALYZE, BUFFERS)` **antes e depois** e comparei `actual time` e `Buffers read`?
- [ ] O tipo de índice casa com o **operador** da `WHERE`? (`@>`/`&&`/`@@` → GIN; geo/`<->` → GiST;
      append-only gigante → BRIN; só `=` → Hash; resto → B-tree)
- [ ] Em índice composto, a coluna **mais à esquerda** é a que a query filtra por igualdade, e o range/
      `ORDER BY` ficou por último?
- [ ] A query usa função/cast na coluna (`lower()`, `::date`)? Se sim, existe índice de **expressão**?
- [ ] Se a query sempre filtra um subconjunto (`deleted_at IS NULL`, `status='ativo'`), há índice **parcial**?
- [ ] Dá pra virar **Index-Only Scan** com `INCLUDE` nas colunas do `SELECT`? (e o `VACUUM` está em dia
      pro visibility map valer?)
- [ ] Conferi `pg_stat_user_indexes.idx_scan` — não estou criando índice que ninguém usa (custo de escrita à toa)?
- [ ] As estatísticas estão frescas (`ANALYZE`) — a estimativa de `rows` bate com o `actual`?
- [ ] O EXPLAIN mostra `external merge Disk` ou `lossy` no bitmap? Se sim, ajustei `work_mem`?
- [ ] `shared_buffers` (~25% RAM) e `effective_cache_size` (50–75% RAM) saíram do default da instalação?

## Tabela de decisão — use X quando Y

| Use… | Quando… |
|---|---|
| **B-tree** | igualdade/range em tipo ordenável, PK/FK, `ORDER BY`, `LIKE 'prefixo%'` (o default, 90% dos casos) |
| **GIN** | coluna `jsonb`/`array`/`tsvector`/`hstore` com `@>`, `&&`, `@@`; busca full-text de alta leitura |
| **GiST** | dados geométricos/PostGIS, ranges, nearest-neighbor (`ORDER BY geom <-> ponto`), dado que muda muito |
| **BRIN** | tabela enorme append-only com coluna correlacionada à ordem física (timestamp, ID sequencial) |
| **Hash** | só compara `=`, nunca range nem ordenação, e quer índice menor que B-tree |
| **Índice de expressão** | a query filtra por `lower(col)`, `col::date`, concatenação — qualquer transformação da coluna |
| **Índice parcial (`WHERE`)** | a query sempre toca um subconjunto fixo (soft-delete, status, flag) e o resto é ruído |
| **Covering (`INCLUDE`) / index-only** | leitura quente que retorna poucas colunas e você quer eliminar o acesso ao heap |
| **Índice composto** | a query filtra 2–3 colunas juntas com frequência; ordene igualdade→range, mais seletiva primeiro |
| **Subir `work_mem`** | EXPLAIN mostra `Sort Method: external merge Disk` ou `Heap Blocks: lossy=N` |
| **Subir `shared_buffers`/`effective_cache_size`** | `Buffers shared read` alto, planner evitando índices, ainda no default da instalação |
| **`Seq Scan` (deixar como está)** | tabela pequena, ou a query retorna >5–10% das linhas (índice não compensaria) |
| **`ANALYZE` / estatística** | estimativa de `rows` diverge muito do `actual rows` no EXPLAIN ANALYZE |

---

Fontes: PostgreSQL Documentation — *11.2 Index Types*, *11.5 Multicolumn Indexes*, *11.7 Indexes on
Expressions*, *11.8 Partial Indexes*, *11.9 Index-Only Scans*, *14.1 Using EXPLAIN*; PostgreSQL Wiki —
*Tuning Your PostgreSQL Server*.