51CTO首页
AI.x社区
博客
学堂
精品班
软考社区
免费课
企业培训
鸿蒙开发者社区
WOT技术大会
IT证书
公众号矩阵
移动端
短视频免费课程课程排行直播课软考学堂
全部课程厂商认证IT技术24年11月软考PMP项目管理免费题库
在线学习
文章资源问答课堂专栏直播
51CTO
鸿蒙开发者社区
51CTO技术栈
51CTO官微
51CTO学堂
51CTO博客
CTO训练营
鸿蒙开发者社区订阅号
51CTO软考
51CTO学堂APP
51CTO学堂企业版APP
鸿蒙开发者社区视频号
51CTO软考题库
活动 短视频 专栏 极客Show 鸿蒙技术特刊

【好文推荐】openGauss索引推荐功能测试

heatdog
发布于 2023-10-30 11:32
浏览
0收藏

一、单索引推荐

单索引推荐功能，目前支持select查询，看官方介绍类似oracle中的sql_tunning_adviser,不过只是推荐创建索引。根据sql优化原理，猜测应该时根据选择来推荐索引。

1、查看sql

PanWeiDB=# create table t2 as select * from pg_tables;
INSERT 0 138

创建测试表

PanWeiDB=# select "table", "column" from gs_index_advise('select schemaname from t2 where tablename=''t2'';'); 
table | column
-------+-----------
t2 | tablename
(1 row)

调用推荐函数的地方需要注意最后面几个分号与引号，有点费解。目前建议需要在t2表tablename创建索引

PanWeiDB=# explain select schemaname from t2 where tablename='t2';
QUERY PLAN
---------------------------------------------------
Seq Scan on t2 (cost=0.00..6.72 rows=2 width=64)
Filter: (tablename = 't2'::name)
(2 rows)

执行计划显示使用seq scan。猜测应该是类似oracle中的db file sequential read。单块读，全表扫描。

PanWeiDB=# select "table", "column" from gs_index_advise('select schemaname,tableowner,schemaname from t2 where schemaname=''pg_catalog'' and tablename=''gs_auditing_policy'';');
table | column
-------+-----------
t2 | tablename
(1 row)

PanWeiDB=# explain select schemaname,tableowner,schemaname from t2 where schemaname='pg_catalog' and tablename='gs_auditing_policy';
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on t2 (cost=0.00..7.07 rows=1 width=128)
Filter: ((schemaname = 'pg_catalog'::name) AND (tablename = 'gs_auditing_policy'::name))
(2 rows)
2、创建索引

安装建议创建索引

PanWeiDB=# create index idx_t2_tablename on t2(tablename);
CREATE INDEX

PanWeiDB=# select * from pg_indexes where tablename='t2';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+------------------+------------+-----------------------------------------------------------------------------------
public | t2 | idx_t2_tablename | | CREATE INDEX idx_t2_tablename ON t2 USING btree (tablename) TABLESPACE pg_default

检查索引idx_t2_tablename已经创建成功，再次查看执行计划。

3、查看执行计划


PanWeiDB=# explain select schemaname,tableowner,schemaname from t2 where schemaname='pg_catalog' and tablename='gs_auditing_policy';
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on t2 (cost=0.00..7.07 rows=1 width=128)
Filter: ((schemaname = 'pg_catalog'::name) AND (tablename = 'gs_auditing_policy'::name))
(2 rows)

PanWeiDB=# explain select schemaname from t2 where tablename='t2';
QUERY PLAN
---------------------------------------------------
Seq Scan on t2 (cost=0.00..6.72 rows=2 width=64)
Filter: (tablename = 't2'::name)
(2 rows)

目前执行计划依然是全表扫描，可能是因为数据量太少，加大数据量测试一下索引是否生效。

PanWeiDB=# insert into t2 select * from t2;
INSERT 0 1130496

再次查看执行计划。

PanWeiDB=# explain select schemaname from t2 where tablename='t2';
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on t2 (cost=517.39..32218.42 rows=16663 width=64)
Recheck Cond: (tablename = 't2'::name)
-> Bitmap Index Scan on idx_t2_tablename (cost=0.00..513.22 rows=16663 width=0)
Index Cond: (tablename = 't2'::name)
(4 rows)

