d. Índices e EXPLAIN

Desde o lab 1, a listagem do catálogo — próximos eventos numa cidade, os mais próximos primeiro — é o endpoint lento. O lab 5 a escondeu atrás de um cache. Antes de recorrer a mais infraestrutura, pergunte ao banco como ele executa a consulta. Muitas vezes, a decisão de escala mais barata de todas é um índice.

1. Leia o plano

A consulta gerada por findUpcomingInCity (página + contagem) equivale a:

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.*, v.*
FROM catalog.events e JOIN catalog.venues v ON v.id = e.venue_id
WHERE v.city = 'Recife' AND e.starts_at >= now()
ORDER BY e.starts_at, e.id
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM catalog.events e JOIN catalog.venues v ON v.id = e.venue_id
WHERE v.city = 'Recife' AND e.starts_at >= now();

docker compose exec db psql -U boxoffice

Antes de qualquer índice, o plano fica assim (de uma execução de teste deste lab, resumido; os seus números vão diferir):

Limit  (actual time=35.620..36.762 rows=20)
  ->  Gather Merge
        ->  Sort  (Sort Key: e.starts_at, e.id; Sort Method: top-N heapsort)
              ->  Hash Join  (Hash Cond: (e.venue_id = v.id))
                    ->  Parallel Seq Scan on events e  (rows=100000 loops=2)
                    ->  Hash  ->  Seq Scan on venues v  (Filter: city = 'Recife'; rows=5)
Execution Time: 36.802 ms          -- and 13.9 ms more for the count(*)

Como ler um plano:

O problema está à vista: 200.000 linhas são lidas e ordenadas para devolver 20.

2. Acrescente os índices

Adicione uma nova migração ao catalog — nunca edite V1/V2:

1
2
3
4
5
6

-- Lab 6: indexes for the listing "upcoming events in a city, soonest first".
-- Walk events in starts_at order and stop after one page, instead of sorting 20,000 rows:
CREATE INDEX events_starts_at_idx ON catalog.events (starts_at, id);
-- Count/filter the events of a city's venues without reading the whole table:
CREATE INDEX events_venue_starts_at_idx ON catalog.events (venue_id, starts_at);
CREATE INDEX venues_city_idx ON catalog.venues (city);

docker compose up --build -d catalog-1 catalog-2 catalog-3      # Flyway applies V3 on startup
docker compose logs catalog-1 catalog-2 catalog-3 | grep "V3\|version \"3"   # one replica applies it

Rode os dois EXPLAIN ANALYZE de novo:

Limit  (actual time=0.064..0.442 rows=20)
  ->  Nested Loop
        ->  Index Scan using events_starts_at_idx on events e  (rows=189)
        ->  Index Scan using venues_pkey on venues v  (loops=189)
Execution Time: 0.620 ms

Aggregate  (actual time=4.006..4.006 rows=1)
  ->  Nested Loop
        ->  Seq Scan on venues v  (rows=5)
        ->  Index Only Scan using events_venue_starts_at_idx on events e  (rows=4000 loops=5)
Execution Time: 4.028 ms

Com o events_starts_at_idx, o PostgreSQL percorre os eventos já em ordem de data e para assim que tem 20 de Recife (leu 189 entradas do índice em vez de 200.000 linhas): a consulta da página cai de ~37 ms para bem menos de 1 ms. A contagem ainda visita todos os eventos da cidade, mas pelo índice menor (venue_id, starts_at), sem tocar a tabela (Index Only Scan): ~14 ms → ~4 ms.

3. Meça

Desligue o cache para que toda requisição chegue ao PostgreSQL e repita o sweep dos labs 2/3 pelo edge:

CACHE_ENABLED=false docker compose up -d catalog-1 catalog-2 catalog-3 && sleep 30
BASE_URL=http://localhost OUT=results/lab6-index bash scripts/sweep.sh 300 600 900 1200 1500
uv run scripts/summarize.py results/lab6-index/rate-*.json

Compare com os números sem cache do lab 3. Depois religue o cache e decida, com números, do que a listagem realmente precisava.

Próximo: 4. Failover.