F.24. hypopg — поддержка гипотетических индексов #

F.24.1. Описание #

hypopg — это расширение Postgres Pro, добавляющее поддержку гипотетических индексов.

Гипотетический или виртуальный индекс — это индекс, который на самом деле не существует, поэтому на его создание не расходуются ресурсы процессора, диска и прочие. Такие индексы помогают без затрат ресурсов выяснить, можно ли повысить производительность проблемных запросов с помощью каких-либо индексов, а также узнать, будет ли Postgres Pro использовать эти индексы или нет.

F.24.2. Установка #

Расширение hypopg поставляется вместе с Postgres Pro в виде отдельного пакета hypopg-ent-16 (подробные инструкции по установке приведены в Главе 16). После установки Postgres Pro создайте расширение hypopg:

CREATE EXTENSION hypopg;

Расширение hypopg теперь доступно. Это можно проверить с помощью psql:

\dx
                     List of installed extensions
  Name   | Version |   Schema   |             Description
---------+---------+------------+-------------------------------------
 hypopg  | 1.4.1   | public     | Hypothetical indexes for Postgres Pro
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Видно, что расширение hypopg установлено.

F.24.3. Функции #

Расширение hypopg можно использовать, чтобы проверить, поможет ли какой-либо индекс выполнению одного или нескольких запросов. Поэтому вам должно быть уже известно, какие запросы нужно оптимизировать и какие индексы попробовать.

Гипотетические индексы, которые создаёт hypopg, хранятся не в каталоге, а в собственной памяти вашего соединения. Таким образом, это не раздувает таблицы и не влияет на параллельные соединения.

Кроме того, поскольку гипотетические индексы фактически не существуют, hypopg гарантирует, что они будут использоваться только в обычном операторе EXPLAIN (без параметра ANALYZE).

Поддерживаются следующие методы доступа:

  • btree

  • brin

  • hash

  • bloom (требуется наличие установленного расширения bloom)

Примечание

Использование hypopg требует некоторых знаний о команде EXPLAIN.

F.24.3.1. Создание гипотетического индекса #

hypopg_create_index() #

Рассмотрим простой пример:

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

У этой таблицы нет индекса. Предположим, мы хотим проверить, поможет ли индекс простому запросу. Сначала посмотрим, как он себя ведёт:

EXPLAIN SELECT val FROM hypo WHERE id = 1;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=14)
   Filter: (id = 1)
(2 rows)

Используется простое последовательное сканирование, поскольку индекса в таблице нет. Построение индекса B-дерева по столбцу id должно помочь этому запросу. Проверим это с помощью hypopg. Функция hypopg_create_index() примет любые стандартные операторы CREATE INDEX (любые другие операторы, переданные этой функции, будут проигнорированы) и создаст гипотетический индекс для каждого:

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;
 indexrelid |      indexname
------------+----------------------
      18284 | <18284>btree_hypo_id
(1 row)

Функция возвращает два столбца:

  • идентификатор объекта гипотетического индекса

  • сгенерированное имя гипотетического индекса

Можно снова запустить EXPLAIN, чтобы увидеть, будет ли Postgres Pro использовать этот индекс:

EXPLAIN SELECT val FROM hypo WHERE id = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using <18284>btree_hypo_id on hypo  (cost=0.04..8.06 rows=1 width=10)
   Index Cond: (id = 1)
(2 rows)

Да, Postgres Pro будет использовать такой индекс. Теперь нужно убедиться, что гипотетический индекс не будет использоваться для фактического выполнения запроса:

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.046..46.390 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning time: 0.160 ms
 Execution time: 46.460 ms
(5 rows)

Это всё, что необходимо для создания гипотетических индексов и проверки, будет ли Postgres Pro использовать такие индексы.

F.24.3.2. Управление гипотетическими индексами #

В представлении hypopg_list_indexes перечислены все созданные гипотетические индексы.

SELECT * FROM hypopg_list_indexes ;
 indexrelid |      index_name       | schema_name | table_name | am_name
------------+-----------------------+-------------+------------+---------
      18284 | <18284>btree_hypo_id  | public      | hypo       | btree
(1 row)
hypopg() #

Функция hypopg() выводит список всех гипотетических индексов, созданных в том же формате, что и pg_index.

SELECT * FROM hypopg() ;
      indexname       | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <18284>btree_hypo_id |      13543 |    18122 |       1 | f           | 1      | 0            | 1978     | <NULL>    | <NULL>   | <NULL>  |  403
(1 row)
hypopg_get_indexdef(oid) #

Функция hypopg_get_indexdef(oid) перечисляет операторы CREATE INDEX, которые воссоздадут сохранённый гипотетический индекс.

SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;
      index_name       |             hypopg_get_indexdef
-----------------------+----------------------------------------------
 <18284>btree_hypo_id  | CREATE INDEX ON public.hypo USING btree (id)
(1 row)
hypopg_relation_size(oid) #

Функция hypopg_relation_size(oid) вычисляет размер гипотетического индекса:

SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
  FROM hypopg_list_indexes ;
      index_name       | pg_size_pretty
-----------------------+----------------
 <18284>btree_hypo_id  | 2544 kB
(1 row)
hypopg_drop_index(oid) #

Функция hypopg_drop_index(oid) удаляет указанный гипотетический индекс.

hypopg_reset() #

