【我和 openGauss 的故事】openGauss 索引推荐功能测试

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)

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=# 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)

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

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)

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)

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)

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)

[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

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

########################################################################################## 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;