PanWeiDB=# explain select schemaname,tableowner,schemaname from t2 where schemaname='pg_catalog' and tablename='gs_auditing_policy';
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_t2_tablename on t2 (cost=0.00..8.27 rows=1 width=128)
Index Cond: (tablename = 'gs_auditing_policy'::name)
Filter: (schemaname = 'pg_catalog'::name)
(3 rows)

检查执行计划，发现执行计划中出现，ndex Scan using idx_t2_tablename on t2。推荐的索引生效。

二、虚拟索引

虚拟索引，看官方介绍意思是说先创建一条不存在的索引，测试索引的性能，测试完成后根据情况确定是否需要真实创建这个索引。

1、环境准准备

PanWeiDB=# drop index idx_t2_tablename;
DROP INDEX

PanWeiDB=# explain select schemaname from t2 where tablename='t2';
QUERY PLAN
------------------------------------------------------------
Seq Scan on t2 (cost=0.00..105670.34 rows=32329 width=64)
Filter: (tablename = 't2'::name)
(2 rows)

查看执行计划，走全表扫描

2、创建虚拟索引

PanWeiDB=# set enable_hypo_index = on;
SET

PanWeiDB=# explain select schemaname from t2 where tablename='t2';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Bitmap Heap Scan on t2 (cost=1902.80..62125.82 rows=32329 width=64)
Recheck Cond: (tablename = 't2'::name)
-> Bitmap Index Scan on <57591>btree_t2_tablename (cost=0.00..1894.72 rows=32329 width=0)
Index Cond: (tablename = 't2'::name)
(4 rows)

创建虚拟索引前需要设置enable_hypo_index参数开启虚拟索引功能。开启功能后发现使用执行计划使用的位图虚拟索引。cost值明显降低。可以真实创建这个索引。

3、操作虚拟索引

查询目前存在的虚拟索引

PanWeiDB=# select * from hypopg_display_index();
indexname | indexrelid | table | column
---------------------------+------------+-------+-------------
<57591>btree_t2_tablename | 57591 | t2 | (tablename)
(1 row)

预估创建真实索引需要使用空间大小

PanWeiDB=# select * from hypopg_estimate_size(57591);
hypopg_estimate_size
----------------------
236503040
(1 row)

删除虚拟索引

PanWeiDB=# select * from hypopg_drop_index(57591);
hypopg_drop_index
-------------------
t
(1 row)

清除所有虚拟索引

PanWeiDB=# select * from hypopg_reset_index();
hypopg_reset_index
--------------------

(1 row)

三、workload级别索引推荐

workload级别索引推荐可以理解成索引推荐功能的全局版。类似于oracle中调用sql_tuning_adviser调优整个sqlset，或者自动任务中的sql优化任务。

1、python环境问题

[root@Euler1 /]# find ./ -name index_advisor_workload.py
./soft/libcgroup/bin/dbmind/components/index_advisor/index_advisor_workload.py
./gauss/app_5b3e5810/bin/dbmind/components/index_advisor/index_advisor_workload.py

生产数据库使用python脚本总会出现版本问题。建议官方能在安装过程中自带python依赖环境，并且与主机python做隔离，类似oracle自带jdk环境。不同的操作系统python版本不同总会出现各种问题。

2、调用workload级别索引推荐

调用workload级别索引推荐功能对数据库系统默认schema pg_catalog进行索引推荐。


[omm@Euler1 ~]$ python /soft/libcgroup/bin/dbmind/components/index_advisor/index_advisor_workload.py 26000 postgres /home/omm/ad_index.txt --schema pg_catalog
Password for database user:

传入schema 为pg_catalog,输入密码