Функция hypopg_reset() удаляет все гипотетические индексы.

F.24.3.3. Гипотетическое скрытие существующих индексов #

hypopg_hide_index(oid) #

Можно гипотетически скрывать как существующие, так и гипотетические индексы. Если вы хотите протестировать эту возможность согласно описанию в документации, сначала вызовите функцию hypopg_reset(), чтобы очистить систему от других гипотетических индексов.

В качестве примера рассмотрим два индекса:

SELECT hypopg_reset();
CREATE INDEX ON hypo(id);
CREATE INDEX ON hypo(id, val);
EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using hypo_id_val_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

План запроса теперь использует индекс hypo_id_val_idx.

Функция hypopg_hide_index(oid) позволяет скрыть индекс в выводе EXPLAIN, используя его OID. Возвращает true, если индекс был успешно скрыт, и false в противном случае.

SELECT hypopg_hide_index('hypo_id_val_idx'::REGCLASS);
 hypopg_hide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

В качестве примера предположим, что план запроса в данный момент использует индекс hypo_id_val_idx. Для продолжения тестирования используйте функцию hypopg_hide_index(oid), чтобы скрыть другой индекс.

SELECT hypopg_hide_index('hypo_id_idx'::REGCLASS);
 hypopg_hide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                    QUERY PLAN
-------------------------------------------------------
Seq Scan on hypo  (cost=0.00..180.00 rows=1 width=13)
Filter: (id = 1)
(2 rows)
hypopg_unhide_index(oid) #

Функция hypopg_unhide_index(oid) восстанавливает ранее скрытый индекс в выводе EXPLAIN, используя его OID. Возвращает true, если индекс был успешно восстановлен, и false в противном случае.

SELECT hypopg_unhide_index('hypo_id_idx'::regclass);
 hypopg_unhide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)
hypopg_unhide_all_index(oid) #

Функция hypopg_unhide_all_index() восстанавливает все скрытые индексы и возвращает void.

hypopg_hidden_indexes() #

Функция hypopg_hidden_indexes() возвращает список OID для всех скрытых индексов.

SELECT * FROM hypopg_hidden_indexes();
 indexid
---------
526604
(1 rows)

В представлении hypopg_hidden_indexes содержится форматированный список всех скрытых индексов.

SELECT * FROM hypopg_hidden_indexes;
  indexrelid |      index_name      | schema_name | table_name | am_name | is_hypo
-------------+----------------------+-------------+------------+---------+---------
      526604 | hypo_id_val_idx      | public      | hypo       | btree   | f
(1 rows)

Примечание

Гипотетические индексы также можно скрывать.

SELECT hypopg_create_index('CREATE INDEX ON hypo(id)');
    hypopg_create_index
------------------------------
(12659,<12659>btree_hypo_id)
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                    QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using "<12659>btree_hypo_id" on hypo  (cost=0.04..8.05 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

Теперь, когда используется гипотетический индекс, можно попробовать скрыть его, чтобы увидеть изменение:

SELECT hypopg_hide_index(12659);
 hypopg_hide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

SELECT * FROM hypopg_hidden_indexes;
  indexrelid |      index_name      | schema_name | table_name | am_name | is_hypo
-------------+----------------------+-------------+------------+---------+---------
       12659 | <12659>btree_hypo_id | public      | hypo       | btree   | t
      526604 | hypo_id_val_idx      | public      | hypo       | btree   | f
(2 rows)

Примечание

Если гипотетический индекс был скрыт, он будет автоматически раскрыт при удалении функцией hypopg_drop_index(oid) или hypopg_reset().

SELECT hypopg_drop_index(12659);

SELECT * FROM hypopg_hidden_indexes;
  indexrelid |      index_name      | schema_name | table_name | am_name | is_hypo
-------------+----------------------+-------------+------------+---------+---------
      526604 | hypo_id_val_idx      | public      | hypo       | btree   | f
(2 rows)

F.24.4. Параметры GUC #

Доступны следующие параметры конфигурации (GUC), которые можно изменять интерактивно:

hypopg.enabled #

По умолчанию включён (on). Используйте этот параметр для глобального включения или отключения hypopg. Если расширение отключено, гипотетические индексы не будут использоваться, но уже определённые гипотетические индексы не будут удалены.

hypopg.use_real_oids #

По умолчанию выключен (off). При таком значении hypopg не будет использовать «реальные» идентификаторы объектов, а вместо этого позаимствует их из диапазона ~ 14000 / 16384 (соответственно наименьший неиспользуемый OID меньше FirstNormalObjectId и FirstNormalObjectId), которые зарезервированы Postgres Pro для использования в будущих выпусках. Это не вызывает никаких проблем, так как свободный диапазон динамически вычисляется при первом использовании hypopg подключением и расширение может работать на резервных серверах. Однако одновременно может существовать не более 2500 гипотетических индексов, и после превышения максимального количества объектов создание нового гипотетического индекса станет очень медленным, пока не будет вызвана функция hypopg_reset().

Если эти недостатки вызывают проблемы, можно включить этот параметр. Тогда hypopg будет запрашивать реальный идентификатор объекта, для чего потребуется получать больше блокировок, и не будет работать на резервных серверах, но позволит использовать полный диапазон идентификаторов объектов.

Обратите внимание, что переключение этого параметра не требует сброса записей.