PostgreSQL 商用版本EPAS(阿里云ppas) 索引推荐功能使用

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , PPAS , enterprisedb , 索引推荐


背景

PostgreSQL商用版本EnterpriseDB,内置了索引推荐功能,原理与这里描述类似。

《PostgreSQL SQL自动优化案例 - 极简,自动推荐索引》

目前仅支持BTREE索引的(单列或多列)推荐,(有一些限制,暂时不支持继承表的推荐,暂时不支持表达式索引的推荐)。

索引推荐用法

1、创建推荐存储表与推荐查阅函数。

-- 在需要用到索引推荐的数据库中,安装索引推荐用到的表、函数、视图   
-- 调用 index_advisor.sql    
psql -f $PGHOME/share/contrib/index_advisor.sql    
   
psql.bin:/opt/edb/as10/share/contrib/index_advisor.sql:22: ERROR:  relation "index_advisor_log" already exists   
psql.bin:/opt/edb/as10/share/contrib/index_advisor.sql:25: ERROR:  relation "ia_reloid" already exists   
psql.bin:/opt/edb/as10/share/contrib/index_advisor.sql:28: ERROR:  relation "ia_backend_pid" already exists   
CREATE FUNCTION   
CREATE FUNCTION   
CREATE VIEW   

2、加载推荐模块

-- 在需要用到索引推荐的会话中加载推荐模块   
load 'index_advisor';   

或者可以设置为自动加载。

vi $PGDATA/postgresql.conf   
   
shared_preload_libraries = 'index_advisor'   
   
   
# - Other Defaults -   
   
#dynamic_library_path = '$libdir'   
#local_preload_libraries = ''   
#session_preload_libraries = ''   
   
#oracle_home =''        # path to the Oracle home directory;   
                                        # only used by OCI Dblink; defaults   
                                        # to ORACLE_HOME environment variable.   

3、设置会话参数,选择是否需要推荐。如果需要推荐,则会自动对这个会话接下来的SQL进行索引推荐(如果多次执行,并被推荐的话,SQL都会被记录在表中)。

postgres=# set index_advisor.enabled TO on;   
SET   

设置后,这个会话的所有QUERY都会被推荐模块进行计算。并将需要推荐的SQL记录下来,包括PID,索引列,索引估算大小,成本估算等。

postgres=# select * from pgbench_history where bid=2;   
 tid  | bid |   aid   | delta |           mtime           | filler    
------+-----+---------+-------+---------------------------+--------   
  444 |   2 | 4438685 | -2029 | 12-JAN-18 20:46:43.07816  |    
   
postgres=# select pg_backend_pid();   
 pg_backend_pid    
----------------   
          32898   
(1 row)   
   
postgres=# select * from index_advisor_log ;   
 reloid |     relname      | attrs | benefit | index_size | backend_pid |            timestamp                
--------+------------------+-------+---------+------------+-------------+----------------------------------   
  16397 | pgbench_history  | {2}   |  175955 |    1260456 |       32898 | 14-JAN-18 21:32:19.564707 +08:00   

4、查阅推荐。

postgres=# select * from show_index_recommendations(32898);   
                                                      show_index_recommendations                                                         
--------------------------------------------------------------------------------------------------------------------------------------   
 create index idx_pgbench_history_bid on public.pgbench_history(bid);/* size: 1260456 KB, benefit: 175955, gain: 0.139596676718584 */   
(1 row)   
   
或   
   
postgres=# select * from show_index_recommendations();   
                                                      show_index_recommendations                                                         
--------------------------------------------------------------------------------------------------------------------------------------   
 create index idx_pgbench_history_bid on public.pgbench_history(bid);/* size: 1260456 KB, benefit: 175955, gain: 0.139596676718584 */   
(1 row)   

5、查看虚拟索引执行计划(HYPOTHETICAL PLAN 表示包含虚拟索引时的执行计划)。

