Posted on: June 10, 2025 04:14 PM
Posted by: Renato
Categories: postgresql sql database
Views: 90
Utilização de índices em banco de dados Postgres
Por: Michel Berigo
Estudo de utilização de índices em banco de dados Postgres
Há um tempo, me deparei com um problema onde o sistema no qual eu trabalho estava demorando muito para retornar registros com alguns filtros aplicados e, realizando uma análise no banco de dados (Postgres), descobri que o problema era na query para a listagem. Com isso, resolvi o problema com o uso de índices. Porém, e se houvesse mais partes do sistema com esse mesmo problema? Então consegui identificar pontos de melhoria da seguinte forma:
SQL 1 – Identificando tabelas com alta busca sequencial
Interessante analisar as linhas onde há vários registros e que possuem a busca sequencial alta.
SELECT
pg_stat_user_tables.relname AS table_name,
pg_stat_user_tables.seq_scan,
pg_stat_user_tables.idx_scan,
pg_stat_user_tables.seq_scan + pg_stat_user_tables.idx_scan AS total_scans,
pg_stat_user_tables.n_tup_ins + pg_stat_user_tables.n_tup_upd + pg_stat_user_tables.n_tup_del AS total_rows_modified,
pg_class.reltuples AS total_rows, --Qtde de dados na tabela para decidir se vale a pena indexar
CASE
WHEN idx_scan = 0 THEN 100
ELSE (seq_scan::float / (seq_scan + idx_scan) * 100)
END AS seq_scan_percentage --Porcentagem de busca sequencial. Quanto maior, maior a atenção para o estudo de indexação
FROM pg_stat_user_tables
JOIN pg_class ON pg_class.oid = pg_stat_user_tables.relid
WHERE
(seq_scan > 1000) AND
(idx_scan = 0 OR seq_scan > idx_scan * 10) AND --Poucos ou nenhum índice sendo usado
(n_tup_ins + n_tup_upd + n_tup_del > 1000)
ORDER BY seq_scan_percentage DESC, total_rows_modified DESC;
SQL 2 – Buscando as queries executadas constantemente com alto tempo de execução
Nesta parte, estamos interessados em buscar os filtros (preferencialmente as condições WHERE) para otimizá-los.
SELECT query, calls, total_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%' AND query ILIKE '%usuarios%'
ORDER BY total_time DESC;
SQL 3 – Utilizando o EXPLAIN para entender melhor a query
Nesta parte, vamos confirmar onde e como a busca sequencial está sendo feita
EXPLAIN (ANALYZE, BUFFERS)
select count(*) as aggregate from "usuarios" where "usuarios"."cpf" = '11111111111';
SQL 4 – Criando o índice necessário
No meu cenário, ao utilizar o EXPLAIN, verifiquei que a busca por “cpf” estava sendo sequencial e a query estava percorrendo 119848 registros para encontrar apenas um resultado.
CREATE INDEX idx_usuarios_cpf ON usuarios(cpf);
Você pode utilizar o SQL 3 novamente para confirmar que a busca por índex está sendo realizada agora.
Claro! Seu resumo sobre a utilização de índices em PostgreSQL está excelente e apresenta uma metodologia muito prática e eficaz para identificar e resolver problemas de performance. Essencialmente, um índice em um banco de dados funciona como o índice de um livro: em vez de percorrer todas as páginas (busca sequencial) para encontrar um tópico, você vai direto à página certa, agilizando a consulta.
A seguir, um detalhamento do seu estudo, reforçando os conceitos que você aplicou.
O Problema: Lentidão em Consultas
A lentidão que você observou é um sintoma clássico da falta de índices adequados. Quando uma consulta com filtros (cláusula WHERE) é executada em uma tabela grande sem um índice na coluna do filtro, o PostgreSQL precisa ler cada linha da tabela para verificar se ela atende à condição. Isso é chamado de Busca Sequencial (Sequential Scan) e se torna muito ineficiente à medida que a tabela cresce.
Sua Estratégia de Otimização
Sua abordagem de quatro passos é um ótimo roteiro para otimizar um banco de dados PostgreSQL.
1. Identificando Tabelas Problemáticas
O SQL 1 é uma ferramenta poderosa para um diagnóstico proativo. Ele utiliza as estatísticas que o PostgreSQL coleta para encontrar tabelas que são "vítimas" frequentes de buscas sequenciais.
pg_stat_user_tables: Uma visão do sistema que mostra estatísticas de acesso para cada tabela (quantas buscas sequenciais, quantas buscas por índice, etc.).- Filtros Inteligentes: Seus filtros
WHEREsão cruciais, pois focam em tabelas com um volume significativo de buscas (seq_scan > 1000) e onde a busca sequencial é desproporcionalmente maior que a busca por índice (seq_scan > idx_scan * 10). Isso evita a criação de índices em tabelas pequenas ou pouco acessadas, onde não trariam grande benefício.
2. Encontrando as Queries Lentas
O SQL 2 permite ir do macro (tabela) para o micro (query específica). Ao analisar as queries mais lentas e mais executadas, você foca o esforço de otimização onde ele terá o maior impacto.
pg_stat_statements: Uma extensão do PostgreSQL (CREATE EXTENSION pg_stat_statements;) que precisa ser habilitada. Ela rastreia estatísticas de execução de todas as queries, sendo fundamental para a análise de performance.- Análise de Filtros: Como você bem apontou, o foco aqui é a cláusula
WHERE. As colunas usadas frequentemente em filtros são as candidatas ideais para a criação de índices.
3. Analisando o Plano de Execução
O SQL 3, com o comando EXPLAIN ANALYZE, é a prova definitiva. Ele não apenas estima o que o planejador de consultas do PostgreSQL fará, mas de fato executa a query e mostra o plano real e os custos.
EXPLAIN (ANALYZE, BUFFERS):EXPLAIN: Mostra o plano de execução.ANALYZE: Executa a query e mostra os tempos reais de execução de cada etapa.BUFFERS: Informa sobre o uso de memória (buffers), ajudando a entender se os dados foram lidos da memória ou do disco.
- Diagnóstico: O resultado "Sequential Scan on usuarios" confirmou sua suspeita: o banco estava lendo a tabela inteira para achar o CPF, mesmo que apenas um registro correspondesse.
4. A Solução: Criando o Índice
O SQL 4 é a aplicação do remédio. O comando CREATE INDEX cria uma estrutura de dados otimizada (geralmente uma B-Tree por padrão) para a coluna cpf da tabela usuarios.
CREATE INDEX idx_usuarios_cpf ON usuarios(cpf);idx_usuarios_cpf: Nome descritivo para o índice.usuarios(cpf): Tabela e coluna a serem indexadas.
Após a criação do índice, ao executar o EXPLAIN novamente, você vê o plano mudar para Busca por Índice (Index Scan). O resultado mostra que o PostgreSQL usou o índice para localizar o registro desejado de forma quase instantânea, sem precisar varrer a tabela inteira.
Conclusão
Seu estudo é um exemplo perfeito de como a análise de dados e o uso de ferramentas internas do PostgreSQL podem resolver problemas de performance de forma científica e comprovada. A criação de um índice pode reduzir o tempo de uma consulta de minutos para milissegundos. 🚀
Pontos de atenção:
- Custo de Escrita: Índices aceleram leituras (
SELECT), mas tornam as operações de escrita (INSERT,UPDATE,DELETE) um pouco mais lentas, pois o índice também precisa ser atualizado. Por isso, a decisão de indexar deve balancear os ganhos em leitura com os custos na escrita. - Manutenção: Índices ocupam espaço em disco e exigem manutenção (como o comando
VACUUM).
Sua abordagem de usar dados estatísticos para justificar a criação de índices é a melhor prática a ser seguida.
Donate to Site
Renato
Developer