Nesta seção, vamos trabalhar com o banco de dados sfbikeshare
para criar índices e analisar seu impacto na performance.
Baseline
Vamos estabelecer uma baseline de performance para nossas consultas. Isso nos permitirá quantificar exatamente o impacto que os índices terão na performance.
Exercício
Exercise
Answer
O plano de execução provavelmente mostra um Sequential Scan, indicando que o banco está lendo todas as mais de 600.000 linhas da tabela trip
para encontrar aquelas onde start_station_id = 50
.
Isso é ineficiente, especialmente considerando que provavelmente apenas algumas milhares de linhas satisfazem essa condição.
A criação de um índice na coluna start_station_id
permitiria ao PostgreSQL localizar diretamente as linhas relevantes sem precisar varrer toda a tabela.
Vamos também examinar esta query:
Exercício
Criando o Primeiro Índice
Vamos começar criando um índice B-Tree na coluna start_station_id
da tabela trip
, que usamos na primeira consulta.
Exercício
Info
Durante a criação do índice, o PostgreSQL mantém a tabela disponível para leituras, mas bloqueia escritas.
Em ambientes de produção com tabelas muito grandes, você pode usar CREATE INDEX CONCURRENTLY
para evitar bloquear escritas, embora isso torne a criação do índice mais lenta.
Agora vamos verificar o impacto do índice:
Exercício
Exercício
Answer
Dica: Crie uma query e analise a distribuição dos valores na coluna subscription_type
versus start_station_id
.
Índices para Análises Temporais
A tabela status
contém timestamps na coluna time
.
Análises temporais são extremamente comuns em engenharia de dados, então vamos explorar como otimizá-las.
Exercício
Exercício
Exercise
Answer
Quando uma consulta precisa processar uma porcentagem significativa da tabela (mais de 5-10%), o PostgreSQL muitas vezes decide que um Sequential Scan é mais eficiente que usar o índice.
Isso porque usar um índice requer duas operações de I/O: ler o índice e depois ler os blocos da tabela, que podem estar espalhados fisicamente no disco.
Para grandes porções de dados, ler a tabela sequencialmente é mais eficiente que pular entre diferentes blocos.
Índices são mais efetivos para consultas seletivas que retornam uma pequena fração dos dados.
Exercício
Exercício
Exercício
Exercício
Exercício
Exercício
Answer
A primeira query filtra pela coluna category1
, enquanto a segunda filtra pela coluna category2
. Ambas as colunas são do tipo inteiro, da mesma tabela.
Exercício
Answer
A diferença na performance das duas queries após a criação dos índices ocorre devido à distribuição dos valores nas colunas category1
e category2
.
O índice será mais eficaz se uma coluna tem uma distribuição mais uniforme dos valores, permitindo que o PostgreSQL localize rapidamente as linhas relevantes.
Por outro lado, se uma coluna tem muitos valores repetidos (baixa cardinalidade), o índice pode não ser tão útil, pois muitas linhas podem corresponder ao mesmo valor, levando a um maior número de leituras de blocos.
Distribuição category1
:
SELECT s.category1,
COUNT(*) AS qtde_cat1
FROM public.status s
GROUP BY s.category1
ORDER BY s.category1 ASC;
Distribuição category2
:
SELECT s.category2,
COUNT(*) AS qtde_cat2
FROM public.status s
GROUP BY s.category2
ORDER BY s.category2 ASC;
Por hoje é só! Bons estudos!