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

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

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

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)
1.
2.
3.
4.
5.
6.

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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
1.
2.
3.
4.
5.
6.
7.

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

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

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

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

PanWeiDB=# select * from hypopg_estimate_size(57591);
hypopg_estimate_size
----------------------
236503040
(1 row)
1.
2.
3.
4.
5.

PanWeiDB=# select * from hypopg_drop_index(57591);
hypopg_drop_index
-------------------
t
(1 row)
1.
2.
3.
4.
5.

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

(1 row)
1.
2.
3.
4.
5.

[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
1.
2.
3.

[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:
1.
2.
3.

########################################################################################## 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;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.