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

开发 前端
使用 HypoPG 需要对 EXPLAIN 命令有一定的了解。如果您需要有关此命令的更多信息,可以查看官方文档。另外也还有很多非常好的资源。
 本文转载自微信公众号「 红石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 | <18284>btree_hypo_id
(1 row)

该函数返回两列:

• 假设索引的对象标识符

• 生成的假设索引名称

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

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)

是的,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 | <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
----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <18284>btree_hypo_id |      13543 |    18122 |       1 | f           | 1      | 0            | 1978     | <NULL>    | <NULL>   | <NULL>  |  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
-----------------------+----------------------------------------------
 <18284>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
-----------------------+----------------
 <18284>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,<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)

责任编辑:武晓燕 来源: 红石PG
相关推荐

2020-08-30 14:31:40

Python编程语言开发

2015-06-29 11:38:47

物联网

2015-09-21 09:39:35

Java数组初始化

2018-01-18 05:20:59

2021-04-30 21:00:14

VRAR虚拟现实技术

2021-02-26 09:04:22

数组ArrayListHashMap

2013-05-14 10:41:16

Palo AltoNGFWUTM

2010-11-17 11:11:44

跳槽

2021-01-29 09:17:00

深度学习AI人工智能

2024-03-21 17:29:45

2024-06-20 12:38:07

2013-08-14 18:25:28

2022-12-26 09:16:45

Guava架构模型

2016-11-28 11:19:48

术语神秘

2020-08-03 10:43:33

数据中心人工智能技术

2022-04-26 06:53:37

HBaseMD5RowKey

2018-03-09 10:34:48

显卡参数超频

2020-10-27 13:29:11

人工智能编程技术

2022-03-21 09:47:02

深度学习模型人工智能

2017-01-19 17:57:47

大数据
点赞
收藏

51CTO技术栈公众号