openGauss每日一练第17天|索引管理

1.创建表，在表中创建索引。

--为表mytest的mytestnum列创建一个索引
su - omm
gsql -r
drop table if exists mytest;
create table mytest(id serial primary key,mytestnum serial);
create index idx_mytest_mytestnum on mytest(mytestnum);
--查看索引
\di

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# drop table if exists mytest;
NOTICE:  table "mytest" does not exist, skipping
DROP TABLE
omm=# create table mytest(id serial primary key,mytestnum serial);
NOTICE:  CREATE TABLE will create implicit sequence "mytest_id_seq" for serial column "mytest.id"
NOTICE:  CREATE TABLE will create implicit sequence "mytest_mytestnum_seq" for serial column "mytest.mytestnum"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytest_pkey" for table "mytest"
CREATE TABLE
omm=# create index idx_mytest_mytestnum on mytest(mytestnum);
CREATE INDEX
omm=# \di

(2 rows)

omm=#                         List of relations
 Schema |         Name         | Type  | Owner | Table  | Storage 
--------+----------------------+-------+-------+--------+---------
 public | idx_mytest_mytestnum | index | omm   | mytest | 
 public | mytest_pkey          | index | omm   | mytest | 
omm=# 

2.通过hint使用索引。

--测试准备，创建表mycustomer，并插入数据
CREATE TABLE mycustomer
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16),
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
);
insert into mycustomer values
(1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'United States', -7.00, 'condo'),
(2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United States', -7.00, 'condo'),
(3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');
--创建索引
create index mycustomer_idx on  mycustomer(ca_address_sk);
--通过hint强制使用索引，查看执行计划
EXPLAIN SELECT /*+ indexscan(mycustomer mycustomer_idx ) */ * FROM mycustomer WHERE ca_address_sk<100;

omm=# CREATE TABLE mycustomer
omm-# (
omm(# ca_address_sk integer NOT NULL ,
omm(# ca_address_id character(16),
omm(# ca_street_number character(10) ,
omm(# ca_street_name character varying(60) ,
omm(# ca_street_type character(15) ,
omm(# ca_suite_number character(10) ,
omm(# ca_city character varying(60) ,
omm(# ca_county character varying(30) ,
omm(# omm(# ca_state character(2) ,
ca_zip character(10) ,
omm(# omm(# ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
omm(# ca_location_type character(20)
omm(# );
CREATE TABLE
omm=# insert into mycustomer values
omm-# (1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'United States', -7.00, 'condo'),
omm-# (2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United States', -7.00, 'condo'),
omm-# (3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');
INSERT 0 3
omm=# create index mycustomer_idx on  mycustomer(ca_address_sk);
CREATE INDEX
omm=# EXPLAIN SELECT /*+ indexscan(mycustomer mycustomer_idx ) */ * FROM mycustomer WHERE ca_address_sk<100;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 [Bypass]
 Index Scan using mycustomer_idx on mycustomer  (cost=0.00..8.27 rows=1 width=788)
   Index Cond: (ca_address_sk < 100)
(3 rows)

omm=# EXPLAIN SELECT * FROM mycustomer WHERE ca_address_sk<100;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on mycustomer  (cost=0.00..1.04 rows=1 width=788)
   Filter: (ca_address_sk < 100)
(2 rows)

omm=# 

3.rename索引。

ALTER INDEX idx_mytest_mytestnum RENAME TO idx_mytest_mytestnum_new;
\di

omm=# \di
                          List of relations
 Schema |         Name         | Type  | Owner |   Table    | Storage 
--------+----------------------+-------+-------+------------+---------
 public | idx_mytest_mytestnum | index | omm   | mytest     | 
 public | mycustomer_idx       | index | omm   | mycustomer | 
 public | mytest_pkey          | index | omm   | mytest     | 
(3 rows)

omm=# ALTER INDEX idx_mytest_mytestnum RENAME TO idx_mytest_mytestnum_new;
ALTER INDEX
omm=# \di
                            List of relations
 Schema |           Name           | Type  | Owner |   Table    | Storage 
--------+--------------------------+-------+-------+------------+---------
 public | idx_mytest_mytestnum_new | index | omm   | mytest     | 
 public | mycustomer_idx           | index | omm   | mycustomer | 
 public | mytest_pkey              | index | omm   | mytest     | 
(3 rows)

omm=# 

4.重建索引。

--重建一个单独索引
ALTER INDEX  idx_mytest_mytestnum_new REBUILD;
REINDEX INDEX  idx_mytest_mytestnum_new;
--重建所有索引
reindex table mytest;

omm=# ALTER INDEX  idx_mytest_mytestnum_new REBUILD;
omm=# REINDEX
REINDEX INDEX  idx_mytest_mytestnum_new;
omm=# REINDEX
omm=# reindex table mytest;
REINDEX

5.移动索引到其他表空间。

--创建表空间myindex_ts：
CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
--将索引idx_mytest_mytestnum_new移动到表空间myindex_ts：
ALTER INDEX idx_mytest_mytestnum_new SET TABLESPACE myindex_ts;
--查看索引所在的表空间
select * from pg_indexes where tablename = 'mytest';
--或
select * from pg_indexes where indexname = 'idx_mytest_mytestnum_new';

omm=# CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
CREATE TABLESPACE
omm=# ALTER INDEX idx_mytest_mytestnum_new SET TABLESPACE myindex_ts;
ALTER INDEX
omm=# select * from pg_indexes where tablename = 'mytest';
 schemaname | tablename |        indexname         | tablespace |                                           indexdef          
                                  
------------+-----------+--------------------------+------------+-------------------------------------------------------------
----------------------------------
 public     | mytest    | mytest_pkey              |            | CREATE UNIQUE INDEX mytest_pkey ON mytest USING btree (id) T
ABLESPACE pg_default
 public     | mytest    | idx_mytest_mytestnum_new | myindex_ts | CREATE INDEX idx_mytest_mytestnum_new ON mytest USING btree 
(mytestnum) TABLESPACE myindex_ts
(2 rows)

omm=# \x
Expanded display is on.
omm=# select * from pg_indexes where tablename = 'mytest';
-[ RECORD 1 ]---------------------------------------------------------------------------------------------
schemaname | public
tablename  | mytest
indexname  | mytest_pkey
tablespace | 
indexdef   | CREATE UNIQUE INDEX mytest_pkey ON mytest USING btree (id) TABLESPACE pg_default
-[ RECORD 2 ]---------------------------------------------------------------------------------------------
schemaname | public
tablename  | mytest
indexname  | idx_mytest_mytestnum_new
tablespace | myindex_ts
indexdef   | CREATE INDEX idx_mytest_mytestnum_new ON mytest USING btree (mytestnum) TABLESPACE myindex_ts

omm=# select * from pg_indexes where indexname = 'idx_mytest_mytestnum_new';
-[ RECORD 1 ]---------------------------------------------------------------------------------------------
schemaname | public
tablename  | mytest
indexname  | idx_mytest_mytestnum_new
tablespace | myindex_ts
indexdef   | CREATE INDEX idx_mytest_mytestnum_new ON mytest USING btree (mytestnum) TABLESPACE myindex_ts

omm=# 

6.删除索引。

--删除表mytest上的索引idx_mytest_mytestnum_new：
drop index idx_mytest_mytestnum_new;
\di

omm=# drop index idx_mytest_mytestnum_new;
DROP INDEX
omm=# \di
List of relations
-[ RECORD 1 ]-----------
Schema  | public
Name    | mycustomer_idx
Type    | index
Owner   | omm
Table   | mycustomer
Storage | 
-[ RECORD 2 ]-----------
Schema  | public
Name    | mytest_pkey
Type    | index
Owner   | omm
Table   | mytest
Storage | 

omm=# \x
Expanded display is off.
omm=# \di
                       List of relations
 Schema |      Name      | Type  | Owner |   Table    | Storage 
--------+----------------+-------+-------+------------+---------
 public | mycustomer_idx | index | omm   | mycustomer | 
 public | mytest_pkey    | index | omm   | mytest     | 
(2 rows)

omm=#