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

Escopo de aplicação

Antes de usar a extensão hypopg, é necessário saber:
- Quais consultas precisam de otimização.
- Quais tipos de índice testar.
Versões compatíveis do PolarDB for PostgreSQL:
- PostgreSQL 17 (versão secundária do mecanismo 2.0.17.10.7.0 ou posterior)
- PostgreSQL 16 (versão secundária do mecanismo 2.0.16.9.8.0 ou posterior)
- PostgreSQL 14 (versão secundária do mecanismo 2.0.14.5.1.0 ou posterior)
- PostgreSQL 11 (versão secundária do mecanismo 2.0.11.9.28.0 ou posterior)
Nota
É possível visualizar a versão secundária do mecanismo no console ou executando a instrução SHOW polardb_version;. Se a versão secundária do mecanismo não atender aos requisitos, atualize a versão secundária do mecanismo.
Versões compatíveis do :
- (versão secundária do mecanismo 2.0.14.5.1.0 ou posterior)
- (versão secundária do mecanismo 2.0.11.9.28.0 ou posterior)
Nota
É possível visualizar a versão secundária do mecanismo no console ou executando a instrução SHOW polardb_version;. Se a versão secundária do mecanismo não atender aos requisitos, atualize a versão secundária do mecanismo.

Introdução

A extensão hypopg é uma extensão de terceiros de código aberto compatível com o PolarDB for PostgreSQL e o . Os índices hipotéticos criados pelo hypopg não existem em tabelas do sistema; eles residem na memória privada da conexão. Como esses índices não ocupam arquivos físicos, o hypopg garante seu uso apenas em instruções EXPLAIN simples (sem a opção ANALYZE). Por não serem índices reais, os índices hipotéticos não consomem CPU, disco ou outros recursos.

Nota

A extensão hypopg oferece suporte aos seguintes tipos de índice:

btree: índices B-tree.
brin: índices de intervalo de blocos.
hash: índices hash.
bloom: índices bloom (requer instalação prévia da extensão bloom).

Uso

Instale a extensão.

Instale a extensão hypopg.
```
CREATE EXTENSION hypopg;
```

Verifique a instalação da extensã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)

Nota

A saída anterior indica que a versão 1.3.1 do hypopg está instalada.

Também é possível consultar a tabela pg_extension via SQL para confirmar a instalação do hypopg. Exemplo:

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)

Configure os parâmetros.

Parâmetro

Descrição

hypopg.enabled

Valor padrão: on. Valores válidos:

on: ativa a extensão hypopg.
off: desativa a extensão hypopg.
Nota
Quando a extensão hypopg está desativada, os índices hipotéticos não são utilizados, mas os existentes permanecem.

hypopg.use_real_oids

Valor padrão: off. Valores válidos:

off: o hypopg não usa identificadores de objeto reais (OIDs), selecionando-os de um intervalo livre reservado pelo banco de dados para versões futuras. O cálculo dinâmico desse intervalo ocorre no primeiro uso do hypopg e funciona em servidores standby, evitando problemas.
Nota
A desvantagem do valor padrão off é o limite de aproximadamente 2.500 índices hipotéticos simultâneos. Exceder esse máximo torna a criação de novos índices muito lenta. Nesse caso, chame a função hypopg_reset() para resolver o problema. Para obter mais informações, consulte Operações com índices hipotéticos.
on: permite que o hypopg use identificadores de objeto reais (OIDs). O parâmetro hypopg.use_real_oids evita a lentidão na criação de índices ao atingir o limite máximo. Embora solicite um identificador real — consumindo mais recursos de bloqueio e sendo incompatível com servidores standby —, essa opção permite utilizar todos os identificadores disponíveis. Para obter mais informações, consulte Operações com índices hipotéticos.
Nota
Alterar este parâmetro não exige redefinir os identificadores de índice hipotético. Identificadores reais e não reais podem coexistir.

Desinstale a extensão.
```
DROP EXTENSION hypopg;
```

Nota

Para obter mais informações sobre o uso, consulte Operações com índices hipotéticos.

Exemplo

Crie uma tabela e insira dados. A tabela não possui índices. Exemplo:

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

Verifique se um índice beneficiaria uma consulta simples. Exemplo:

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Saída esperada:

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

Nota

Como a tabela hypo não possui índices, a consulta realiza uma varredura sequencial.

Crie um índice hipotético. Exemplo:

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

Saída esperada:

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

Descrição dos parâmetros:

Parâmetro	Descrição
13925	Identificador do índice hipotético.
<13925>btree_hypo_id	Nome do índice hipotético gerado.

Nota

Um índice B-tree simples na coluna id beneficiaria esta consulta.
A função hypopg_create_index() aceita qualquer instrução CREATE INDEX padrão (ignora outras instruções) e cria um índice hipotético para cada uma.
O identificador é gerado dinamicamente. Neste exemplo, o valor é 13925.

Execute a instrução EXPLAIN para verificar se o banco de dados usaria o índice. Exemplo:

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Saída esperada:

                                     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)

Nota

O banco de dados utiliza esse tipo de índice.

Execute a instrução EXPLAIN ANALYZE para verificar se o banco de dados usa o índice hipotético durante a execução real. Exemplo:

EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

Saída esperada:

                                            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)

Nota

Na execução real, o banco de dados não utiliza o índice hipotético.

Operações com índices hipotéticos

A extensão hypopg também fornece funções e visualizações úteis.

Visualização hypopg_list_indexes: lista todos os índices hipotéticos criados. Exemplo:

SELECT * FROM hypopg_list_indexes;

Saída esperada:

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

Função hypopg(): lista todos os índices hipotéticos criados no mesmo formato da pg_index. Exemplo:

SELECT * FROM hypopg();

Saída esperada:

      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)

Função hypopg_get_indexdef(oid): retorna o comando CREATE INDEX real com base no identificador do índice hipotético. Exemplo:

SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes;

Saída esperada:

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

Função hypopg_relation_size(oid): estima o tamanho de um índice hipotético. Exemplo:

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

Saída esperada:

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

Função hypopg_drop_index(oid): exclui o índice hipotético com o identificador especificado. Exemplo:
```
SELECT hypopg_drop_index(13925);
```
Saída esperada:
```
 hypopg_drop_index
-------------------
 t
(1 row)
```
Função hypopg_reset(): exclui todos os índices hipotéticos. Exemplo:
```
SELECT hypopg_reset();
```
Saída esperada:
```
 hypopg_reset
--------------

(1 row)
```