No mundo do Postgres, os índices são extremamente importantes para a navegação eficiente no armazenamento do banco de dados (chamado de 'heap'). O Postgres não suporta a clusterização para isso, e a arquitetura MVCC faz com que você acumule muitas versões da mesma tupla. Portanto, é muito importante saber como criar e manter índices eficazes para suportar aplicações.
Apresento a vocês algumas dicas para otimização e melhoria do uso dos índices.
Observação: as consultas mostradas abaixo funcionam em um .
Uso de Índices Cobertores (Covering Indexes)
Vamos considerar uma consulta para extrair endereços de e-mail de usuários inativos. Na tabela customer há uma coluna ativo, e a consulta fica simples:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLANO DA CONSULTA
-----------------------------------------------------------
Seq Scan on customer (custo=0.00..16.49 linhas=15 largura=32)
Filtro: (active = 0)
(2 linhas) Na consulta, é invocada a varredura sequencial completa da tabela customer. Vamos criar um índice para a coluna ativo:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLANO DA CONSULTA
-----------------------------------------------------------------------------
Scan de Índice usando idx_cust1 na tabela customer (custo=0.28..12.29 linhas=15 largura=32)
Condição do Índice: (active = 0)
(2 linhas) Ajudou, a varredura subsequente se transformou em 'index scan' Isso significa que o Postgres irá escanear o índice 'idx_cust1' e então continuará a busca no heap da tabela para ler os valores de outras colunas (neste caso, a coluna email), que são necessários na consulta.
No PostgreSQL 11, os índices cobertores foram introduzidos. Eles permitem incluir uma ou mais colunas adicionais no próprio índice — seus valores são armazenados no armazenamento de dados do índice.
Se tivéssemos usado esse recurso e adicionado o valor do e-mail dentro do índice, então o Postgres não precisaria procurar no heap da tabela o valor email. Vamos ver se isso funcionará:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLANO DA CONSULTA
----------------------------------------------------------------------------------
Scan de Índice Apenas usando idx_cust2 na tabela customer (custo=0.28..12.29 linhas=15 largura=32)
Condição do Índice: (active = 0)
(2 linhas) «Scan de Índice Apenas' nos diz que agora a consulta precisa apenas de um único índice, o que ajuda a evitar todas as operações de entrada/saída do disco para ler o heap da tabela.
Atualmente, os índices cobertos estão disponíveis apenas para árvores B. No entanto, nesse caso, os esforços de manutenção serão maiores.
Uso de índices parciais
Os índices parciais indexam apenas um subconjunto das linhas da tabela. Isso economiza espaço dos índices e permite uma varredura mais rápida.
Suponha que precisamos obter uma lista de endereços de e-mail de nossos clientes na Califórnia. A consulta será a seguinte:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
que possui um plano de consulta que envolve a varredura das duas tabelas que estão unidas:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)O que os índices regulares nos oferecem:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows) Varredura address foi substituída por uma varredura de índice idx_address1, e então uma pilha foi escaneada address.
Como esta é uma consulta frequente e precisa ser otimizada, podemos usar um índice parcial, que indexa apenas aquelas linhas de endereços cujo distrito ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan usando idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows) Agora a consulta lê apenas idx_address2 e não acessa a tabela address.
Uso de índices multivalorados (Multi-Value Indexes)
Algumas colunas que precisam ser indexadas podem não conter tipos de dados escalares. Tipos de colunas como jsonb, arrays e tsvector podem conter valores compostos ou múltiplos. Se você precisar indexar essas colunas, geralmente é necessário pesquisar todos os valores individuais nessas colunas.
Vamos tentar encontrar os títulos de todos os filmes que contêm cortes de erros de gravação. Na tabela film existe uma coluna de texto chamada special_features. Se o filme tiver esse "recurso especial", a coluna contém um elemento em forma de array de texto Behind The Scenes. Para encontrar todos esses filmes, precisamos selecionar todas as linhas com "Behind The Scenes" em qualquer valor do array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; O operador de contenção @> verifica se a parte direita é um subconjunto da parte esquerda.
Plano de consulta:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Que solicita uma varredura completa da pilha com um custo de 67.
Vamos ver se um índice B-tree comum nos ajuda:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)O índice nem foi considerado. O índice B-tree não tem conhecimento da existência de elementos individuais nos valores indexados.
Precisamos de um índice GIN.
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)O índice GIN suporta a correspondência de valores individuais com valores compostos indexados, resultando em uma diminuição de mais de duas vezes no custo do plano de consulta.
Eliminando a duplicação de índices
Os índices se acumulam ao longo do tempo, e às vezes um novo índice pode conter a mesma definição que um dos anteriores. Para obter definições de índices em SQL que sejam legíveis para humanos, você pode usar a visão de catálogo. pg_indexes. Você também poderá encontrar facilmente definições idênticas:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
E aqui está o resultado quando executado no banco de dados padrão pagila:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Índices de superconjunto (Superset Indexes)
Pode acontecer que você acumule muitos índices, um dos quais indexa um subconjunto de colunas que já são indexadas por outros índices. Isso pode ser desejável ou não — um superconjunto pode levar à varredura apenas pelos índices, o que é bom, mas ao mesmo tempo pode ocupar muito espaço, ou a consulta para a qual esse superconjunto foi otimizado pode já não estar sendo usada.
Se você precisa automatizar a identificação desses índices, pode começar com da tabela pg_catalog.
Índices não utilizados
À medida que os aplicativos que usam bancos de dados evoluem, as consultas que eles utilizam também mudam. Índices adicionados anteriormente podem não estar mais sendo usados por nenhuma consulta. A cada varredura de índice, ele é marcado pelo gerenciador de estatísticas, e na visão do catálogo do sistema pg_stat_user_indexes pode-se verificar o valor idx_scan, que é um contador cumulativo. Monitorar este valor por um determinado período (digamos, um mês) fornecerá uma boa visão sobre quais índices não estão sendo utilizados e podem ser removidos.
Aqui está a consulta para obter os contadores de varredura atuais de todos os índices no esquema 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
com saída como esta:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 linhas)Recriação de índices com menos bloqueios
Frequentemente, é necessário recriar índices, por exemplo, quando eles se expandem em tamanho, e a recriação pode acelerar a varredura. Além disso, os índices podem ficar corrompidos. A alteração dos parâmetros do índice também pode exigir sua recriação.
Ativando a criação paralela de índices
No PostgreSQL 11, a criação de um índice B-Tree é concorrente. Para acelerar o processo de criação, pode-se usar vários trabalhadores executando em paralelo. No entanto, verifique se esses parâmetros de configuração estão definidos corretamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Os valores padrão são muito baixos. Idealmente, esses números devem ser aumentados junto com o número de núcleos de processador. Para mais detalhes, leia em .
Criação de índices em segundo plano
Você pode criar um índice em segundo plano usando o parâmetro CONCURRENTLY comandos CRIAR ÍNDICE:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEste procedimento de criação de índice difere do normal, pois não requer bloqueio da tabela, o que significa que não bloqueia operações de gravação. Por outro lado, leva mais tempo e consome mais recursos.
Postgres oferece diversas opções flexíveis para criar índices e caminhos para resolver quaisquer casos específicos, além de fornecer maneiras de gerenciar o banco de dados em caso de crescimento explosivo de seu aplicativo. Esperamos que essas dicas ajudem você a tornar suas consultas mais rápidas e seu banco de dados preparado para escalar.
Fonte: habr.com
