PostgreSQL na Prática: Queries de Alta Performance e Índices Inteligentes

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as total_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
ORDER BY total_orders DESC
LIMIT 20;

-- Atualizar statistics quando os dados mudam muito
ANALYZE users;
ANALYZE orders;
 
-- Ou tudo de uma vez
ANALYZE VERBOSE;

-- ❌ Query usa created_at e status mas o índice está errado
CREATE INDEX idx_orders_status ON orders(status);
-- Se a query filtra status = 'pending' E ordena por created_at,
-- o banco ainda faz sort depois do filter
 
-- ✅ Índice composto na ordem correta
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- Agora a query usa o índice para filter E sort em um só passo

-- Índice em TODA a tabela orders (cara)
CREATE INDEX idx_orders_status ON orders(status);
 
-- Índice só nos pedidos pendentes (80% menores, mais rápido)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
 
-- A query que usa:
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;
-- Usa o índice parcial perfeitamente

-- Busca case-insensitive sem índice → Seq Scan
SELECT * FROM users WHERE LOWER(email) = 'joao@email.com';
 
-- Criar índice na expressão
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
 
-- Agora usa o índice!
SELECT * FROM users WHERE LOWER(email) = 'joao@email.com';

-- Tabela com coluna JSONB
ALTER TABLE products ADD COLUMN metadata JSONB;
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);
 
-- Query que aproveita o GIN index
SELECT * FROM products
WHERE metadata @> '{"category": "electronics", "in_stock": true}';
 
-- Para arrays
CREATE TABLE posts (tags TEXT[]);
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
 
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

-- Ranking de vendedores por região
SELECT
  seller_name,
  region,
  total_sales,
  RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) as rank_in_region,
  ROUND(100.0 * total_sales / SUM(total_sales) OVER (PARTITION BY region), 2) as pct_of_region
FROM sales_summary
ORDER BY region, rank_in_region;
 
-- Média móvel de 7 dias
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d
FROM daily_revenue
ORDER BY date;
 
-- Diferença em relação ao dia anterior
SELECT
  date,
  revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) as day_over_day_change
FROM daily_revenue;

-- Categorias com subcategorias aninhadas
WITH RECURSIVE category_tree AS (
  -- Base: categorias raiz
  SELECT id, name, parent_id, 0 as depth, name::TEXT as path
  FROM categories
  WHERE parent_id IS NULL
 
  UNION ALL
 
  -- Recursão: filhas das categorias já encontradas
  SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || ' > ' || c.name
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY path;
 
-- Resultado:
-- Electronics
-- Electronics > Phones
-- Electronics > Phones > Smartphones
-- Electronics > Laptops

-- Criar tabela particionada por mês
CREATE TABLE events (
  id BIGSERIAL,
  user_id INT NOT NULL,
  event_type VARCHAR(50),
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
 
-- Criar partições
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
 
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
 
CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
 
-- Índice em cada partição (automático para índices globais no PG 17)
CREATE INDEX ON events_2026_03(user_id, created_at DESC);
 
-- Query usa APENAS a partição correta (partition pruning)
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
AND user_id = 42;

-- Inserir ou atualizar se já existe (atomicamente)
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON CONFLICT (user_id) DO UPDATE
  SET login_count = user_stats.login_count + 1,
      last_login = NOW()
WHERE user_stats.last_login < NOW() - INTERVAL '1 hour';
 
-- Ignorar duplicatas silenciosamente
INSERT INTO event_log (event_id, processed_at)
VALUES (123, NOW())
ON CONFLICT (event_id) DO NOTHING;

-- Ver índices não utilizados (candidatos a remover)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'
ORDER BY schemaname, tablename;
 
-- Ver queries mais lentas (requer pg_stat_statements)
SELECT query, calls, total_exec_time / calls as avg_ms, rows / calls as avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY avg_ms DESC
LIMIT 20;
 
-- Ver locks que estão bloqueando queries
SELECT blocked.pid, blocked.query, blocking.pid as blocking_pid, blocking.query as blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';