F.26. hypopg — поддержка гипотетических индексов
Пред. НаверхПриложение F. Дополнительно поставляемые модули и расширения, поставляемые в postgrespro-std-17-contribНачало След.

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

F.26.1. Описание
F.26.2. Установка
F.26.3. Функции
F.26.4. Параметры GUC

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

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

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

F.26.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.26.3. Функции #

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

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

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

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

  • btree

  • brin

  • hash

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

Примечание

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

F.26.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.26.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.26.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.26.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 будет запрашивать реальный идентификатор объекта, для чего потребуется получать больше блокировок, и не будет работать на резервных серверах, но позволит использовать полный диапазон идентификаторов объектов.

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

Пред. Наверх След.
F.25. Модули словарей Hunspell Начало F.27. intagg — агрегатор и нумератор целых чисел

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.