# L3.4 — Queries lentas / N+1 / índices de origem

**Objetivo (A1):** eliminar *sequential scans* e N+1 das rotas públicas de portal,
reduzindo o tempo de DB por requisição (`origem`/`ttfb`). Complementa L2.3 (FPC)
e L2.4 (cache de layout): o FPC zera o custo de DB no HIT, mas no MISS (1ª vez ou
após `PageCache::flush()` por publicação) e nas rotas não cacheadas (busca, CMS)
o custo de DB continua integral. Esta tarefa ataca o custo de DB em si.

Seed: `329d` (Ver L2.4. Profiling de queries lentas em paginas publicas;
adicionar indices e eager loading onde necessario).

## Diagnóstico

Inspeção do banco de testes (`prefeituras_v5`, pgsql) via `information_schema`/
`pg_indexes`: as tabelas de conteúdo **não tinham NENHUM índice secundário** —
apenas PK. Todo `where('slug')`, `where('category_contents_id')`, `where('status')`,
`where('publication_date')`, `where('key')` era *sequential scan*.

Consultas repetidas pesadas por request (`PortalController::index`):

- `getTemplateActived()` (`app/Http/Traits/GetInformationsPrefectureTrait.php:92`)
  faz `Configuration::where('key','layout')->first()` **e** um `UPDATE` de side-effect
  (`pagina_em_manutencao`) — e era invocado ~6× por request.
- `CategoryContent::where('slug', X)->first()` repetido (`PortalController.php:165,168,200`).
- `whereHas('categoryContents', …)` aninhados (belongsTo, não N:N) em
  `slidesNews`/`directContents`/`allTransparencyContents` — seq scan em
  `contents`+`category_contents`.
- `DB::select` cru com interpolação de `$category->id` (`PortalController.php:349`).
- `categoryContents()` é `belongsTo` (`Content.php:107`) → N+1 em views que acessam
  `$content->categoryContents` sem `with`.

## Fase 1 — Índices (migração)

`database/migrations/2026_09_10_120000_add_performance_indexes_l34.php`
(`up()` cria, `down()` remove — todos **non-unique** para não quebrar dados
legados com `slug`/`key` duplicados):

| Tabela | Índices |
|---|---|
| `contents` | `slug`; `(category_contents_id, status, publication_date)`; `(highlights, publication_date)`; `(carousel, category_contents_id)`; `deleted_at`; `order` |
| `category_contents` | `slug`; `(category_contents_id, status, order)` |
| `archives` | `contents_id`; `static_pages_id` |
| `configurations` | `key` |
| `calendar_events` | `status` |
| `static_pages` | `slug` |
| `highlights` | `(status, layout, position)` |
| `activity_log` | `(description, created_at)` |

Aplicado em container: `php artisan migrate --force`. 16 índices criados
(anteriormente só PKs + 1 índice legado em `activity_log`).

> **Deploy em produção:** em tabela grande, `Schema::table()->index()` faz
> `CREATE INDEX` bloqueante. Fora de horário crítico, usar
> `CREATE INDEX CONCURRENTLY` (pgsql) ou janela de manutenção. O app suporta
> pgsql/mysql; as definições usam `->index()` portátil.

## Fase 2 — Perfilamento / verificação do planner

Teste automatizado `tests/Unit/PerformanceIndexTest.php` (roda DENTRO do
container, contra pgsql):

1. `testIndexesExist` — afirma que os 15 índices existem (`pg_indexes`).
2. `testHotQueriesUseIndexes` (data-provider, 14 queries quentes) — desliga
   `enable_seqscan` (`SET enable_seqscan = off`) e faz `EXPLAIN` de cada query,
   afirmando **Index Scan** e **nunca Seq Scan**. O disable de seqscan torna o
   teste robusto mesmo com tabelas vazias (força o otimizador a usar o índice).

Resultado: **15 testes, 43 asserções — OK**.

## Fase 3 — Correções de código

Todas validadas por `tests/Unit/EagerLoadMemoizeTest.php` (2 testes, OK):

- **Memoização de `getTemplateActived()`** (`GetInformationsPrefectureTrait.php:92`):
  cache estático por request da leitura de `Configuration` (1 query, não 1 por
  chamada). O `UPDATE`/`abort` de `pagina_em_manutencao` é preservado (só roda se
  a view de layout não existir).
- **Eager loading `categoryContents`** em `getContentByCategoryWith`
  (`GetInformationsPrefectureTrait.php`) e em `getContentsByParentCategory`
  (`NewsTrait.php:164` — 2 queries) e em `$sessions` (`PortalController.php:144`):
  elimina N+1 ao iterar conteúdos na home/listagens.
- **`DB::select` cru → Eloquent** (`PortalController.php:349`): substituído por
  `Content::where('category_contents_id', $id)->whereNull('deleted_at')`
  `->selectRaw('extract(year from publication_date) as ano')->groupBy('ano')`
  `->orderByDesc('ano')->get()` — sem interpolação de string (bindings) e usa o
  novo índice em `category_contents_id`. Removido o `use DB` órfão do controller.

## Validação

```
docker exec public_infra_update-app-1 php vendor/bin/phpunit
```

- `PerformanceIndexTest` (Fase 1+2): **15/15 OK**.
- `EagerLoadMemoizeTest` (Fase 3): **2/2 OK**.
- Suíte completa: **55 testes, 327 asserções** — única falha é o stub
  `Tests\Feature\ExampleTest` (redirect 302 em `/`), pré-existente e sem relação
  com esta tarefa.
- `php -l` limpo nos 3 arquivos alterados + migração.

## Riscos / mitigação

- Migração bloqueante em tabela grande → `CONCURRENTLY`/janela (Fase 1).
- `getTemplateActived()` tem write em GET → comportamento preservado; só a
  leitura foi memoizada (Fase 3).
- Índices non-unique para não falhar em `slug`/`key` duplicados.

## Aceitação

- EXPLAIN das queries quentes sem Seq Scan (teste automatizado).
- Sem regressão de HTML (índices não alteram dados; eager loading só reduz queries).
- No MISS do FPC e em rotas de busca/CMS, redução de custo de DB por uso de
  índices + eliminação de N+1.