Índices que funcionam: B-tree, hash, GIN, covering, composto

-- Criando índices básicos
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
CREATE INDEX idx_pedidos_status ON pedidos(status);
CREATE INDEX idx_pedidos_criado ON pedidos(criado_em);

-- Verificar índices de uma tabela:
d pedidos   -- no psql
-- ou:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'pedidos';

-- Quando o índice É usado:
-- ✅ WHERE cliente_id = 42          -- equality
-- ✅ WHERE cliente_id IN (1, 2, 3)  -- IN list
-- ✅ WHERE criado_em > '2024-01-01' -- range scan
-- ✅ ORDER BY cliente_id LIMIT 10   -- sort com limit (index scan evita sort)
-- ✅ COUNT(*) com WHERE indexado    -- pode usar index scan

-- Quando o índice NÃO é usado (ou não deveria ser):
-- ❌ WHERE UPPER(email) = 'USER@EX.COM'   -- função na coluna destrói índice
-- ❌ WHERE id + 1 = 42                    -- expressão na coluna indexada
-- ❌ WHERE status LIKE '%pendente%'        -- % no início impede uso de índice
-- ❌ SELECT * FROM pedidos               -- sem WHERE, Seq Scan é melhor
-- ❌ Tabelas muito pequenas (Seq Scan é mais rápido)

-- Para funções na coluna — índice funcional/expressão:
CREATE INDEX idx_email_lower ON clientes(LOWER(email));
-- Agora funciona: WHERE LOWER(email) = 'user@ex.com'

-- Índice composto — leftmost prefix rule
CREATE INDEX idx_pedidos_cliente_status ON pedidos(cliente_id, status);

-- Usa o índice ✅ (prefixo da esquerda):
SELECT * FROM pedidos WHERE cliente_id = 42;
SELECT * FROM pedidos WHERE cliente_id = 42 AND status = 'pendente';

-- NÃO usa o índice ❌ (só coluna da direita):
SELECT * FROM pedidos WHERE status = 'pendente';
-- Solução: criar índice separado em (status)

-- Ordem das colunas no índice composto importa:
-- (cliente_id, status): bom para queries por cliente, ou (cliente, status)
-- (status, cliente_id): bom para queries por status, ou (status, cliente)
-- Regra: coluna mais seletiva (menos valores repetidos) primeiro — geralmente

-- Índice composto para ORDER BY eficiente:
CREATE INDEX idx_criado_status ON pedidos(criado_em DESC, status);
-- Agora: SELECT * FROM pedidos ORDER BY criado_em DESC LIMIT 10
-- usa Index Scan sem Sort adicional!

-- Índice parcial — indexa só parte da tabela
CREATE INDEX idx_pedidos_pendentes ON pedidos(criado_em)
WHERE status = 'pendente';
-- Índice menor, mais rápido, menos memória
-- Só funciona para queries que incluem o WHERE do índice

-- Índice GIN para JSONB e arrays
CREATE INDEX idx_produtos_jsonb ON produtos USING GIN (atributos);
-- Habilita: WHERE atributos @> '{"cor": "azul"}'
-- e: WHERE atributos ? 'desconto'  (chave existe)

CREATE INDEX idx_artigos_tags ON artigos USING GIN (tags);
-- Habilita: WHERE tags @> ARRAY['python', 'sql']

-- Índice GIN para full-text search
CREATE INDEX idx_artigos_fts ON artigos
USING GIN (to_tsvector('portuguese', titulo || ' ' || corpo));
-- Habilita: WHERE to_tsvector('portuguese', titulo || ' ' || corpo)
--           @@ to_tsquery('portuguese', 'sql & performance')

-- Covering index com INCLUDE
CREATE INDEX idx_pedidos_covering ON pedidos(cliente_id)
INCLUDE (total, status, criado_em);
-- Query: SELECT total, status FROM pedidos WHERE cliente_id = 42
-- Resultado: Index-Only Scan — não toca no heap

-- Criar índice sem bloquear produção (CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_novo ON tabela_grande(coluna);
-- Demora mais, mas não bloqueia writes durante a criação