7.2. Índices - Tópicos avançados

Índices: Os índices são estruturas utilizadas para melhorar o desempenho das consultas em um banco de dados. Eles aceleram a busca de dados em uma tabela, permitindo que as consultas sejam executadas de forma mais eficiente. É importante entender como criar e gerenciar índices adequados para otimizar o desempenho das consultas e melhorar a velocidade de execução das operações no banco de dados.

Índices compostos

Em vez de criar um único índice em uma coluna, é possível criar um índice que abrange múltiplas colunas. Isso pode ser útil quando as consultas envolvem uma combinação de colunas em cláusulas de filtro ou em cláusulas de junção. Índices compostos podem melhorar significativamente o desempenho de consultas complexas.

Para entender melhor os Índices compostos vamos criar uma tabela de exemplo com várias colunas e demonstrar como criar um índice composto que abranja duas ou mais colunas. Em seguida, podemos executar consultas que se beneficiem desse índice para mostrar o impacto no desempenho das consultas.

Exemplo de Índices compostos:

• Crie uma tabela chamada clientes com colunas como id, nome, sobrenome, email e telefone:

-- Criação da tabela clientes
CREATE TABLE clientes (
id SERIAL PRIMARY KEY,
nome VARCHAR(100),
sobrenome VARCHAR(100),
email VARCHAR(100),
telefone VARCHAR(20)
);

• Insira um conjunto de dados para teste, por exemplo 5000 registros:

-- Inserção de dados fictícios na tabela clientes
INSERT INTO clientes (nome, sobrenome, email, telefone)
SELECT 
    'Nome ' || generate_series,
    'Sobrenome ' || generate_series,
    'email' || generate_series || '@example.com',
    'Telefone ' || generate_series
FROM generate_series(1, 5000) -- Altere o valor para ajustar a quantidade de registros desejada
;

• Execute a consulta com EXPLAIN ANALYZE e observe o resultado:

-- Exemplo de consulta sem utilizar o índice composto
$ EXPLAIN ANALYZE SELECT * FROM clientes WHERE sobrenome = 'Sobrenome 33' AND email = 'email33@example.com';
                            QUERY PLAN                              
-----------------------------------------------------------------
 Seq Scan on clientes  (cost=0.00..136.00 rows=1 width=61) (actual time=0.026..1.352 rows=1 loops=1)
   Filter: (((sobrenome)::text = 'Sobrenome 33'::text) AND ((email)::text = 'email33@example.com'::text))
   Rows Removed by Filter: 4999
 Planning Time: 0.363 ms
 Execution Time: 1.384 ms
(5 rows)

• Agora crie um índice composto nas colunas sobrenome e email.

-- Criação do índice composto
CREATE INDEX idx_clientes_sobrenome_email ON clientes (sobrenome, email);

• Por fim, depois de criado o índice, execute novamente a consulta com EXPLAIN ANALYZE e acompanhe o resultado:

-- Exemplo de consulta utilizando o índice composto
$ EXPLAIN ANALYZE SELECT * FROM clientes WHERE sobrenome = 'Sobrenome 33' AND email = 'email33@example.com';
                            QUERY PLAN                              
-----------------------------------------------------------------
 Index Scan using idx_clientes_sobrenome_email on clientes  (cost=0.28..8.30 rows=1 width=61) (actual time=0.050..0.052 rows=1 loops=1)
   Index Cond: (((sobrenome)::text = 'Sobrenome 33'::text) AND ((email)::text = 'email33@example.com'::text))
 Planning Time: 0.210 ms
 Execution Time: 0.092 ms
(4 rows)

As informações fornecidas pelos planos de execução são úteis para avaliar o desempenho das consultas antes e depois da criação do índice. Vamos analisar algumas métricas importantes nos planos de execução:

1. Cost: O custo estimado da execução da consulta. Quanto menor o custo, melhor é o desempenho da consulta.

2. Actual time: O tempo real de execução da consulta em milissegundos. Quanto menor o tempo, mais rápida é a execução da consulta.

3. Rows: O número de linhas retornadas pela consulta. Quanto menor o número de linhas, mais eficiente é a execução da consulta.

