Passo-a-passo detalhado do skill, referenciando as fases cognitivas:
1SENSE — Entender o requisito de dados
O que a query precisa retornar? Qual é o caso de uso (relatório, listagem, API)?
Verificar schema: `SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'users'`
2CONTEXTUALIZE — Analisar índices e volume
```sql
-- Verificar índices existentes no PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- Volume de linhas
SELECT relname, reltuples::bigint FROM pg_class WHERE relname IN ('users', 'orders');
-- Queries lentas
SELECT query, calls, total_exec_time/calls as avg_ms, rows/calls as avg_rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
```
3HYPOTHESIZE — Projetar a query
```sql
-- ANTES: N+1 problem (1 query por usuário)
SELECT * FROM users WHERE active = true;
-- para cada usuário: SELECT COUNT(*) FROM orders WHERE user_id = ?
-- DEPOIS: Single query com aggregation
SELECT
u.id, u.name, u.email,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_revenue,
MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
GROUP BY u.id, u.name, u.email
ORDER BY total_revenue DESC;
```
4EVALUATE — Analisar execution plan
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
GROUP BY u.id;
```
Red flags: Seq Scan em tabela grande, Sort em coluna não indexada, Nested Loop com muitas linhas
5RECOMMEND — Sugerir índices
```sql
-- Índice para o WHERE clause
CREATE INDEX CONCURRENTLY idx_users_active ON users(active) WHERE active = true;
-- Índice composto para o JOIN + filtro
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC);
```
6REFLECT — Validar melhoria
Comparar `actual time` no EXPLAIN ANALYZE antes e depois dos índices
Verificar que estatísticas estão atualizadas: `ANALYZE users; ANALYZE orders;`
Reportar telemetria via mcp-skillschain
SynapticSkills