postgres=# explain select * from pgbench_history where bid=2;   
                                             QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------   
 Gather  (cost=1000.00..455329.29 rows=470413 width=116)   
   Workers Planned: 6   
   ->  Parallel Seq Scan on pgbench_history  (cost=0.00..407287.99 rows=78402 width=116)   
         Filter: (bid = 2)   
 Result  (cost=0.00..0.00 rows=0 width=0)   
   One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text   
   ->  Bitmap Heap Scan on pgbench_history  (cost=5339.17..279373.83 rows=470413 width=116)   
         Recheck Cond: (bid = 2)   
         ->  Bitmap Index Scan on "<hypothetical-index>:8"  (cost=0.00..5221.56 rows=470413 width=0)   
               Index Cond: (bid = 2)   
(10 rows)   

6、关闭推荐。

postgres=# set index_advisor.enabled TO off;   
SET   

关闭推荐后,不会使用虚拟索引,也不会再计算是否需要推荐索引。

postgres=# explain select * from pgbench_history where bid=2;   
                                       QUERY PLAN                                           
-----------------------------------------------------------------------------------------   
 Gather  (cost=1000.00..455329.29 rows=470413 width=116)   
   Workers Planned: 6   
   ->  Parallel Seq Scan on pgbench_history  (cost=0.00..407287.99 rows=78402 width=116)   
         Filter: (bid = 2)   
(4 rows)   

7、解读推荐索引信息。

postgres=# select * from show_index_recommendations();   
                                                      show_index_recommendations                                                         
--------------------------------------------------------------------------------------------------------------------------------------   
 create index idx_pgbench_history_bid on public.pgbench_history(bid);/* size: 1260456 KB, benefit: 175955, gain: 0.139596676718584 */   
(1 row)   

size,这个索引占用的空间,对应index_advisor_log.index_size。

benefit,这个推荐索引涉及的index_advisor_log.benefit,即加上索引后,相比没有索引时的explain成本估算COST差异。

gain,index_advisor_log -> sum(benefit)/greatest(size(s)),即多次推荐的统计,获得了多少提升空间,越大,表示提升效果越好。

如何让普通用户支持推荐

1、允许普通用户加载推荐模块

需要将$PGHOME/lib/index_advisor.so拷贝到$PGHOME/lib/plugins/index_advisor.so。普通用户才能LOAD。

2、允许普通用户增删改查推荐存储表

  • Grant SELECT and INSERT privileges on the index_advisor_log table to allow a user to invoke Index Advisor.

  • Grant DELETE privileges on the index_advisor_log table to allow the specified user to delete the table contents.

  • Grant SELECT privilege on the index_recommendations view.

推荐的配置

1、在template1模板库、以及已有的数据库(如果这些库需要使用索引推荐功能的话)中安装index_advisor.sql

psql template1 -f $PGHOME/share/contrib/index_advisor.sql    

psql template1 <<EOF
grant select,insert,delete on index_advisor_log to public;
grant select on index_recommendations to public;
EOF

2、配置postgresql.conf,默认加载模块

vi $PGDATA/postgresql.conf   
   
shared_preload_libraries = 'index_advisor'   

3、配置postgresql.conf,index_advisor.enabled=off,默认关闭计算

vi $PGDATA/postgresql.conf   
   
index_advisor.enabled=off   

4、当用户需要用到推荐模块时,只要打开index_advisor.enabled参数即可。

psql   
psql.bin (10.1.5)   
Type "help" for help.   
   
postgres=# show index_advisor.enabled ;   
 index_advisor.enabled    
-----------------------   
 off   
(1 row)   
   
postgres=# explain select * from pgbench_history where bid=2;   
                                       QUERY PLAN                                           
-----------------------------------------------------------------------------------------   
 Gather  (cost=1000.00..455329.29 rows=470413 width=116)   
   Workers Planned: 6   
   ->  Parallel Seq Scan on pgbench_history  (cost=0.00..407287.99 rows=78402 width=116)   
         Filter: (bid = 2)   
(4 rows)   
   
