prerequisites and usage of the hypopg extension - PolarDB: Central de Documentação do Alibaba Cloud

Crie um índice em uma tabela grande pode levar minutos ou horas, além de consumir recursos significativos de disco, CPU e locks. A extensão hypopg é uma solução open source que permite testar se um índice proposto melhoraria uma consulta específica antes da criação do índice real. Os índices hipotéticos não têm custo: existem apenas na memória da sessão, não ocupam espaço em disco e não afetam a execução das consultas.

Como funciona

Os índices hipotéticos residem na memória privada da conexão, e não em tabelas do sistema. Como não possuem presença física no disco, são visíveis apenas para instruções EXPLAIN simples, e não para EXPLAIN ANALYZE, que executa a consulta com dados reais.

Esse mecanismo fornece uma resposta imediata à pergunta "o planner usaria este índice?" sem o custo de criá-lo.

Tipos de índice compatíveis:

Tipo de índice	Descrição
`btree`	Índices B-tree (padrão)
`brin`	Block Range Indexes (BRIN)
`hash`	Índices hash
`bloom`	Índices bloom (requer a extensão bloom)

Pré-requisitos

Antes de começar, verifique se você tem:

Um cluster PolarDB for PostgreSQL executando uma das seguintes versões secundárias do mecanismo:
- PostgreSQL 16: 2.0.16.9.8.0 ou posterior
- PostgreSQL 14: 2.0.14.5.1.0 ou posterior
- PostgreSQL 11: 2.0.11.9.28.0 ou posterior
Consultas lentas identificadas para otimização e tipos de índice definidos para teste

Para verificar sua versão secundária do mecanismo, visualize-a no console ou execute SHOW polardb_version; . Para atualizar, consulte Upgrade the minor engine version .

Instale a extensão

CREATE EXTENSION hypopg;

Para verificar a instalação:

\dx hypopg

Saída esperada:

List of installed extensions
  Name  | Version | Schema |             Description
--------+---------+--------+-------------------------------------
 hypopg | 1.3.1   | public | Hypothetical indexes for PostgreSQL
(1 row)

Alternativamente, consulte o catálogo pg_extension:

SELECT * FROM pg_extension WHERE extname = 'hypopg';

Saída esperada:

extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+----------+--------------+----------------+------------+-----------+--------------
 hypopg |       10 |         2200 | t              | 1.3.1      |           |
(1 row)

Teste um índice sem criá-lo

Este exemplo demonstra o fluxo de trabalho completo: identificar uma consulta lenta, crie um índice hipotético, confirme se o planner o utilizaria e decidir sobre a criação do índice real.

1. Defina uma tabela de teste.

CREATE TABLE hypo (id integer, val text);
INSERT INTO hypo SELECT i, 'line ' || i FROM generate_series(1, 100000) i;
VACUUM ANALYZE hypo;

2. Verifique o plano de consulta atual.

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Sem um índice, o planner recorre a uma varredura sequencial:

QUERY PLAN
--------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10)
   Filter: (id = 1)
(2 rows)

3. Crie um índice hipotético.

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');

Saída:

indexrelid |      indexname
------------+----------------------
      13925 | <13925>btree_hypo_id
(1 row)

O indexrelid (neste caso, 13925) é atribuído dinamicamente. O indexname reflete o tipo de índice e as colunas.

A função hypopg_create_index() aceita qualquer instrução CREATE INDEX padrão. Outras instruções passadas a ela são ignoradas.

4. Verifique se o planner usaria o índice.

EXPLAIN SELECT val FROM hypo WHERE id = 1;

O plano de consulta agora mostra uma varredura de índice:

QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using "<13925>btree_hypo_id" on hypo  (cost=0.04..8.06 rows=1 width=10)
   Index Cond: (id = 1)
(2 rows)

O custo cai de 1791.00 para 8.06. O planner usaria este índice.

5. Confirme que o índice não é usado durante a execução real.

EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10) (actual time=0.030..15.439 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning Time: 0.066 ms
 Execution Time: 15.492 ms
(5 rows)

O comando EXPLAIN ANALYZE executa a consulta real e ignora índices hipotéticos. Esse comportamento é esperado.

Gerencie índices hipotéticos

Use as funções e visualizações a seguir para gerenciar índices hipotéticos.

Nome	Tipo	Descrição
`hypopg_list_indexes`	View	Lista todos os índices hipotéticos
`hypopg()`	Função	Lista índices hipotéticos no formato `pg_index`
`hypopg_get_indexdef(oid)`	Função	Retorna a instrução `CREATE INDEX` de um índice hipotético
`hypopg_relation_size(oid)`	Função	Estima o tamanho de um índice hipotético
`hypopg_drop_index(oid)`	Função	Exclui um índice hipotético pelo identificador de objeto (OID)
`hypopg_reset()`	Função	Exclui todos os índices hipotéticos

hypopg_list_indexes

Lista todos os índices hipotéticos na sessão atual:

SELECT * FROM hypopg_list_indexes;

indexrelid |      index_name      | schema_name | table_name | am_name
------------+----------------------+-------------+------------+---------
      13925 | <13925>btree_hypo_id | public      | hypo       | btree
(1 row)

hypopg()

Lista índices hipotéticos no mesmo formato do catálogo de sistema pg_index:

SELECT * FROM hypopg();

      indexname       | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <13925>btree_hypo_id |      13925 |    16450 |       1 | f           | 1      | 0            | 1978     |           |          |         |  403
(1 row)

hypopg_get_indexdef(oid)

Retorna a instrução CREATE INDEX que criaria o índice hipotético especificado:

SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes;

      index_name      |             hypopg_get_indexdef
----------------------+----------------------------------------------
 <13925>btree_hypo_id | CREATE INDEX ON public.hypo USING btree (id)
(1 row)

hypopg_relation_size(oid)

Estima o tamanho de um índice hipotético:

SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
FROM hypopg_list_indexes;

      index_name      | pg_size_pretty
----------------------+----------------
 <13925>btree_hypo_id | 2544 kB
(1 row)

hypopg_drop_index(oid)

Exclui um único índice hipotético por OID:

SELECT hypopg_drop_index(13925);

hypopg_drop_index
-------------------
 t
(1 row)

hypopg_reset()

Exclui todos os índices hipotéticos na sessão atual:

SELECT hypopg_reset();

hypopg_reset
--------------

(1 row)

Configure a extensão

Parâmetro	Padrão	Descrição
`hypopg.enabled`	`on`	Controla se o planner usa índices hipotéticos. Defina como `off` para desativar sem excluir os índices hipotéticos existentes.
`hypopg.use_real_oids`	`off`	Controla como os identificadores de objeto (OIDs) são atribuídos aos índices hipotéticos. Visualize os detalhes abaixo.

hypopg.use_real_oids

**off (padrão):** o hypopg selecione OIDs de um intervalo livre reservado, calculado dinamicamente no primeiro uso da extensão. Este modo funciona em servidores standby. A contrapartida é um limite de aproximadamente 2.500 índices hipotéticos simultâneos. Ao ultrapassar esse limite, a criação de novos índices hipotéticos torna-se lenta. Execute hypopg_reset() para limpar todos os índices hipotéticos existentes e restaurar o desempenho normal.

**on:** o hypopg solicita OIDs reais ao banco de dados. Isso remove o limite de 2.500 índices, mas exige mais recursos de lock e não pode ser usado em servidores standby.

Alterar este parâmetro não exige redefinir os índices hipotéticos existentes. OIDs reais e não reais podem coexistir.

Desinstale a extensão

DROP EXTENSION hypopg;