Comparando os planos de execução antes e depois da criação do índice, podemos observar as seguintes diferenças:

Antes do índice:

• O plano de execução mostra um "Seq Scan" (leitura sequencial) na tabela, o que indica que não há um índice adequado para a consulta.
• O custo estimado e o tempo de execução são maiores, pois é necessário percorrer todas as linhas da tabela para encontrar os registros desejados.
• O número de linhas removidas pelo filtro é alto, indicando uma busca menos eficiente.

Depois do índice:

• O plano de execução mostra um "Index Scan" (leitura pelo índice) na tabela, o que indica que o índice composto está sendo utilizado.
• O custo estimado e o tempo de execução são menores, pois a busca é feita diretamente no índice, que contém as colunas utilizadas na consulta.
• O número de linhas removidas pelo filtro é menor, indicando uma busca mais eficiente.

Com base nesses resultados, podemos concluir que a criação do índice composto nas colunas sobrenome e email melhorou significativamente o desempenho das consultas que utilizam essas colunas como critérios de filtro. O índice permitiu a realização de uma busca mais eficiente, reduzindo o custo e o tempo de execução da consulta.

É importante destacar que a eficiência dos índices pode variar dependendo dos dados e das consultas específicas do seu banco de dados. Portanto, é recomendado realizar testes e avaliações para determinar quais índices são mais adequados para otimizar o desempenho do seu sistema.

O EXPLAIN ANALYZE através de planos de execução apresentam ainda os indicadores "Planning Time" e "Execution Time" fornecem informações adicionais sobre o tempo de planejamento e execução da consulta.

1. Planning Time: É o tempo gasto pelo PostgreSQL para planejar a execução da consulta. Inclui a análise da consulta, a escolha dos melhores planos de execução e a geração do plano de execução final. Um valor baixo indica que o planejamento da consulta foi rápido.

2. Execution Time: É o tempo real de execução da consulta, excluindo o tempo de planejamento. Indica quanto tempo o PostgreSQL leva para executar efetivamente a consulta no banco de dados. Um valor baixo indica que a execução da consulta foi rápida.

Nota

Em geral, tempos baixos para ambos os indicadores são desejáveis, pois significam que o planejamento e a execução da consulta são eficientes.

No entanto, é importante observar que os tempos podem variar dependendo do tamanho da tabela, da complexidade da consulta e da carga do sistema. É recomendável analisar esses indicadores em várias execuções e comparar com outros planos de execução para avaliar o desempenho de forma mais precisa.

Índices parciais

Em certos casos, criar um índice para uma parte dos dados pode ser mais eficiente do que criar um índice para a tabela inteira. Índices parciais permitem que você defina uma condição de filtro para o índice, de modo que ele seja aplicado apenas aos registros que atendem a essa condição. Isso pode reduzir o tamanho do índice e melhorar o desempenho das consultas relevantes.

Para entender os índices parciais podemos criar uma tabela com uma grande quantidade de registros e aplicar um índice parcial com base em uma condição específica. Em seguida, podemos executar consultas que utilizem essa condição de filtro e comparar o desempenho com e sem o uso do índice parcial.

Exemplo de Índices parciais:

• Crie uma tabela chamada produtos com colunas como id, nome, categoria e disponibilidade:

-- Criação da tabela produtos
CREATE TABLE produtos (
id SERIAL PRIMARY KEY,
nome VARCHAR(100),
categoria VARCHAR(100),
disponibilidade BOOLEAN
);

• Insira um conjunto de dados para teste, por exemplo, 100.000 registros fictícios:

-- Inserção de dados fictícios na tabela produtos
INSERT INTO produtos (nome, categoria, disponibilidade)
SELECT 
    'Produto ' || generate_series,
    'Categoria ' || (generate_series % 100),
    (generate_series % 2 = 0)
FROM generate_series(1, 100000);

• Execute consultas com EXPLAIN ANALYZE que filtrem os registros com disponibilidade = true e observe o desempenho:

-- Exemplo de consulta sem o índice parcial
$ EXPLAIN ANALYZE SELECT * FROM produtos WHERE disponibilidade = true;
                            QUERY PLAN                              
