# SQL e índices em PostgreSQL

Interrogações de leitura e escrita sobre o esquema da Loja de Bilhetes e índices para as pesquisas frequentes.

Página: https://resumos.rgo.pt/cadeiras/lbaw/sql-indices/

Com o [esquema criado](https://resumos.rgo.pt/cadeiras/lbaw/sql-indices/esquema-relacional/), a aplicação precisa de ler e escrever dados. Esta página escreve as três interrogações centrais da Loja de Bilhetes e mostra como um índice transforma a pesquisa mais frequente.

## Três interrogações sobre a loja

Sessões futuras de um evento, com lugares ainda por vender. A lotação menos os bilhetes não cancelados dá os lugares livres:

```
SELECT s.id, s.data_hora, s.preco,
       s.lotacao - COUNT(b.id) AS livres
FROM sessoes AS s
LEFT JOIN bilhetes AS b
  ON b.sessao_id = s.id AND b.estado <> 'cancelado'
WHERE s.evento_id = 3 AND s.data_hora > NOW()
GROUP BY s.id;
```

O `LEFT JOIN` com a condição do estado dentro do `ON` é o ponto delicado: bilhetes cancelados não ocupam lugar, mas a sessão continua a aparecer mesmo sem nenhum bilhete vendido. Se pusesses `b.estado <> 'cancelado'` no `WHERE`, as sessões sem bilhetes desapareciam da lista, porque `NULL <> 'cancelado'` não é verdadeiro.

```
-- parece equivalente, mas esconde as sessões vazias
SELECT s.id, s.lotacao - COUNT(b.id) AS livres
FROM sessoes AS s
LEFT JOIN bilhetes AS b ON b.sessao_id = s.id
WHERE s.evento_id = 1 AND b.estado <> 'cancelado'
GROUP BY s.id;
```

Uma sessão sem bilhetes chega ao `WHERE` com `b.estado` a nulo, e nulo comparado com qualquer coisa não é verdadeiro. A linha é descartada e a sessão com mais lugares livres é precisamente a que desaparece da lista.

```
-- a condição no ON filtra antes de juntar
SELECT s.id, s.lotacao - COUNT(b.id) AS livres
FROM sessoes AS s
LEFT JOIN bilhetes AS b
  ON b.sessao_id = s.id AND b.estado <> 'cancelado'
WHERE s.evento_id = 1
GROUP BY s.id;
```

Os cancelados ficam de fora da contagem e as sessões vazias continuam na lista com os lugares todos livres. Regra geral: condições da tabela opcional vão no `ON`, condições da pesquisa vão no `WHERE`.

Corre a versão certa sobre dados de exemplo e confirma os lugares livres. A sessão 1 tem lotação 100 com um bilhete pago e um cancelado, por isso imprime 99. A sessão 2 tem lotação 50 sem bilhetes e continua na lista com 50:

```sql
PRAGMA foreign_keys = ON;
CREATE TABLE sessoes (
    id INTEGER PRIMARY KEY,
    evento_id INTEGER NOT NULL,
    data_hora TEXT NOT NULL,
    preco NUMERIC(8, 2) NOT NULL,
    lotacao INTEGER NOT NULL CHECK (lotacao > 0)
);
CREATE TABLE bilhetes (
    id INTEGER PRIMARY KEY,
    utilizador_id INTEGER NOT NULL,
    sessao_id INTEGER NOT NULL REFERENCES sessoes (id),
    codigo TEXT NOT NULL UNIQUE,
    estado TEXT NOT NULL CHECK (estado IN ('reservado', 'pago', 'cancelado'))
);
INSERT INTO sessoes (evento_id, data_hora, preco, lotacao) VALUES
(1, '2026-12-14 21:30', 12.50, 100),
(1, '2026-12-15 21:30', 12.50, 50);
INSERT INTO bilhetes (utilizador_id, sessao_id, codigo, estado) VALUES
(7, 1, 'B-1041', 'pago'),
(8, 1, 'B-1042', 'cancelado');
SELECT s.id, s.data_hora, s.preco,
       s.lotacao - COUNT(b.id) AS livres
FROM sessoes AS s
LEFT JOIN bilhetes AS b
  ON b.sessao_id = s.id AND b.estado <> 'cancelado'
WHERE s.evento_id = 1 AND s.data_hora > '2026-01-01'
GROUP BY s.id;
```

A saída mostra `1|2026-12-14 21:30|12.5|99` e `2|2026-12-15 21:30|12.5|50`. A data de corte está fixa porque o motor desta página não tem o `NOW()` do PostgreSQL. O raciocínio do `ON` contra o `WHERE` é o mesmo nos dois motores.

Registar a compra de um bilhete:

```
INSERT INTO bilhetes (utilizador_id, sessao_id, codigo, estado)
VALUES (7, 12, 'B-1042', 'reservado')
RETURNING id;
```

O `RETURNING id` devolve o identificador criado sem uma segunda interrogação. Guarda esse valor: é ele que a página de confirmação mostra e que o pagamento usa a seguir.

Histórico de compras de um utilizador, do mais recente para o mais antigo:

```
SELECT e.titulo, s.data_hora, b.codigo, b.estado
FROM bilhetes AS b
JOIN sessoes AS s ON s.id = b.sessao_id
JOIN eventos AS e ON e.id = s.evento_id
WHERE b.utilizador_id = 7
ORDER BY s.data_hora DESC;
```

Três tabelas ligadas pelas chaves estrangeiras do esquema. Cada `JOIN` segue uma associação do modelo, o que confirma que a conversão foi bem feita: se precisasses de uma coluna calculada à mão para ligar duas tabelas, faltava uma chave estrangeira.

## Ler o plano com EXPLAIN

Antes de otimizar, vê como o PostgreSQL executa a pesquisa de eventos por data:

```
EXPLAIN SELECT id, titulo FROM eventos
WHERE categoria = 'musica';
```

Sem índice, o plano mostra um varrimento sequencial: o motor lê todas as linhas da tabela e filtra. Numa tabela pequena a saída parece-se com isto:

```
Seq Scan on eventos  (cost=0.00..35.50 rows=2550 width=36)
  Filter: (categoria = 'musica'::text)
```

A linha `Seq Scan` é o diagnóstico: o motor percorre a tabela inteira. Com mil eventos isto é instantâneo, mas é o padrão que progride mal. A pesquisa por data das sessões é a interrogação mais frequente da loja, por isso merece um índice:

```
CREATE INDEX idx_sessoes_data ON sessoes (data_hora);
```

Repete o `EXPLAIN` numa pesquisa por intervalo de datas e o plano passa a pesquisa por índice: o motor salta diretamente para as linhas do intervalo em vez de varrer a tabela.

```
Index Scan using idx_sessoes_data on sessoes  (cost=0.29..8.31 rows=1 width=72)
  Index Cond: ((data_hora > '2026-12-01 00:00:00') AND (data_hora < '2026-12-31 23:59:59'))
```

Os valores de custo mudam com os dados e as estatísticas da tabela; o que lês no plano é o nome do nó. `Seq Scan` significa varrer tudo, `Index Scan using` seguido do nome do índice significa saltar para as linhas. O ganho aparece quando a tabela cresce e a condição seleciona poucas linhas.

![Comparação de duas estratégias sobre cinco linhas. Sem índice, uma seta percorre as cinco linhas e lê todas. Com índice em categoria, uma seta parte da caixa de índice e chega só às três linhas rock.](https://resumos.rgo.pt/cadeiras/lbaw/sql-indices/figura-1.svg)

O desenho mostra a mesma diferença sem números: sem índice lês as cinco linhas para ficar com três, com índice lês só as três.

[Vídeo: How to read and understand Explain Query Plan in Postgresql](https://www.youtube.com/watch?v=YxLl_SAaRhc)

A miniatura vem do YouTube. O vídeo só carrega quando clicas. [Abrir no YouTube](https://www.youtube.com/watch?v=YxLl_SAaRhc)

## Para saber mais

*   [Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html), o guia oficial para ler planos com `EXPLAIN` e `EXPLAIN ANALYZE`.
*   [Reading an EXPLAIN ANALYZE query plan](https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan), artigo passo a passo a ler um plano real.

Índices não são gratuitos

Cada índice acelera leituras e atrasa escritas, porque cada `INSERT` e `UPDATE` tem de atualizar todos os índices da tabela. Indexa as colunas das pesquisas frequentes, como datas e chaves estrangeiras usadas em junções, e não as colunas que quase nunca filtras. Um índice por coluna “para o caso de ser preciso” deixa a escrita mais lenta sem leituras que o paguem.