F.25. hypopg — поддержка гипотетических индексов #
F.25.1. Описание #
hypopg — это расширение Postgres Pro, добавляющее поддержку гипотетических индексов.
Гипотетический или виртуальный индекс — это индекс, который на самом деле не существует, поэтому на его создание не расходуются ресурсы процессора, диска и прочие. Такие индексы помогают без затрат ресурсов выяснить, можно ли повысить производительность проблемных запросов с помощью каких-либо индексов, а также узнать, будет ли Postgres Pro использовать эти индексы или нет.
F.25.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.25.3. Функции #
Расширение hypopg можно использовать, чтобы проверить, поможет ли какой-либо индекс выполнению одного или нескольких запросов. Поэтому вам должно быть уже известно, какие запросы нужно оптимизировать и какие индексы попробовать.
Гипотетические индексы, которые создаёт hypopg, хранятся не в каталоге, а в собственной памяти вашего соединения. Таким образом, это не раздувает таблицы и не влияет на параллельные соединения.
Кроме того, поскольку гипотетические индексы фактически не существуют, hypopg гарантирует, что они будут использоваться только в обычном операторе EXPLAIN (без параметра ANALYZE).
Поддерживаются следующие методы доступа:
btreebrinhashbloom(требуется наличие установленного расширения bloom)
Примечание
Использование hypopg требует некоторых знаний о команде EXPLAIN.
F.25.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.25.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.25.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.25.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 будет запрашивать реальный идентификатор объекта, для чего потребуется получать больше блокировок, и не будет работать на резервных серверах, но позволит использовать полный диапазон идентификаторов объектов.
Обратите внимание, что переключение этого параметра не требует сброса записей.