-----------------------------------------------------------------
Seq Scan on produtos  (cost=0.00..861.12 rows=6256 width=441) (actual time=0.035..27.829 rows=50000 loops=1)
   Filter: disponibilidade
   Rows Removed by Filter: 50000
 Planning Time: 0.147 ms
 Execution Time: 31.654 ms
(5 rows)

• Crie um índice parcial para os registros com disponibilidade = true:

-- Criação do índice parcial
CREATE INDEX idx_produtos_disponibilidade ON produtos (disponibilidade) WHERE disponibilidade = true;

• Depois de criado o índice, execute consultas que filtrem os registros com disponibilidade = true e compare o desempenho com e sem o uso do índice parcial.

-- Exemplo de consulta com o índice parcial
$ EXPLAIN ANALYZE SELECT * FROM produtos WHERE disponibilidade = true;
                            QUERY PLAN                              
-----------------------------------------------------------------
Bitmap Heap Scan on produtos  (cost=438.79..1674.79 rows=50000 width=441) (actual time=3.800..15.352 rows=50000 loops=1)
   Recheck Cond: disponibilidade
   Heap Blocks: exact=736
   ->  Bitmap Index Scan on idx_produtos_disponibilidade  (cost=0.00..426.29 rows=50000 width=0) (actual time=3.560..3.560 rows=50000 loops=1)
 Planning Time: 0.360 ms
 Execution Time: 19.139 ms
(6 rows)

Analisando os resultados, podemos observar o seguinte:

Consulta sem o índice parcial:

• O plano de execução mostra um "Seq Scan" (leitura sequencial) na tabela, o que indica que não há um índice adequado para a consulta.
• O custo estimado e o tempo de execução são altos, pois é necessário percorrer todas as linhas da tabela para encontrar os registros desejados.
• O número de linhas removidas pelo filtro é igual ao número total de registros na tabela, indicando uma busca menos eficiente.

Consulta com o índice parcial:

• O plano de execução mostra um "Bitmap Heap Scan" seguido de um "Bitmap Index Scan", o que indica que o índice parcial está sendo utilizado.
• O custo estimado e o tempo de execução são menores, pois a busca é feita diretamente no índice parcial, que contém apenas os registros com disponibilidade = true.
• O número de linhas removidas pelo filtro é igual ao número total de registros encontrados no índice parcial, indicando uma busca mais eficiente.

Com base nesses resultados, podemos concluir que a criação do índice parcial na coluna disponibilidade melhorou significativamente o desempenho da consulta que utiliza essa coluna como critério de filtro. O índice parcial permite a realização de uma busca mais eficiente, reduzindo o custo e o tempo de execução da consulta.

No entanto, é importante destacar que a eficiência dos índices parciais pode variar dependendo dos dados e das consultas específicas do seu banco de dados. Portanto, é recomendado realizar testes e avaliações para determinar quais índices são mais adequados para otimizar o desempenho do seu sistema.

Como já mencionado os indicadores "Planning Time" e "Execution Time" fornecem informações adicionais sobre o tempo de planejamento e execução da consulta...

Índices funcionais

Índices funcionais são índices que são criados com base nos valores de retorno de uma função ou expressão. Eles são úteis quando você precisa fazer consultas em uma tabela com base em valores derivados ou transformados de uma coluna existente. Os índices funcionais podem melhorar o desempenho de consultas que envolvem funções ou expressões complexas.

Para entender melhor os índices funcionais, vamos criar uma tabela de exemplo chamada "funcionarios" com as colunas "id", "nome", "sobrenome" e "data_nascimento". Em seguida, vamos criar um índice funcional para a "idade" dos funcionários com base na coluna "data_nascimento". Isso nos permitirá executar consultas eficientes com base na "idade" dos funcionários.

Exemplo de Índices funcionais:

• Criação da tabela "funcionarios":

-- Criação da tabela funcionarios
CREATE TABLE funcionarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    sobrenome VARCHAR(100),
    data_nascimento DATE
);

• Inserção de dados fictícios na tabela "funcionarios", por exemplo, 100.000 registros:

