openGauss每日一练第17天 | openGauss逻辑结构：索引管理

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

## 创建表omm=# create table customer (
omm(# id serial,
omm(# name varchar(10),
omm(# address varchar(10), 
omm(# age integer default 20,
omm(# primary key (id)
omm(# );
NOTICE:  CREATE TABLE will create implicit sequence "customer_id_seq" for serial column "customer.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
CREATE TABLE## 在表的id上创建索引omm=# create index idx_cus on customer (id);
CREATE INDEX

omm=# \di idx_cus 
                   List of relations
 Schema |  Name   | Type  | Owner |  Table   | Storage 
--------+---------+-------+-------+----------+---------
 public | idx_cus | index | omm   | customer | 
(1 row)

2.通过hint使用索引

omm=# insert into customer (name,address,age) values ('li','wf',20);
INSERT 0 1
omm=# insert into customer (name,address,age) values 
omm-# ('liu','bj',21),
omm-# ('wang','tj',22),
omm-# ('li','sh',23);
INSERT 0 3omm=# explain select /*+ indexscan(customer idx_cus) */
omm=# * from customer where id < 3;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_cus on customer  (cost=0.00..48.43 rows=239 width=84)
   Index Cond: (id < 3)
(3 rows)

3.rename索引

omm=# alter index idx_cus rename to idx_cus_new;
ALTER INDEX

4.重建索引

## 重建一个单独索引
omm=# alter index idx_cus_new rebuild;
REINDEX 
omm=# reindex index idx_cus_new ;
REINDEX## 重建所有索引omm=# reindex table customer ;
REINDEX

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

omm=# create tablespace tbs relative location 'tablespace/tbs';
CREATE TABLESPACEomm=# alter index idx_cus_new set tablespace tbs ;   
ALTER INDEX

omm=# select * from pg_indexes where indexname = 'idx_cus_new';
 schemaname | tablename |  indexname  | tablespace |                               indexdef                  
             
------------+-----------+-------------+------------+---------------------------------------------------------
-------------
 public     | customer  | idx_cus_new | tbs        | CREATE INDEX idx_cus_new ON customer USING btree (id) TA
BLESPACE tbs
(1 row)

6.删除索引

omm=# drop index idx_cus_new ;
DROP INDEX