No mundo do Postgres, os índices são essenciais para uma navegação eficiente no armazenamento de um banco de dados (chamado de "heap"). Postgres não suporta clustering para ele, e a arquitetura MVCC faz com que você termine com muitas versões da mesma tupla. Portanto, é muito importante poder criar e manter índices eficientes para dar suporte a aplicativos.
Aqui estão algumas dicas para otimizar e melhorar o uso de índices.
Observação: as consultas mostradas abaixo funcionam em um .
Usando índices de cobertura
Vejamos uma solicitação para extrair endereços de e-mail de usuários inativos. Mesa customer há uma coluna active, e a consulta é simples:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
A consulta chama a sequência de varredura completa da tabela customer. Vamos criar um índice em uma coluna active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
Ajudou, a varredura subsequente se transformou em "index scan". Isso significa que o Postgres irá escanear o índice "idx_cust1", e então continue procurando no heap da tabela para ler os valores das outras colunas (neste caso, a coluna email) que a consulta precisa.
Os índices de cobertura são introduzidos no PostgreSQL 11. Eles permitem que você inclua uma ou mais colunas adicionais no próprio índice - seus valores são armazenados no armazenamento de dados do índice.
Se aproveitássemos esse recurso e adicionássemos o valor do email dentro do índice, o Postgres não precisaria procurar o valor no heap da tabela. email. Vamos ver se isso vai funcionar:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan' nos informa que a consulta agora precisa apenas de um índice, o que ajuda a evitar todas as E/S de disco para ler o heap da tabela.
Atualmente, os índices de cobertura estão disponíveis apenas para árvores B. Porém, neste caso, o esforço de manutenção será maior.
Usando índices parciais
Os índices parciais indexam apenas um subconjunto das linhas em uma tabela. Isso economiza o tamanho dos índices e torna as verificações mais rápidas.
Digamos que queremos obter uma lista dos endereços de e-mail de nossos clientes na Califórnia. A requisição ficará assim:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
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 comuns nos darão:
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)
Scan address foi substituído por varredura de índice idx_address1e, em seguida, digitalizou a pilha address.
Como essa é uma consulta frequente e precisa ser otimizada, podemos usar um índice parcial, que indexa apenas as linhas com endereços em que o 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 using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
Agora a consulta só lê idx_address2 e não toca na mesa address.
Usando índices de vários valores
Algumas colunas a serem indexadas podem não conter um tipo de dados escalar. Tipos de coluna como jsonb, arrays и tsvector conter valores compostos ou múltiplos. Se você precisar indexar essas colunas, geralmente precisará pesquisar todos os valores individuais nessas colunas.
Vamos tentar encontrar os títulos de todos os filmes que contêm cortes de tomadas malsucedidas. Mesa film há uma coluna de texto chamada special_features. Se o filme tiver essa "propriedade especial", a coluna conterá o elemento como uma matriz de texto Behind The Scenes. Para pesquisar todos esses filmes, precisamos selecionar todas as linhas com "Behind The Scenes" quando qualquer valores de matriz special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operador de aninhamento @> verifica se o lado direito é um subconjunto do lado esquerdo.
Solicitar plano:
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 de pilha completa com um custo de 67.
Vamos ver se um índice B-tree regular 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 da árvore B não está ciente 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 oferece suporte ao mapeamento de valores únicos em relação a valores compostos indexados, resultando em um custo de plano de consulta reduzido para mais da metade.
Livrar-se de índices duplicados
Os índices se acumulam com o tempo e, às vezes, um novo índice pode conter a mesma definição de um dos anteriores. Você pode usar a exibição de catálogo para obter definições de índices SQL legíveis por humanos. pg_indexes. Você também pode facilmente encontrar definições idênticas:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
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
Pode acontecer de você acabar com muitos índices, um dos quais indexa um superconjunto de colunas que indexam outros índices. Isso pode ou não ser desejável - o superconjunto pode resultar em varreduras somente de índice, o que é bom, mas pode ocupar muito espaço ou a consulta que o superconjunto deveria otimizar não é mais usada.
Se você precisa automatizar a definição de tais índices, pode começar com Da mesa pg_catalog.
índices não utilizados
À medida que os aplicativos que usam bancos de dados evoluem, também evoluem as consultas que eles usam. Os índices adicionados anteriormente não podem mais ser usados por nenhuma consulta. Cada vez que um índice é verificado, ele é marcado pelo gerenciador de estatísticas e na visualização do catálogo do sistema pg_stat_user_indexes você pode ver o valor idx_scan, que é um contador cumulativo. Rastrear esse valor por um período de tempo (digamos, um mês) dará uma boa ideia de quais índices não estão sendo usados e podem ser descartados.
Aqui está uma consulta para obter as contagens 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';
with output like this:
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 rows)Reconstruindo índices com menos bloqueios
Os índices geralmente precisam ser reconstruídos, por exemplo, quando ficam inchados, e a reconstrução pode acelerar a varredura. Além disso, os índices podem ser corrompidos. A alteração dos parâmetros do índice também pode exigir sua reconstrução.
Ativar criação de índice paralelo
No PostgreSQL 11, a criação de um índice B-Tree é simultânea. Para acelerar o processo de criação, vários trabalhadores paralelos podem ser usados. No entanto, verifique se essas opções de configuração estão definidas corretamente:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Os valores padrão são muito pequenos. Idealmente, esses números devem aumentar junto com o número de núcleos do processador. Leia mais em .
Criação de índice em segundo plano
Você pode criar um índice em segundo plano usando a opção CONCURRENTLY equipes CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEsse procedimento de criação de índice difere do usual porque não requer um bloqueio na tabela e, portanto, não bloqueia as operações de gravação. Por outro lado, leva mais tempo e consome mais recursos.
O Postgres fornece muita flexibilidade para criar índices e maneiras de resolver quaisquer casos especiais, bem como maneiras de gerenciar o banco de dados caso seu aplicativo cresça explosivamente. Esperamos que essas dicas ajudem você a obter suas consultas rapidamente e seu banco de dados pronto para escalar.
Fonte: habr.com