########################################################################################## Created indexes ##########################################################################################
pg_catalog: CREATE INDEX gs_asp_sampletime_index ON gs_asp USING btree (sample_time) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_name_index ON gs_auditing_policy USING btree (polname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_oid_index ON gs_auditing_policy USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_access_row_index ON gs_auditing_policy_access USING btree (accesstype, labelname, policyoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_access_oid_index ON gs_auditing_policy_access USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_filters_row_index ON gs_auditing_policy_filters USING btree (policyoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_filters_oid_index ON gs_auditing_policy_filters USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_privileges_row_index ON gs_auditing_policy_privileges USING btree (privilegetype, labelname, policyoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_auditing_policy_privileges_oid_index ON gs_auditing_policy_privileges USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_client_global_keys_oid_index ON gs_client_global_keys USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_client_global_keys_name_index ON gs_client_global_keys USING btree (global_key_name, key_namespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_client_global_keys_args_oid_index ON gs_client_global_keys_args USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_column_keys_distributed_id_index ON gs_column_keys USING btree (column_key_distributed_id) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_column_keys_oid_index ON gs_column_keys USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_column_keys_name_index ON gs_column_keys USING btree (column_key_name, key_namespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_column_keys_args_oid_index ON gs_column_keys_args USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_db_privilege_oid_index ON gs_db_privilege USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_db_privilege_roleid_index ON gs_db_privilege USING btree (roleid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_db_privilege_roleid_privilege_type_index ON gs_db_privilege USING btree (roleid, privilege_type) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_encrypted_columns_rel_id_column_name_index ON gs_encrypted_columns USING btree (rel_id, column_name) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_encrypted_columns_oid_index ON gs_encrypted_columns USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_encrypted_proc_oid ON gs_encrypted_proc USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_encrypted_proc_func_id_index ON gs_encrypted_proc USING btree (func_id) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_global_chain_relid_index ON gs_global_chain USING btree (relid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_job_argument_oid_index ON gs_job_argument USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_job_argument_name_index ON gs_job_argument USING btree (job_name, argument_name) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_job_argument_position_index ON gs_job_argument USING btree (job_name, argument_position) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_job_attribute_oid_index ON gs_job_attribute USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_job_attribute_name_index ON gs_job_attribute USING btree (job_name, attribute_name) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_masking_policy_oid_index ON gs_masking_policy USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_masking_policy_name_index ON gs_masking_policy USING btree (polname) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_masking_policy_actions_policy_oid_index ON gs_masking_policy_actions USING btree (policyoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_masking_policy_actions_row_index ON gs_masking_policy_actions USING btree (actiontype, actlabelname, policyoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_masking_policy_actions_oid_index ON gs_masking_policy_actions USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_masking_policy_filters_row_index ON gs_masking_policy_filters USING btree (policyoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_masking_policy_filters_oid_index ON gs_masking_policy_filters USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_matview_oid_index ON gs_matview USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_matviewdep_oid_index ON gs_matview_dependency USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_model_oid_index ON gs_model_warehouse USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_model_name_index ON gs_model_warehouse USING btree (modelname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_opt_model_name_index ON gs_opt_model USING btree (model_name) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_package_oid_index ON gs_package USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_package_name_index ON gs_package USING btree (pkgname, pkgnamespace) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_policy_label_name_index ON gs_policy_label USING btree (labelname, fqdnnamespace, fqdnid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_policy_label_oid_index ON gs_policy_label USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_recyclebin_dbid_relid_index ON gs_recyclebin USING btree (rcydbid, rcyrelid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_recyclebin_id_index ON gs_recyclebin USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_recyclebin_dbid_spcid_rcycsn_index ON gs_recyclebin USING btree (rcytablespace, rcydbid, rcyrecyclecsn) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_recyclebin_name_index ON gs_recyclebin USING btree (rcyname) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_recyclebin_dbid_nsp_oriname_index ON gs_recyclebin USING btree (rcynamespace, rcydbid, rcyoriginname, rcyrecyclecsn) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_recyclebin_baseid_index ON gs_recyclebin USING btree (rcybaseid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_txn_snapshot_xmin_index ON gs_txn_snapshot USING btree (snpxmin) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_txn_snapshot_csn_xmin_index ON gs_txn_snapshot USING btree (snpcsn DESC, snpxmin) TABLESPACE pg_default;
pg_catalog: CREATE INDEX gs_txn_snapshot_time_csn_index ON gs_txn_snapshot USING btree (snptime DESC, snpcsn) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX gs_uid_relid_index ON gs_uid USING btree (relid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX streaming_gather_agg_index ON pg_aggregate USING btree (aggtransfn, aggcollectfn, aggfinalfn) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_aggregate_fnoid_index ON pg_aggregate USING btree (aggfnoid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_am_name_index ON pg_am USING btree (amname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_am_oid_index ON pg_am USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_amop_oid_index ON pg_amop USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_amop_fam_strat_index ON pg_amop USING btree (amopfamily, amoplefttype, amoprighttype, amopstrategy) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_amop_opr_fam_index ON pg_amop USING btree (amopopr, amoppurpose, amopfamily) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_amproc_fam_proc_index ON pg_amproc USING btree (amprocfamily, amproclefttype, amprocrighttype, amprocnum) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_amproc_oid_index ON pg_amproc USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_app_workloadgroup_mapping_name_index ON pg_app_workloadgroup_mapping USING btree (appname) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_app_workloadgroup_mapping_oid_index ON pg_app_workloadgroup_mapping USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_attrdef_oid_index ON pg_attrdef USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_attrdef_adrelid_adnum_index ON pg_attrdef USING btree (adrelid, adnum) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_attribute_relid_attnum_index ON pg_attribute USING btree (attrelid, attnum) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_attribute_relid_attnam_index ON pg_attribute USING btree (attrelid, attname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_auth_history_oid_index ON pg_auth_history USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_auth_history_index ON pg_auth_history USING btree (roloid, passwordtime) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_auth_members_member_role_index ON pg_auth_members USING btree (member, roleid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_auth_members_role_member_index ON pg_auth_members USING btree (roleid, member) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_authid_oid_index ON pg_authid USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_authid_rolname_index ON pg_authid USING btree (rolname) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_cast_source_target_index ON pg_cast USING btree (castsource, casttarget) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_cast_oid_index ON pg_cast USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_class_tblspc_relfilenode_index ON pg_class USING btree (reltablespace, relfilenode) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_class_relname_nsp_index ON pg_class USING btree (relname, relnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_class_oid_index ON pg_class USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_collation_name_enc_nsp_index ON pg_collation USING btree (collname, collencoding, collnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_collation_oid_index ON pg_collation USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_constraint_conname_nsp_index ON pg_constraint USING btree (conname, connamespace) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_constraint_conrelid_index ON pg_constraint USING btree (conrelid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_constraint_contypid_index ON pg_constraint USING btree (contypid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_constraint_oid_index ON pg_constraint USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_conversion_default_index ON pg_conversion USING btree (connamespace, conforencoding, contoencoding, oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_conversion_oid_index ON pg_conversion USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_conversion_name_nsp_index ON pg_conversion USING btree (conname, connamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_database_datname_index ON pg_database USING btree (datname) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_database_oid_index ON pg_database USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_db_role_setting_databaseid_rol_index ON pg_db_role_setting USING btree (setdatabase, setrole) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_default_acl_role_nsp_obj_index ON pg_default_acl USING btree (defaclrole, defaclnamespace, defaclobjtype) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_default_acl_oid_index ON pg_default_acl USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_depend_depender_index ON pg_depend USING btree (classid, objid, objsubid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_depend_reference_index ON pg_depend USING btree (refclassid, refobjid, refobjsubid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_description_o_c_o_index ON pg_description USING btree (objoid, classoid, objsubid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_directory_name_index ON pg_directory USING btree (dirname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_directory_oid_index ON pg_directory USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_enum_typid_sortorder_index ON pg_enum USING btree (enumtypid, enumsortorder) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_enum_oid_index ON pg_enum USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_enum_typid_label_index ON pg_enum USING btree (enumtypid, enumlabel) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_extension_oid_index ON pg_extension USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_extension_name_index ON pg_extension USING btree (extname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_extension_data_source_name_index ON pg_extension_data_source USING btree (srcname) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_extension_data_source_oid_index ON pg_extension_data_source USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_foreign_data_wrapper_oid_index ON pg_foreign_data_wrapper USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_foreign_data_wrapper_name_index ON pg_foreign_data_wrapper USING btree (fdwname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_foreign_server_name_index ON pg_foreign_server USING btree (srvname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_foreign_server_oid_index ON pg_foreign_server USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_foreign_table_relid_index ON pg_foreign_table USING btree (ftrelid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_hashbucket_bid_index ON pg_hashbucket USING btree (bucketid, "bucketcnt", bucketmapsize) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_hashbucket_oid_index ON pg_hashbucket USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_index_indexrelid_index ON pg_index USING btree (indexrelid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_index_indrelid_index ON pg_index USING btree (indrelid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_inherits_parent_index ON pg_inherits USING btree (inhparent) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_inherits_relid_seqno_index ON pg_inherits USING btree (inhrelid, inhseqno) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_job_id_index ON pg_job USING btree (job_id) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_job_oid_index ON pg_job USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_job_proc_oid_index ON pg_job_proc USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_job_proc_id_index ON pg_job_proc USING btree (job_id) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_language_name_index ON pg_language USING btree (lanname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_largeobject_loid_pn_index ON pg_largeobject USING btree (loid, pageno) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_largeobject_metadata_oid_index ON pg_largeobject_metadata USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_namespace_nspname_index ON pg_namespace USING btree (nspname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_namespace_oid_index ON pg_namespace USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_object_index ON pg_object USING btree (object_oid, object_type) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_opclass_am_name_nsp_index ON pg_opclass USING btree (opcmethod, opcname, opcnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_opclass_oid_index ON pg_opclass USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_operator_oid_index ON pg_operator USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_operator_oprname_l_r_n_index ON pg_operator USING btree (oprname, oprleft, oprright, oprnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_opfamily_am_name_nsp_index ON pg_opfamily USING btree (opfmethod, opfname, opfnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_opfamily_oid_index ON pg_opfamily USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_partition_reloid_index ON pg_partition USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_partition_indextblid_parentoid_reloid_index ON pg_partition USING btree (indextblid, parentid, oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_partition_partoid_index ON pg_partition USING btree (relname, parttype, parentid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_partition_indextblid_index ON pg_partition USING btree (indextblid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_partition_parentoid_index ON pg_partition USING btree (parttype, parentid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_pltemplate_name_index ON pg_pltemplate USING btree (tmplname) TABLESPACE pg_global;
pg_catalog: CREATE INDEX pg_proc_proname_all_args_nsp_index ON pg_proc USING btree (proname, allargtypes, pronamespace, propackageid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_proc_oid_index ON pg_proc USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_proc_proname_args_nsp_index ON pg_proc USING btree (proname, proargtypes, pronamespace, propackageid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_proc_proname_args_nsp_new_index ON pg_proc USING btree (proname, proargtypes, pronamespace, propackageid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_publication_oid_index ON pg_publication USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_publication_pubname_index ON pg_publication USING btree (pubname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_publication_rel_map_index ON pg_publication_rel USING btree (prrelid, prpubid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_publication_rel_oid_index ON pg_publication_rel USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_range_rngtypid_index ON pg_range USING btree (rngtypid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_replication_origin_roident_index ON pg_replication_origin USING btree (roident) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_replication_origin_roname_index ON pg_replication_origin USING btree (roname text_pattern_ops) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_resource_pool_name_index ON pg_resource_pool USING btree (respool_name) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_resource_pool_oid_index ON pg_resource_pool USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_rewrite_rel_rulename_index ON pg_rewrite USING btree (ev_class, rulename) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_rewrite_oid_index ON pg_rewrite USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_rlspolicy_oid_index ON pg_rlspolicy USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_rlspolicy_polrelid_polname_index ON pg_rlspolicy USING btree (polrelid, polname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_seclabel_object_index ON pg_seclabel USING btree (objoid, classoid, objsubid, provider) TABLESPACE pg_default;
pg_catalog: CREATE INDEX pg_shdepend_depender_index ON pg_shdepend USING btree (dbid, classid, objid, objsubid) TABLESPACE pg_global;
pg_catalog: CREATE INDEX pg_shdepend_reference_index ON pg_shdepend USING btree (refclassid, refobjid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_shdescription_o_c_index ON pg_shdescription USING btree (objoid, classoid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_shseclabel_object_index ON pg_shseclabel USING btree (objoid, classoid, provider) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_statistic_relid_kind_att_inh_index ON pg_statistic USING btree (starelid, starelkind, staattnum, stainherit) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_statistic_ext_relid_kind_inh_key_index ON pg_statistic_ext USING btree (starelid, starelkind, stainherit, stakey) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_subscription_oid_index ON pg_subscription USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_subscription_subname_index ON pg_subscription USING btree (subdbid, subname) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_synonym_oid_index ON pg_synonym USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_synonym_name_nsp_index ON pg_synonym USING btree (synname, synnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_tablespace_oid_index ON pg_tablespace USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_tablespace_spcname_index ON pg_tablespace USING btree (spcname) TABLESPACE pg_global;
pg_catalog: CREATE INDEX pg_trigger_tgconstraint_index ON pg_trigger USING btree (tgconstraint) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_trigger_oid_index ON pg_trigger USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_trigger_tgrelid_tgname_index ON pg_trigger USING btree (tgrelid, tgname) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_config_oid_index ON pg_ts_config USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_config_cfgname_index ON pg_ts_config USING btree (cfgname, cfgnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_config_map_index ON pg_ts_config_map USING btree (mapcfg, maptokentype, mapseqno) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_dict_oid_index ON pg_ts_dict USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_dict_dictname_index ON pg_ts_dict USING btree (dictname, dictnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_parser_prsname_index ON pg_ts_parser USING btree (prsname, prsnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_parser_oid_index ON pg_ts_parser USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_template_tmplname_index ON pg_ts_template USING btree (tmplname, tmplnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_ts_template_oid_index ON pg_ts_template USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_type_oid_index ON pg_type USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_type_typname_nsp_index ON pg_type USING btree (typname, typnamespace) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_user_mapping_oid_index ON pg_user_mapping USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_user_mapping_user_server_index ON pg_user_mapping USING btree (umuser, umserver) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pg_user_status_index ON pg_user_status USING btree (roloid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_user_status_oid_index ON pg_user_status USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_workload_group_name_index ON pg_workload_group USING btree (workload_gpname) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pg_workload_group_oid_index ON pg_workload_group USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pgxc_class_pcrelid_index ON pgxc_class USING btree (pcrelid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pgxc_group_name_index ON pgxc_group USING btree (group_name) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pgxc_group_oid ON pgxc_group USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pgxc_node_oid_index ON pgxc_node USING btree (oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pgxc_node_id_index ON pgxc_node USING btree (node_id) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pgxc_node_name_type_index ON pgxc_node USING btree (node_name, node_type, oid) TABLESPACE pg_global;
pg_catalog: CREATE UNIQUE INDEX pgxc_slice_order_index ON pgxc_slice USING btree (relid, type, sliceorder, sindex) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX pgxc_slice_relid_index ON pgxc_slice USING btree (relid, type, relname, sindex) TABLESPACE pg_default;
pg_catalog: CREATE INDEX statement_history_time_idx ON statement_history USING btree (start_time, is_slow_sql) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_cont_query_relid_index ON streaming_cont_query USING btree (relid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_cont_query_id_index ON streaming_cont_query USING btree (id) TABLESPACE pg_default;
pg_catalog: CREATE INDEX streaming_cont_query_schema_change_index ON streaming_cont_query USING btree (matrelid, active) TABLESPACE pg_default;
pg_catalog: CREATE INDEX streaming_cont_query_lookupidxid_index ON streaming_cont_query USING btree (lookupidxid) TABLESPACE pg_default;
pg_catalog: CREATE INDEX streaming_cont_query_matrelid_index ON streaming_cont_query USING btree (matrelid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_cont_query_defrelid_index ON streaming_cont_query USING btree (defrelid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_cont_query_oid_index ON streaming_cont_query USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_reaper_status_oid_index ON streaming_reaper_status USING btree (oid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_reaper_status_id_index ON streaming_reaper_status USING btree (id) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_stream_relid_index ON streaming_stream USING btree (relid) TABLESPACE pg_default;
pg_catalog: CREATE UNIQUE INDEX streaming_stream_oid_index ON streaming_stream USING btree (oid) TABLESPACE pg_default;
################################################################################# Current workload useless indexes #################################################################################
DROP INDEX gs_global_chain_relid_index;
DROP INDEX streaming_gather_agg_index;
DROP INDEX gs_recyclebin_dbid_relid_index;
DROP INDEX pg_constraint_conrelid_index;
DROP INDEX gs_policy_label_name_index;
DROP INDEX streaming_cont_query_lookupidxid_index;
DROP INDEX pg_partition_parentoid_index;
DROP INDEX gs_txn_snapshot_xmin_index;
DROP INDEX pg_proc_proname_all_args_nsp_index;
DROP INDEX gs_db_privilege_roleid_index;
DROP INDEX gs_recyclebin_baseid_index;
DROP INDEX pg_shdepend_reference_index;
DROP INDEX pg_constraint_conname_nsp_index;
DROP INDEX gs_masking_policy_actions_policy_oid_index;
DROP INDEX pg_depend_depender_index;
DROP INDEX gs_txn_snapshot_csn_xmin_index;
DROP INDEX pg_trigger_tgconstraint_index;
DROP INDEX gs_recyclebin_name_index;
DROP INDEX pg_proc_proname_args_nsp_index;
DROP INDEX pg_constraint_contypid_index;
DROP INDEX pg_proc_proname_args_nsp_new_index;
DROP INDEX gs_asp_sampletime_index;
DROP INDEX pg_partition_indextblid_index;
DROP INDEX streaming_cont_query_schema_change_index;
DROP INDEX pg_hashbucket_bid_index;
DROP INDEX gs_recyclebin_dbid_spcid_rcycsn_index;
DROP INDEX pg_depend_reference_index;
DROP INDEX pg_class_tblspc_relfilenode_index;
DROP INDEX pg_inherits_parent_index;
DROP INDEX gs_recyclebin_dbid_nsp_oriname_index;
DROP INDEX pg_shdepend_depender_index;
DROP INDEX streaming_cont_query_matrelid_index;
DROP INDEX gs_txn_snapshot_time_csn_index;
DROP INDEX statement_history_time_idx;
DROP INDEX pg_index_indrelid_index;
######################################################################################### Redundant indexes #########################################################################################
DROP INDEX pg_catalog.gs_db_privilege_roleid_index;
DROP INDEX pg_catalog.pg_partition_indextblid_index;
DROP INDEX pg_catalog.pg_proc_proname_args_nsp_index;
DROP INDEX pg_catalog.streaming_cont_query_matrelid_index;

结果显示系统自带pg_catalog下需要创建这么多索引。其实作为rdbms的基表，本身数据量不是很大，没必要创建。但如果是生产中的业务数据库，创建前需要与业务人员沟通，并且根据数据量决定是否创建。对于小表，很多情况下没有必要。

四、总结

1、索引推荐功能与workload级别索引推荐功能是非常好的功能，可以帮助dba解决很多sql优化方面的问题。
2、虚拟索引功能是个很不错的功能。对sql优化很有帮助。
3、建议能够将该功能强化到sql_tunning_adviser，这样可以解决dba很多麻烦。


文章转载自公众号：openGauss

分类
数据库
标签
openGauss
已于2023-10-30 11:32:45修改
赞
收藏
回复
举报
回复
    相关推荐
    heatdog
    这个用户很懒，还没有个人简介
    帖子
    视频
    声望
    粉丝
    最近发布
    热门推荐
    #打开鸿蒙知识宝盒# 智问智答大师赛 30回复
    「Mac畅玩鸿蒙与硬件2」鸿蒙开发环境配置篇2 - 在 Mac 上安装 DevEco Studio 3回复
    ArkData：首选项——让数据在设备中安全的流动 2回复
    HarmonyOS应用数据库调试工具 HarmonyOS Debug Database (debug-db) 2回复
    【Py4OH系列教程】鸿蒙Python框架的前世今生 0回复
    相关问题
    HarmonyOS 学习途径推荐 2回答
    列表滑动鸿蒙推荐ux设计 1回答
    HarmonyOS 类似onactivity result这个功能 在Harmony OS中推荐怎么实现? 1回答
    开发板推荐或购买链接 2回答
    瀑布流场景的推荐实现方案 1回答
    社区精华内容

    目录