No mundo do Postgres, os índices são cruciais para navegar com eficiência em um repositório de banco de dados (chamado heap, heap). O Postgres não suporta clustering e a arquitetura MVCC faz com que você acumule muitas versões da mesma tupla. Portanto, é muito importante poder criar e manter índices eficazes para dar suporte a aplicativos.
Aqui estão algumas dicas para otimizar e melhorar o uso de índices.
Nota: as consultas mostradas abaixo funcionam em um banco de dados de amostra de pagila não modificado.Usando índices de cobertura
Vamos revisar a solicitação para recuperar endereços de email para usuários inativos. A tabela do
customer possui uma coluna
active e a solicitação é simples:
pagila=
A consulta chama a sequência completa da verificação da tabela do
customer . Vamos criar um índice para a coluna
active :
pagila=
Ajudou, a verificação subsequente se transformou em uma "
index scan ". Isso significa que o Postgres varrerá o índice
idx_cust1 e continuará pesquisando a pilha da tabela para ler os valores das outras colunas (neste caso, a coluna de
email ) necessárias à consulta.
O PostgreSQL 11 introduziu índices de cobertura. Eles permitem incluir uma ou mais colunas adicionais no próprio índice - seus valores são armazenados no armazenamento de dados do índice.
Se usássemos esse recurso e adicionássemos um valor de email ao índice, o Postgres não precisaria procurar o valor do
email no heap da tabela. Vamos ver se isso funciona:
pagila=
A "
Index Only Scan " nos diz que a consulta agora precisa de apenas um índice, o que ajuda a evitar todas as E / S de disco para ler a pilha da tabela.
Hoje, os índices de cobertura estão disponíveis apenas para árvores B. No entanto, nesse caso, os esforços de escolta serão maiores.
Usando índices parciais
Os índices parciais indexam apenas um subconjunto das linhas em uma tabela. Isso economiza o tamanho dos índices e verificações mais rápidas.
Suponha que precisamos obter uma lista de endereços de e-mail de nossos clientes da Califórnia. A solicitação será 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=
Quais índices comuns nos fornecerão:
pagila=
A verificação de
address foi substituída pela
idx_address1 índice
idx_address1 e, em seguida, o heap de
address foi verificado.
Como essa é uma consulta frequente e precisa ser otimizada, podemos usar um índice parcial que indexa apenas as linhas com endereços nos quais a
'California' :
pagila=
Agora, a solicitação lê apenas
idx_address2 e não toca na tabela de
address .
Usando índices com vários valores
Algumas colunas que precisam ser indexadas podem não conter um tipo de dados escalar. Tipos de
jsonb como
jsonb ,
arrays e
tsvector contêm valores múltiplos ou múltiplos. Se você precisar indexar essas colunas, geralmente precisará procurar todos os valores individuais nessas colunas.
Vamos tentar encontrar os nomes de todos os filmes que contêm cortes de tomadas sem êxito. A tabela de
film possui uma coluna de texto chamada
special_features . Se o filme tiver essa "propriedade especial", a coluna conterá um elemento na forma de uma matriz de texto nos
Behind The Scenes . Para procurar por todos esses filmes, precisamos selecionar todas as linhas com "Bastidores" para
quaisquer valores da matriz
special_features :
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
O operador de contenção
@> verifica se o lado direito é um subconjunto do lado esquerdo.
Plano de Solicitação:
pagila=
O que solicita uma verificação completa da pilha com um custo de 67.
Vamos ver se o índice B-tree comum nos ajuda a:
pagila=
O índice nem sequer 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=
O índice GIN suporta a comparação de valores individuais com valores compostos indexados, como resultado, o custo do plano de consulta é reduzido em mais da metade.
Livre-se de índices duplicados
Os índices se acumulam com o tempo e, às vezes, um novo índice pode conter a mesma definição que uma das anteriores. Para obter definições SQL legíveis humanas de índices, você pode usar a exibição de catálogo
pg_indexes . Você também pode encontrar facilmente as mesmas definições:
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=
Índices de superconjunto
Pode acontecer que você acumule muitos índices, um dos quais indexa um subconjunto das colunas que indexam outros índices. Isso pode ser desejável ou não - um superconjunto só pode varrer por índice, o que é bom, mas pode ocupar muito espaço ou a consulta para a qual esse superconjunto deveria ser otimizado não é mais usada.
Se você precisar automatizar a definição desses índices, poderá começar com
pg_index na tabela
pg_catalog .
Índices não utilizados
À medida que os aplicativos que usam bancos de dados se desenvolvem, o mesmo ocorre com as consultas que eles usam. Os índices adicionados anteriormente não podem mais ser usados por nenhuma consulta. Cada vez que o índice é varrido, é marcado pelo gerenciador de estatísticas e, na
pg_stat_user_indexes catálogo do sistema
pg_stat_user_indexes é possível ver o valor
idx_scan , que é um contador cumulativo. O rastreamento desse valor por um período de tempo (digamos, um mês) dará uma boa idéia de quais índices não são usados e podem ser excluídos.
Aqui está uma solicitação para obter as contagens atuais de varredura 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=
Recrie índices com menos bloqueios
Muitas vezes, os índices precisam ser recriados, por exemplo, quando são inflados em tamanho, e a recriação pode acelerar a verificação. Além disso, os índices podem estar corrompidos. Alterar os parâmetros do índice também pode exigir a sua recriação.
Ativar criação de índice paralelo
No PostgreSQL 11, a criação de um índice B-Tree é competitiva. Para acelerar o processo de criação, vários trabalhadores paralelos podem ser usados. 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 pequenos. Idealmente, esses números devem ser aumentados junto com o número de núcleos do processador. Leia a
documentação para mais detalhes.
Criação de índice em segundo plano
Você pode criar um índice em segundo plano usando o parâmetro
CONCURRENTLY do comando
CREATE INDEX :
pagila=
Esse procedimento de criação de índice difere do usual, pois não requer bloqueio de tabela e, portanto, não bloqueia operações de gravação. Por outro lado, leva mais tempo e consome mais recursos.
O Postgres oferece muitas opções flexíveis para a criação de índices e maneiras de resolver casos específicos, além de maneiras de gerenciar o banco de dados em caso de crescimento explosivo do seu aplicativo. Esperamos que essas dicas o ajudem a tornar suas consultas rápidas e o banco de dados pronto para escalar.