-- Inserção de 10.000 registros fictícios na tabela funcionarios
INSERT INTO funcionarios (nome, sobrenome, data_nascimento)
SELECT
    'Nome ' || generate_series,
    'Sobrenome ' || generate_series,
    '1990-01-01'::date + (random() * 365)::integer -- Data de nascimento aleatória entre 1990-01-01 e 1991-12-31
FROM generate_series(1, 10000);

• Execute consultas com EXPLAIN ANALYZE que filtrem os registros de funcionário cuja idade >= 30 e observe o desempenho:

-- Exemplo de consulta sem o índice funcional
$ EXPLAIN ANALYZE SELECT * FROM funcionarios WHERE DATE_PART('year', AGE(NOW(), data_nascimento)) >= 30;
                            QUERY PLAN                              
-----------------------------------------------------------------
Seq Scan on funcionarios  (cost=0.00..308.00 rows=3333 width=31) (actual time=0.031..10.702 rows=10000 loops=1)
   Filter: (date_part('year'::text, age(now(), (data_nascimento)::timestamp with time zone)) >= '30'::double precision)
 Planning Time: 0.421 ms
 Execution Time: 11.750 ms
(4 rows)

• Criação da função imutável para cálculo de idade

Existem algumas restrições ao criar índices no PostgreSQL, especialmente para índices funcionais, o PostgreSQL exige que as funções usadas na expressão do índice sejam marcadas como IMMUTABLE. Essa restrição existe porque o PostgreSQL precisa garantir que o valor do índice não mude durante a vida útil do índice.

Dica

Aprenda mais sobre Categorias de Volatilidade da Função acessando a documentação oficial do PostgreSQL clicando aqui

Antes de criar o índice funcional, precisamos criar uma função imutável que calcule a idade de um funcionário com base na data de nascimento. A função será usada na expressão do índice para realizar o cálculo:

-- Criação da função imutável para cálculo de idade
CREATE OR REPLACE FUNCTION calcula_idade(data_nascimento DATE)
RETURNS INTEGER AS $$
BEGIN
  RETURN DATE_PART('year', AGE(NOW(), data_nascimento));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Neste exemplo, criamos uma função chamada "calcula_idade" que recebe a data de nascimento como parâmetro e retorna a idade em anos. A função é marcada como IMMUTABLE, indicando que ela sempre retornará o mesmo resultado para os mesmos argumentos e não possui efeitos colaterais.

• Criação do índice funcional com base na idade dos funcionários usando a função "calcula_idade":

-- Criação do índice funcional
CREATE INDEX "idx_funcionarios_idade" ON funcionarios (calcula_idade(data_nascimento));

Neste exemplo, criamos um índice chamado "idx_funcionarios_idade" na tabela "funcionarios", usando a função "calcula_idade" para calcular a idade dos funcionários com base na coluna "data_nascimento". O índice será criado com base no resultado da função, permitindo consultas e ordenações rápidas com base na idade dos funcionários.

• Consulta utilizando o índice funcional:

-- Exemplo de consulta utilizando o índice funcional
$ EXPLAIN ANALYZE SELECT * FROM funcionarios WHERE calcula_idade(data_nascimento) >= 30;
                            QUERY PLAN                              
-----------------------------------------------------------------
Index Scan using idx_funcionarios_idade on funcionarios  (cost=0.29..406.61 rows=3333 width=31) (actual time=0.047..3.876 rows=10000 loops=1)
   Index Cond: (calcula_idade(data_nascimento) >= 30)
 Planning Time: 0.437 ms
 Execution Time: 4.671 ms
(4 rows)

Neste exemplo, criamos um índice funcional chamado "idx_funcionarios_idade" com base na função "calcula_idade(data_nascimento DATE)", que calcula a idade dos funcionários com base na data atual e na data de nascimento. Em seguida, executamos uma consulta para selecionar os funcionários com idade acima de 30 anos.

Índices de expressão

Semelhante aos índices funcionais, os índices de expressão permitem criar um índice com base em uma expressão matemática ou lógica. Isso pode ser útil quando as consultas envolvem cálculos complexos ou transformações de dados. Os índices de expressão permitem que você otimize consultas que envolvem expressões personalizadas.

