hypopg — это расширение Postgres Pro, добавляющее поддержку гипотетических индексов.
Гипотетический или виртуальный индекс — это индекс, который на самом деле не существует, поэтому на его создание не расходуются ресурсы процессора, диска и прочие. Такие индексы помогают без затрат ресурсов выяснить, можно ли повысить производительность проблемных запросов с помощью каких-либо индексов, а также узнать, будет ли Postgres Pro использовать эти индексы или нет.
Расширение 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 установлено.
Расширение hypopg можно использовать, чтобы проверить, поможет ли какой-либо индекс выполнению одного или нескольких запросов. Поэтому вам должно быть уже известно, какие запросы нужно оптимизировать и какие индексы попробовать.
Гипотетические индексы, которые создаёт hypopg, хранятся не в каталоге, а в собственной памяти вашего соединения. Таким образом, это не раздувает таблицы и не влияет на параллельные соединения.
Кроме того, поскольку гипотетические индексы фактически не существуют, hypopg гарантирует, что они будут использоваться только в обычном операторе EXPLAIN (без параметра ANALYZE).
Поддерживаются следующие методы доступа:
btree
brin
hash
bloom (требуется наличие установленного расширения bloom)
Использование hypopg требует некоторых знаний о команде EXPLAIN.
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 использовать такие индексы.
В представлении 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() удаляет все гипотетические индексы.
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)Доступны следующие параметры конфигурации (GUC), которые можно изменять интерактивно:
hypopg.enabled
#По умолчанию включён (on). Используйте этот параметр для глобального включения или отключения hypopg. Если расширение отключено, гипотетические индексы не будут использоваться, но уже определённые гипотетические индексы не будут удалены.
hypopg.use_real_oids
#По умолчанию выключен (off). При таком значении hypopg не будет использовать «реальные» идентификаторы объектов, а вместо этого позаимствует их из диапазона ~ 14000 / 16384 (соответственно наименьший неиспользуемый OID меньше FirstNormalObjectId и FirstNormalObjectId), которые зарезервированы Postgres Pro для использования в будущих выпусках. Это не вызывает никаких проблем, так как свободный диапазон динамически вычисляется при первом использовании hypopg подключением и расширение может работать на резервных серверах. Однако одновременно может существовать не более 2500 гипотетических индексов, и после превышения максимального количества объектов создание нового гипотетического индекса станет очень медленным, пока не будет вызвана функция hypopg_reset().
Если эти недостатки вызывают проблемы, можно включить этот параметр. Тогда hypopg будет запрашивать реальный идентификатор объекта, для чего потребуется получать больше блокировок, и не будет работать на резервных серверах, но позволит использовать полный диапазон идентификаторов объектов.
Обратите внимание, что переключение этого параметра не требует сброса записей.