回复
【我和openGauss的故事】openGauss索引推荐功能测试
老老老JR老北
发布于 2023-9-6 15:18
浏览
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
分类
标签
已于2023-9-6 15:18:52修改
赞
收藏
回复
相关推荐
