como usar a extensão index_adviser no RDS PostgreSQL - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Pré-requisitos

Antes de começar, verifique se:

A instância RDS executa a versão secundária do mecanismo 20230830 ou posterior

Importante

Se sua instância executar uma versão secundária do mecanismo anterior a 20230830 e você já tiver usado esta extensão, poderá continuar a utilizá-la. Para criar ou recriar a extensão, primeiro atualize a versão secundária do mecanismo para a versão mais recente. Para obter informações sobre os requisitos de segurança de plugins, consulte Restrições na criação de plugins.

Componentes

A criação da extensão gera automaticamente três componentes:

Componente	Tipo	Descrição
`index_advisory`	Tabela	Registra sugestões de indexação geradas durante a análise de consultas
`show_index_advisory()`	Função PL/pgSQL	Retorna instruções `CREATE INDEX` formatadas para uma sessão específica, identificada pelo ID do processo de backend
`select_index_advisory`	View	Exibe sugestões de indexação de todas as sessões; mesmo formato da saída de `show_index_advisory()`

Configurar a extensão

Crie a extensão.

postgres=# create extension index_adviser;
CREATE EXTENSION

Carregue a extensão na sessão atual.
```
postgres=# LOAD 'index_adviser';
LOAD
```
Nota
A instrução LOAD aplica-se apenas à sessão atual. Para carregar a extensão automaticamente em todas as sessões, adicione-a ao parâmetro shared_preload_libraries e reinicie a instância. Essa alteração pode afetar o desempenho da instância.
```
shared_preload_libraries='index_adviser'
```

Obter recomendações de índices

Analisar uma consulta única sem executá-la

Adicione o prefixo EXPLAIN a qualquer consulta para obter recomendações de índices sem executá-la. A extensão insere as recomendações diretamente na saída do plano de consulta.

O exemplo abaixo cria uma tabela de teste e executa três consultas com EXPLAIN:

CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;

postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
   Filter: (a < 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
         Index Cond: (a < 10000)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (a = 100)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (a = 100)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (b = 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (b = 10000)
(6 rows)

A linha One-Time Filter: ' plan (using Index Adviser) ' indica que a extensão registrou recomendações. Consulte a tabela index_advisory para visualizá-las.

Analisar consultas durante a execução

Sem o prefixo EXPLAIN, a extensão analisa as consultas conforme são executadas e registra as recomendações automaticamente.

Nota

Não utilize index_adviser em transações somente leitura.

Consultar a tabela index_advisory

Após executar consultas (com ou sem EXPLAIN), consulte a tabela index_advisory para visualizar as recomendações brutas:

postgres=# SELECT * FROM index_advisory;
 reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
--------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
  16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
  16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00
  16438 | t       | {2}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:07.814453 +00:00
(3 rows)

Descrição dos campos:

Campo	Tipo	Descrição
`reloid`	oid	Identificador de objeto (OID) da tabela
`relname`	name	Nome da tabela
`attrs`	integer[]	Número da coluna para o índice recomendado
`benefit`	real	Redução estimada no custo da consulta proporcionada pelo índice
`original_cost`	real	Tempo médio necessário para executar a instrução SQL antes de usar o índice para acelerar a consulta
`new_cost`	real	Tempo médio necessário para executar a instrução SQL após usar o índice para acelerar a consulta
`index_size`	integer	Tamanho estimado do índice em páginas de disco
`backend_pid`	integer	ID do processo de backend da sessão que gerou esta recomendação
`timestamp`	timestamp	Momento em que a recomendação foi gerada

Obter recomendações no nível da sessão

A função show_index_advisory() e a view select_index_advisory agregam recomendações por consulta em resumos no nível da sessão e produzem instruções CREATE INDEX prontas para execução.

Usar show_index_advisory()

Chame a função com um ID de processo de backend para obter recomendações dessa sessão. Passe null para obter recomendações da sessão atual:

postgres=# SELECT show_index_advisory(null);
                                                             show_index_advisory
----------------------------------------------------------------------------------------------------------------------------------------------------
 create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
 create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

Cada linha contém uma instrução CREATE INDEX seguida de um comentário com estas métricas:

Métrica	Descrição
`size`	Tamanho estimado do índice em KB — `MAX(index_size)` entre todas as consultas que se beneficiam deste índice
`benefit`	Redução total de custo entre todas as consultas que se beneficiam deste índice — `SUM(benefit)` por consulta
`gain`	Razão benefício/tamanho: `SUM(benefit) / MAX(index_size)`. Um valor maior indica que o índice oferece mais benefício em relação ao espaço em disco consumido
`original_cost`	Tempo médio necessário para executar a instrução SQL antes de usar o índice para acelerar a consulta
`new_cost`	Tempo médio necessário para executar a instrução SQL após usar o índice para acelerar a consulta. Se vários índices forem recomendados para uma única consulta, este valor reflete o custo após a aplicação de todos os índices recomendados

Para obter recomendações de uma sessão específica, passe o ID do processo de backend correspondente, encontrado na coluna backend_pid da tabela index_advisory:

SELECT show_index_advisory(79370);

Usar select_index_advisory

A view select_index_advisory exibe recomendações de todas as sessões presentes na tabela index_advisory, agrupadas por ID de processo de backend:

postgres=# SELECT * FROM select_index_advisory;
 backend_pid |                                                             show_index_advisory
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------
       79370 | create index t_a_idx on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
       79370 | create index t_b_idx on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

Por exemplo, quando backend_pid = 79370, o resultado da consulta para a recomendação de indexação da coluna a na tabela t é o seguinte:

 reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
--------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
  16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
  16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00

Utilize o valor de gain para priorizar quais índices criar primeiro. Um gain mais alto significa que o índice proporciona maior redução no custo da consulta em relação ao espaço em disco que consome.