Vamos criar um exemplo de índice de expressão para entender como funciona.

• Criação da tabela de exemplo:

-- Criação da tabela produtos
CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    preco NUMERIC(10, 2),
    quantidade INT
);

Dica

Se você está seguindo os exemplos, vai perceber que já criamos uma tabela "produtos" anteriormente, para seguir com o exemplo que tem finalidade fundamental a didática, você pode excluir a tabelas produtos utilizando o comando DROP TABLE:

DROP TABLE produtos;

• Inserção de dados fictícios na tabela produtos:

-- Inserção de dados fictícios na tabela produtos
INSERT INTO produtos (nome, preco, quantidade)
SELECT
    'Produto ' || generate_series,
    ROUND(RANDOM() * 100 * 100) / 100,
    ROUND(RANDOM() * 500)
FROM generate_series(1, 8000);

• Execute consultas com EXPLAIN ANALYZE que filtrem os registros de produtos pela expressão "(preco * quantidade) >= 200" e observe o desempenho:

-- Exemplo de consulta sem utilizar o índice de expressão
$ EXPLAIN ANALYZE SELECT * FROM produtos WHERE (preco * quantidade) >= 200;
                            QUERY PLAN                              
-----------------------------------------------------------------
Seq Scan on produtos  (cost=0.00..199.00 rows=2667 width=26) (actual time=0.020..6.733 rows=7810 loops=1)
   Filter: ((preco * (quantidade)::numeric) >= '200'::numeric)
   Rows Removed by Filter: 190
 Planning Time: 0.348 ms
 Execution Time: 7.397 ms
(5 rows)

Agora, vamos criar um índice de expressão que calcule o valor total de cada produto, multiplicando o preço pela quantidade.

• Criação do índice de expressão:

-- Criação do índice de expressão
CREATE INDEX idx_produtos_valor_total ON produtos ((preco * quantidade));

Neste exemplo, estamos criando um índice chamado "idx_produtos_valor_total" na tabela produtos, com base na expressão "(preco * quantidade)". O índice será criado com base no resultado dessa expressão, permitindo consultas eficientes que envolvam o valor total dos produtos.

Consulta utilizando o índice de expressão:

-- Exemplo de consulta utilizando o índice de expressão
$ EXPLAIN ANALYZE SELECT * FROM produtos WHERE (preco * quantidade) >= 200;
                            QUERY PLAN                              
-----------------------------------------------------------------
Bitmap Heap Scan on produtos  (cost=60.95..166.62 rows=2667 width=26) (actual time=1.678..3.827 rows=7810 loops=1)
   Recheck Cond: ((preco * (quantidade)::numeric) >= '200'::numeric)
   Heap Blocks: exact=59
   ->  Bitmap Index Scan on idx_produtos_valor_total  (cost=0.00..60.28 rows=2667 width=0) (actual time=1.643..1.644 rows=7810 loops=1)
         Index Cond: ((preco * (quantidade)::numeric) >= '200'::numeric)
 Planning Time: 0.451 ms
 Execution Time: 5.015 ms
(7 rows)

Neste exemplo, estamos executando uma consulta que seleciona os produtos cujo valor total é maior ou igual a 200. O PostgreSQL utilizará o índice de expressão "idx_produtos_valor_total" para acelerar essa consulta.

Os índices de expressão podem ser úteis em casos onde a expressão ou função aplicada às colunas é complexa e não pode ser otimizada por um índice simples. Eles permitem que você crie índices personalizados para atender a necessidades específicas de consulta, melhorando o desempenho em cenários onde o valor indexado é uma função ou expressão dos valores das colunas.

Nota

É importante lembrar que a escolha correta dos índices depende das consultas que você executa com mais frequência e da estrutura da sua tabela. É recomendado realizar testes e análises de desempenho para determinar quais índices são mais eficientes para o seu caso de uso específico.

Índices de texto completo

