Teste de domínio

Doze perguntas e um caso, intercalados: não seguem a ordem das lições e não dizem de que lição vêm. É de propósito — na vida real ninguém te diz de que capítulo é o problema.

Como funciona

⭐ Regras

Sem consultar as lições nem a folha. Se consultares, marca a pergunta com (consultei) — é informação útil para a correção, não uma falta.
Não há soluções nesta página. A correção vem de /corrigir indices-btree-sql depois de entregares.
Se não souberes, escreve o que sabes e onde encalhaste. «Não sei» corrige-se; uma resposta vaga a fingir que sabe é a pior entrega possível, porque esconde onde está o buraco.
Conta ~45 minutos. A parte 4 vale tanto como as outras três juntas.

Parte 1 — mecanismo

1. Uma tabela tem 12 000 páginas e 1 500 000 linhas. Calcula o custo que o planeador atribui a SELECT * FROM t WHERE x = 5 (sem índice, valores por omissão). Mostra as parcelas.

2. bt_metap devolve level = 1. Quantas leituras de página custa localizar uma chave, e porquê?

3. Um índice sobre uma coluna com 800 valores distintos numa tabela de 4 000 000 de linhas tem muito menos entradas nas folhas do que 4 000 000. Porquê? Que efeito tem isso no tamanho e na altura?

4. Explica porque é que WHERE nome LIKE 'Ana%' pode usar um índice B-tree e WHERE nome LIKE '%Silva' não pode. A resposta tem de falar da estrutura, não da sintaxe.

Parte 2 — ler planos

5. Que conclusões tiras do excerto abaixo? Diz o que está bem e o que está mal.

Index Only Scan using idx_a on t  (actual time=0.03..14.2 rows=9000 loops=1)
  Heap Fetches: 17400
  Buffers: shared hit=9200 read=140

6. Um nó diz (actual time=0.004..0.005 rows=3 loops=22000) numa query que demora 130 ms ao todo. Quantas linhas produziu, quanto tempo gastou, e que fração da query é?

7. O plano diz Index Scan using idx_bc on t com Buffers: shared read=2480. pg_relation_size('idx_bc')/8192 dá 2500. O índice é (b, c) e a query filtra por WHERE c = 9. Explica o que aconteceu e o que farias.

8. Correste a mesma query duas vezes: 310 ms e depois 8 ms, com o mesmo plano. O que mudou, e que número usarias para comparar duas versões de uma query?

Parte 3 — decidir

9. Estas são as queries quentes de uma tabela faturas:
(a) WHERE empresa_id = ? AND emitida_em BETWEEN ? AND ?
(b) WHERE empresa_id = ? AND estado = 'rascunho' — rascunhos são 0,5% das linhas
(c) WHERE numero = ? — único
Propõe os índices, com a ordem das colunas justificada, e diz se algum deve ser parcial.

10. Um colega quer CREATE INDEX ON pedidos ((date_part('month', criado_em))), onde criado_em é timestamptz. O que acontece quando ele corre isto? E qual é a solução que tu preferes — e porquê?

11. Uma query devolve 0,8% das linhas, usa o índice, e o BUFFERS mostra que leu praticamente todas as páginas da tabela. Explica como é possível e diz que estatística te teria avisado — e porque é que essa estatística, sozinha, pode enganar.

12. Alguém diz-te: «encontrei num artigo que o Postgres deixa de usar índices acima de 20% das linhas». Responde-lhe em três ou quatro frases.

Parte 4 — caso completo

⚠️ Esta parte vale tanto como as outras três juntas

Não é por ser mais difícil — é por ser a única que mede se consegues juntar as peças.

O caso

Tabela leituras, 40 000 000 de linhas, ~9000 INSERTs por minuto, sem UPDATEs. Colunas: id, sensor_id, medida, estado ('ok' 99,2%, 'alerta' 0,8%), registada_em.

Índices atuais: leituras_pkey (id) · (sensor_id) · (registada_em) · (estado).

Um painel corre de 30 em 30 segundos:

SELECT sensor_id, max(medida) FROM leituras
WHERE estado = 'alerta' AND registada_em > now() - interval '1 hour'
GROUP BY sensor_id;

Demora 6 s. O plano:

HashAggregate  (actual time=5980.2..5981.0 rows=140 loops=1)
  ->  Bitmap Heap Scan on leituras  (actual time=410.3..5890.1 rows=2400 loops=1)
        Recheck Cond: (estado = 'alerta'::text)
        Filter: (registada_em > (now() - '01:00:00'::interval))
        Rows Removed by Filter: 317600
        Heap Blocks: exact=41200 lossy=98000
        Buffers: shared hit=12000 read=127200
        ->  Bitmap Index Scan on leituras_estado  (actual time=95.1..95.1 rows=320000 loops=1)
              Index Cond: (estado = 'alerta'::text)

13a. Onde está o tempo, e como é que o plano o diz?

13b. Aponta dois sinais concretos de trabalho desperdiçado ou de recursos insuficientes, e diz o que cada um significa.

13c. Propõe uma alteração de índices. Justifica a ordem das colunas e diz se é parcial. Estima o que ganhas e o que pagas — lembrando os 9000 INSERTs por minuto.

13d. Que índice existente ficaria redundante com a tua proposta? E que verificações fazes antes de o apagar?

13e. Como confirmas que a tua proposta resulta, sem deixar o índice criado em produção? Escreve os comandos.

Entregar

O botão gera o Markdown com todas as respostas. Cola-o em topicos/indices-btree-sql/respostas/AAAA-MM-DD-teste.md e corre /corrigir indices-btree-sql.

Texto da entrega (só aparece se a cópia automática falhar)

As respostas ficam guardadas no teu browser enquanto não fechares — mas o armazenamento local pode falhar sem aviso. Copia a entrega antes de fechar a página.