(文档)第120讲:索引调优利器—hypopg使用技巧

目标

• hypopg概述

• hypopg安装

• hypopg应用案例

hypopg概述

hypopg是创建虚拟索引的工具，它能有助于了解特定索引是否可以提高有问题查询的性能。假设的或虚拟的索引是一种不存在的索引，因此创建它不需要CPU、磁盘或任何资源。可以利用它去发现PostgreSQL是否会使用这些索引，而无需花费资源来创建它们，是创建索引进行调优的有利助手。

hypopg工作原理

• 什么是假想索引(Hypothetical Index)

假想索引是一种虚拟索引(Virtual Index)，它仅在数据库内存中模拟索引结构，不会实际存储数据或占用磁盘空间。PostgreSQL查询优化器可以像对待真实索引一样评估其对查询计划的影响，但创建过程几乎瞬间完成，且不会对数据库性能产生任何负面影响，虚拟索引只保存在当前会话的内存中，退出数据库后失效，需要再次创建。

• HypoPG解决的核心痛点

资源消耗大：创建大型表索引可能需要数小时及数十GB磁盘空间

生产环境风险：在生产库直接测试索引可能导致性能波动

试错成本高：无效索引的创建与删除过程耗时且影响业务连续性

• HypoPG通过以下创新机制解决这些问题：

索引元数据仅存储在当前数据库连接的内存中完全隔离的测试环境，不影响其他数据库会话支持创建/删除/隐藏多个假想索引进行组合测试。

hypopg技术实现原理

HypoPG通过PostgreSQL的扩展机制(Extension) 实现，主要工作流程如下：

hypopg版本兼容性矩阵

不同PostgreSQL的版本与hypopg版本之间的兼容性匹配。

hypopg安装

1、下载

wget https://github.com/HypoPG/hypopg/archive/1.4.1.zip

2、源码安装(适用于所有系统)

make

make install

3、安装插件

CREATE EXTENSION hypopg;

4、验证

\dx

\dx+ hypopg

Hypopg使用开关

HypoPG提供了两个关键配置参数，可根据需求动态调整：

Hypopg函数

HypoPG提供了创建、删除和获取索引信息的函数，可以监控数据表和索引的存储情况。HypoPG提供了一系列的函数和参数。

CREATE EXTENSION hypopg：用于安装HypoPG扩展。

hypopg_create_index：函数用于创建虚拟索引。

hypopg_drop_index：函数用于删除指定的虚拟索引。

hypopg_get_indexdef：函数用于获取关于索引的定义信息。

hypopg_relation_size：函数用于估算假设索引的大小。

hypopg_hide_index：函数用于禁用某个索引。

hypopg_unhide_index：函数用于解禁某个索引。

hypopg使用技巧

• 创建表并插入数据

• CREATE TABLE hypo (id integer, val text) ;

• INSERT INTO hypo SELECT i, ‘line ‘ || i FROM generate_series(1, 100000) i ;

• VACUUM ANALYZE hypo ;

• 查看执行计划

• 创建虚拟索引

• SELECT * FROM hypopg_create_index(‘CREATE INDEX ON hypo (id)’);

• 查看可用的虚拟索引

• SELECT * FROM hypopg_list_indexes ;

• 如果需要有关虚拟索引更多信息，hypopg()函数将以类似于pg_index系统目录的方式返回虚拟索引，创建完虚拟索引后，执行计划就会使用。

• EXPLAIN SELECT * FROM hypo WHERE id = 1;

• 如果是真正执行则不会用到虚拟索引。

• EXPLAIN ANALYZE SELECT * FROM hypo WHERE id = 1;

Hypopg管理

• 删除虚拟索引/退出数据库连接

• SELECT hypopg_drop_index(indexrelid);

• 清除内存中虚拟索引信息

• SELECT hypopg_reset();

Hypopg管理真实索引

• hypopg插件还提供了隐藏和恢复真实索引的功能，让索引暂时不可见，增加了索引的管理功能，只针对当前会话生效。

• 1、创建2个虚拟索引，并执行EXPLAIN。

• CREATE INDEX ON hypo(id);

• CREATE INDEX ON hypo(id, val);

• EXPLAIN SELECT * FROM hypo WHERE id = 1;

• • 2、隐藏某个索引，让执行计划选择其它索引

• SELECT hypopg_hide_index(‘hypo_id_val_idx’::REGCLASS);

• EXPLAIN SELECT * FROM hypo WHERE id = 1;

• • 3、继续隐藏某个索引，让执行计划选择顺序扫描

• SELECT hypopg_hide_index(‘hypo_id_val_idx’::REGCLASS);

• EXPLAIN SELECT * FROM hypo WHERE id = 1;

• • 4、查看隐藏索引信息

• SELECT * FROM hypopg_hidden_indexes();

• SELECT * FROM hypopg_hidden_indexes;

• • 5、依次取消索引隐藏，执行计划自动使用索引

• SELECT hypopg_unhide_index(‘hypo_id_idx’::regclass);

• SELECT hypopg_unhide_index(‘hypo_id_val_idx’::regclass);

Hypopg使用注意事项

• 问题1：优化器不使用虚拟索引原因

• 统计信息过时：执行ANALYZE table_name更新统计信息

• 索引选择性低：查询返回表中大部分数据，优化器选择全表扫描

• 索引设计不当：调整索引字段顺序或添加WHERE子句

• 配置参数影响：检查enable_indexscan等参数是否被禁用

• 问题2：内存中假想索引过多

• 当虚拟索引太多后，可能导致执行计划混乱，重新设置虚拟索引：SELECT hypopg_reset();

• 问题3：在备用库使用HypoPG

• 默认配置下，备用库可能无法使用HypoPG，设置参数：SET hypopg.use_real_oids = off;

Hypopg未来展望

• HypoPG作为PostgreSQL生态系统中独特的性能调优工具，通过内存级虚拟索引模拟技术，彻底改变了传统索引优化流程。它将索引测试从”小时级”缩短到”分钟级”，同时消除了资源浪费和生产环境风险。无论是数据库管理员、应用开发者还是DevOps工程师，都能通过HypoPG显著提升数据库性能调优效率。

• 随着PostgreSQL内核的不断发展，HypoPG未来可能会集成更多高级特性：

• 自动推荐最优索引组合

• 估算索引实际存储大小和维护成本

• 支持更多索引类型(如BRIN、GIN)

• 与查询重写系统深度集成

PostgreSQL中文社区认证

• 与工信部人才交流中心合作，推出PostgreSQL初/中/高级证书，证书中明确指定适用于信息技术应用创新人才岗位能力评定要求。