要验证索引是否有效，不一定要马上创建索引哦-每日运维

本文转载自微信公众号「红石PG」，作者红石PG。

介绍

如果要检查某些索引是否有助于一个或多个查询，则 HypoPG 非常有用。因此，您应该已经知道需要优化哪些查询，以及要尝试哪些索引。

此外，HypoPG 将要创建的假设索引不会存储在任何系统表中，而是存储在您连接的私有内存中。因此，它不会使任何表出现膨胀，也不会影响任何并发连接。

此外，由于假设索引实际上并不存在，因此 HypoPG 仅能确保在使用简单的 EXPLAIN 语句（不带 ANALYZE 选项）时会使用它们。

安装扩展

跟任何其他扩展一样，您必须将其安装在希望能够使用它的所有数据库上。只需用一个有足够权限的用户，连接到要安装 HypoPG 的数据库上，执行以下查询即可完成：

CREATE EXTENSION hypopg;

现在就可以使用 HypoPG 了。您可以使用 psql 轻松地检查扩展是否存在：

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

如您所见，已安装了 hypopg 版本 1.1.0。如果需要使用纯 SQL 进行检查，请参考 pg_extension 表文档。

配置

以下是可用的配置参数（GUC），并且可以交互式更改：

• hypopg.enabled:默认为on。使用此参数可全局启用或禁用 HypoPG。禁用 HypoPG 后，任何假设索引都不会被使用，但不会删除定义的假设索引。

• hypopg.use_real_oids:默认为off。默认情况下，HypoPG 不会使用 “真实的” 对象标识符，而是从 ~ 14000 / 16384（分别是低于 FirstNormalObjectId 的最低未使用的 oid 和 FirstNormalObjectId）范围内借用标识符，这些标识符由 PostgreSQL 保留，以备将来在未来的版本中使用。这不会造成任何问题，因为可用范围是在连接首次使用 HypoPG 时动态计算的，并且还有可以应用在备用服务器上的优势。但缺点是，您不能同时拥有大约 2500 个假设索引，并且一旦已经创建了超出最多数目的对象，创建新的假设索引会变得非常慢，一直到hypopg_reset()被调用。如果这些缺点有问题，您可以启用此参数。然后，HypoPG 将要求提供真实的对象标识符，这将需要获取更多锁，并且不会在备用服务器工作，但允许使用所有可用的对象标识符。请注意，切换此参数不需要重置已经定义的假设索引，两者可以同时共存。

支持的访问方式

支持以下访问方式：

• btree

• brin

• hash（需要 PostgreSQL 10 或更高版本）

• bloom（需要安装 bloom 扩展）

创建假设索引

注意：使用 HypoPG 需要对 EXPLAIN 命令有一定的了解。如果您需要有关此命令的更多信息，可以查看官方文档。另外也还有很多非常好的资源。

为了清楚起见，让我们使用一个非常简单的测试用例，看看它是如何工作的：

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)

由于表上不存在索引，因此使用了普通的顺序扫描。在 id 列上创建一个简单 btree 索引，应该有助于此查询。让我们来看看 HypoPG。函数 hypopg_create_index() 可接受任何标准的 CREATE INDEX 语句（传递给此函数的任何其他语句都会被忽略），并为每个语句创建一个假设索引：

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

该函数返回两列：

• 假设索引的对象标识符

• 生成的假设索引名称

我们可以再次运行 EXPLAIN，来查看 PostgreSQL 是否会使用这个索引：

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

是的，PostgreSQL 会使用这样的索引。为了确定，让我们检查下实际运行查询的时候，假设索引不会被用到：

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)

这就是创建假设索引并查看 PostgreSQL 是否会使用此类索引的全部内容。

操作假设索引

还有一些其他的很方便的函数和视图：

• hypopg_list_indexes：列出已创建的所有假设索引的视图

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

• **hypopg()**：采用和 pg_index 相同的格式，列出所有已创建的假设索引的函数

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

• **hypopg_get_indexdef(oid)**：列出 CREATE INDEX 语句的函数，该语句可用于重新创建存储的假设索引

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

• **hypopg_relation_size(oid)**：用于估计一个假设索引的大小的函数：

SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
  FROM hypopg_list_indexes;
      index_name       | pg_size_pretty
-----------------------+----------------
 btree_hypo_id  | 2544 kB
(1 row)

• **hypopg_drop_index(oid)**：用于删除指定假设索引的函数

• **hypopg_reset()**：用于删除所有假设索引的函数

假设性隐藏现有索引

您可以假设性隐藏现有索引和假设索引。如果要按照文档中的说明对其进行测试，则应首先使用 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)**：使用其 OID 还原 EXPLAIN 输出中以前隐藏的索引的函数。如果索引已成功还原，则返回 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()**：恢复所有隐藏的索引，并返回 void 的函数。

• **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,btree_hypo_id)
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                    QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using "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 | 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)

要验证索引是否有效，不一定要马上创建索引哦

介绍

安装扩展

配置

支持的访问方式

创建假设索引

操作假设索引

假设性隐藏现有索引

相关文章

发布评论取消回复

宇宙之一粟

VirtualBox 7.1 Beta 发布：采用全新现代化 UI、更换 Logo

兼容MySQL程度最高的国产数据库来了

Llama 3.1 – 405B、70B 和 8B 的多语言与长上下文能力解析

要验证索引是否有效，不一定要马上创建索引哦

介绍

安装扩展

配置

支持的访问方式

创建假设索引

操作假设索引

假设性隐藏现有索引

相关文章

发布评论 取消回复

宇宙之一粟

VirtualBox 7.1 Beta 发布：采用全新现代化 UI、更换 Logo

兼容MySQL程度最高的国产数据库来了

Llama 3.1 – 405B、70B 和 8B 的多语言与长上下文能力解析

发布评论取消回复