O PostgreSQL oferece suporte a índices de texto completo, que permitem realizar pesquisas em texto não estruturado. Esses índices são úteis quando você precisa pesquisar palavras-chave ou frases em um texto, como em um motor de busca. Eles fornecem recursos avançados, como busca por proximidade, busca por sinônimos e busca com suporte a idiomas diferentes.

Os Índices de Texto Completo são usados para pesquisar texto em colunas de texto no PostgreSQL. Eles são especialmente úteis quando você precisa realizar consultas que envolvem pesquisa de palavras-chave, busca por frases ou ordenação de resultados por relevância.

Exemplo de Índices de Texto Completo:

• Crie uma tabela chamada "noticias" com colunas como "id", "titulo" e "conteudo":

-- Criação da tabela noticias
CREATE TABLE noticias (
    id SERIAL PRIMARY KEY,
    titulo VARCHAR(100),
    conteudo TEXT
);

• Insira alguns dados fictícios na tabela "noticias":

-- Inserção de dados fictícios na tabela noticias
INSERT INTO noticias (titulo, conteudo)
VALUES
    ('Notícia 1', 'Este é o conteúdo da notícia 1.'),
    ('Notícia 2', 'Aqui está o conteúdo da notícia 2.'),
    ('Notícia 3', 'Conteúdo da notícia 3 sobre um determinado assunto.'),
    ('Notícia 4', 'Mais um exemplo de conteúdo para a notícia 4.'),
    ('Notícia 5', 'O conteúdo da notícia 5 é importante para os leitores.');

Neste exemplo, estamos criando um índice de texto completo chamado "idx_noticias_conteudo" na tabela "noticias" com base na coluna conteudo. Estamos usando a função to_tsvector para converter o conteúdo do texto em um vetor de busca.

Execute consultas que realizem pesquisas de texto completo, como busca por palavras-chave ou frases:

-- Exemplo de consulta de texto completo usando palavra-chave
SELECT * FROM noticias WHERE to_tsvector('portuguese', conteudo) @@ to_tsquery('portuguese', 'conteúdo');

Neste exemplo, estamos executando uma consulta que busca a palavra-chave "conteúdo" no conteúdo das notícias. A função "to_tsvector" é usada para converter o conteúdo do texto em um vetor de busca, enquanto a função "to_tsquery" é usada para converter a palavra-chave em uma expressão de consulta compatível com o índice de texto completo.

-- Exemplo de consulta de texto completo usando frase
SELECT * FROM noticias WHERE to_tsvector('portuguese', conteudo) @@ plainto_tsquery('portuguese', 'conteúdo');

Neste outro exemplo, estamos executando uma consulta que busca a frase "Conteúdo da notícia 3" no conteúdo das notícias. Com antes a função "to_tsvector" é usada para converter o conteúdo do texto em um vetor de busca, enquanto a função "plainto_tsquery" é usada para converter a frase em uma expressão de consulta compatível com o índice de texto completo.

Ao utilizar Índices de Texto Completo, você pode executar consultas de pesquisa de texto eficientes em colunas de texto, como títulos, conteúdos ou outros campos relevantes. Os Índices de Texto Completo ajudam a melhorar o desempenho e fornecem recursos avançados de pesquisa em seus dados textuais.

Dicas

A documentação oficial do PostgreSQL é uma excelente fonte para aprender mais sobre os recursos e opções de configuração dos índices de texto completo. Recomendo explorar a seção sobre "Full Text Search" na documentação, que fornece informações detalhadas sobre como usar e otimizar os índices de texto completo. Você encontrará explicações sobre as funções, operadores, configurações de dicionário e técnicas avançadas de pesquisa de texto completo. Além disso, a documentação inclui exemplos práticos e casos de uso para ajudá-lo a aplicar esses conceitos em seu próprio ambiente.

Você pode acessar a documentação oficial do PostgreSQL sobre "Full Text Search" clicando aqui.

Considerações finais

Essas são apenas algumas das técnicas avançadas relacionadas a índices no PostgreSQL. Cada técnica tem seus casos de uso específicos e benefícios. É importante entender como aplicar essas técnicas corretamente para otimizar o desempenho das consultas e melhorar a eficiência do banco de dados.