postgres=# set index_advisor.enabled =on;   
SET   
postgres=# explain select * from pgbench_history where bid=2;   
                                             QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------   
 Gather  (cost=1000.00..455329.29 rows=470413 width=116)   
   Workers Planned: 6   
   ->  Parallel Seq Scan on pgbench_history  (cost=0.00..407287.99 rows=78402 width=116)   
         Filter: (bid = 2)   
 Result  (cost=0.00..0.00 rows=0 width=0)   
   One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text   
   ->  Bitmap Heap Scan on pgbench_history  (cost=5339.17..279373.83 rows=470413 width=116)   
         Recheck Cond: (bid = 2)   
         ->  Bitmap Index Scan on "<hypothetical-index>:2"  (cost=0.00..5221.56 rows=470413 width=0)   
               Index Cond: (bid = 2)   
(10 rows)   
   
postgres=# set index_advisor.enabled =off;   
SET   

参考

《PostgreSQL SQL自动优化案例 - 极简,自动推荐索引》

阿里云PPAS商用版本(兼容Oracle)

https://www.enterprisedb.com/docs/en/10.0/EPAS_Guide_v10/EDB_Postgres_Advanced_Server_Guide.1.36.html#pID0E0ZUE0HA

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
27天前
|
关系型数据库 MySQL 数据库
MySQL高级篇——MVCC多版本并发控制
什么是MVCC、快照读与当前读、隐藏字段、Undo Log版本链、ReadView、举例说明、InnoDB 解决幻读问题
MySQL高级篇——MVCC多版本并发控制
|
1月前
|
关系型数据库 分布式数据库 数据库
开源云原生数据库PolarDB PostgreSQL 15兼容版本正式发布
PolarDB进行了深度的内核优化,从而实现以更低的成本提供商业数据库的性能。
|
28天前
|
监控 关系型数据库 MySQL
如何升级mysql的版本
如何升级mysql的版本
88 2
|
28天前
|
存储 监控 关系型数据库
如何升级MySQL版本?
如何升级MySQL版本?
42 2
|
2月前
|
关系型数据库 MySQL Shell
MySQL数据库一键安装脚本,适合任何版本
MySQL数据库一键安装脚本,适合任何版本
45 2
|
2月前
|
关系型数据库 MySQL Linux
一文教会你如何在Linux系统中使用Docker安装Mysql 5.7版本 【详细过程+图解】
这篇文章提供了在Linux系统中使用Docker安装Mysql 5.7版本的详细过程和图解,包括安装指定版本、创建实例、启动、使用Navicat连接测试、文件挂载与端口映射、进入容器、配置文件修改以及重新启动容器等步骤。
一文教会你如何在Linux系统中使用Docker安装Mysql 5.7版本 【详细过程+图解】
|
2月前
|
关系型数据库 MySQL 数据库
同一台电脑安装两个不同版本的mysql。简单暴力有效
这篇文章介绍了在同一台电脑上安装并配置两个不同版本的MySQL数据库的过程,包括修改端口号、配置服务、修改连接端口和测试连接情况,以确保不同版本的MySQL能够正常运行而不相互干扰。
同一台电脑安装两个不同版本的mysql。简单暴力有效
|
2月前
|
算法 关系型数据库 MySQL
揭秘MySQL中的版本号排序:这个超级算法将颠覆你的排序世界!
【8月更文挑战第8天】在软件开发与数据管理中,正确排序版本号对软件更新及数据分析至关重要。因MySQL默认按字符串排序版本号,可能出现&#39;1.20.0&#39;在&#39;1.10.0&#39;之前的不合理情况。解决办法是将版本号各部分转换为整数后排序。例如,使用`SUBSTRING_INDEX`和`CAST`函数从`software`表的`version`字段提取并转换版本号,再按这些整数排序。这种方法可确保版本号按逻辑正确排序,适用于&#39;major.minor.patch&#39;格式的版本号。对于更复杂格式,需调整处理逻辑。掌握此技巧可有效应对版本号排序需求。
114 3
|
2月前
|
关系型数据库 MySQL 数据安全/隐私保护
【MySQL】手把手教你MySQL各版本忘记密码如何处理
【MySQL】手把手教你MySQL各版本忘记密码如何处理
|
3月前
|
关系型数据库 MySQL 数据库

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版