Índices parciais do PostgreSQL

Os índices no PostgreSQL são objetos de banco de dados que melhoram a velocidade das operações de recuperação de dados em uma tabela. Os índices parciais são um tipo de índice que só inclui linhas no índice se elas atenderem a uma determinada condição, otimizando o desempenho e reduzindo o armazenamento.

Uso

Os índices parciais são usados quando você deseja indexar um subconjunto de linhas em uma tabela, normalmente aquelas que atendem a critérios específicos, para melhorar o desempenho da consulta e minimizar o uso de espaço em disco. Eles são definidos pela adição de uma cláusula WHERE à definição do índice.

CREATE INDEX index_name
ON table_name (column_name)
WHERE condition;

Nessa sintaxe, o WHERE condition especifica quais linhas devem ser incluídas no índice.

Exemplos

1. Índice parcial básico

CREATE INDEX active_users_index
ON users (last_login)
WHERE active = true;

Este exemplo cria um índice parcial na tabela users para a coluna last_login, mas somente para as linhas em que o campo active é true.

2. Indexação com condições de valor específicas

CREATE INDEX high_priority_orders_index
ON orders (order_date)
WHERE priority = 'high';

Aqui, um índice parcial é criado na tabela orders para a coluna order_date, concentrando-se apenas em pedidos com um priority de 'high'.

3. Índice parcial de condições complexas

CREATE INDEX recent_high_sales_index
ON sales (sale_amount)
WHERE sale_amount > 1000 AND sale_date > CURRENT_DATE - INTERVAL '1 year';

Este exemplo cria um índice parcial na tabela sales para a coluna sale_amount para vendas superiores a US$ 1.000 no último ano, otimizando as consultas para transações recentes de alto valor.

4. Índice parcial com expressões

CREATE INDEX discounted_products_index
ON products ((price - discount))
WHERE discount > 0;

Este exemplo demonstra o uso de uma expressão no índice, criando um índice parcial no site price - discount calculado para produtos com desconto.

Dicas e práticas recomendadas

• Direcione as consultas frequentes. Use índices parciais para consultas que acessam frequentemente um subconjunto específico de dados para maximizar os benefícios de desempenho.
• Monitorar o uso do índice. Analise regularmente o uso do índice para garantir que ele ainda seja benéfico, pois os padrões de dados e os requisitos de consulta podem mudar com o tempo.
• Minimizar o tamanho do índice. Seja específico na cláusula WHERE para manter o tamanho do índice pequeno e eficiente, evitando a indexação de linhas desnecessárias.
• Impacto no desempenho do teste. Depois de criar um índice parcial, teste o desempenho da consulta para garantir que ele ofereça as melhorias de velocidade desejadas.
• Considere as operações de gravação. Esteja ciente de que os índices parciais podem afetar o desempenho de gravação devido à sobrecarga de manutenção dos índices durante inserções, atualizações e exclusões.
• Use REINDEX. Use regularmente o comando REINDEX para reconstruir índices parciais e manter sua eficiência, especialmente após alterações significativas nos dados.
• Alavancagem EXPLAIN. Utilize o comando EXPLAIN para obter insights sobre como as consultas utilizam índices parciais e ajustar sua estratégia de indexação de acordo.

Considerações sobre desempenho e versão

• Os índices parciais são particularmente benéficos para tabelas com grandes volumes de dados em que apenas um pequeno subconjunto é consultado com frequência.
• A manutenção de índices requer recursos, portanto, avalie a compensação entre o desempenho de leitura e gravação.
• Certifique-se de que você é compatível com a versão do PostgreSQL, pois os aprimoramentos nas estratégias de indexação podem variar entre as versões.