openGauss每日一练第17天 - 学习心得体会

课后作业

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

omm=# create table test(id int,name varchar(10));
CREATE TABLE
omm=# create index idx_test_id on test(id);
CREATE INDEX
omm=# \di
                   List of relations
 Schema |    Name     | Type  | Owner | Table | Storage 
--------+-------------+-------+-------+-------+---------
 public | idx_test_id | index | omm   | test  | 
(1 row)

omm=#

2.通过hint使用索引

omm=# insert into test values (1, 'xiaoming');
INSERT 0 1
omm=# insert into test values (2, 'xiaohong');
INSERT 0 1
omm=# insert into test values (3, 'xiaofang');
INSERT 0 1
omm=# insert into test values (4, 'xiaogang');
INSERT 0 1
omm=# explain select /*+ indexscan(test idx_test_id ) */ * from test where id=1;
(3 rows)

omm=#                                 QUERY PLAN                                
--------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_test_id on test  (cost=0.00..24.36 rows=6 width=42)
   Index Cond: (id = 1)

3.rename索引

omm=# alter index idx_test_id rename to idx_test_id_new;
ALTER INDEX
omm=# \di
                       List of relations
 Schema |      Name       | Type  | Owner |  Table   | Storage 
--------+-----------------+-------+-------+----------+---------
 public | idx_test_id_new | index | omm   | test     |

4.重建索引

omm=# alter index idx_test_id_new rebuild online;
ERROR:  syntax error at or near "online"
LINE 1: alter index idx_test_id_new rebuild online;
                                            ^
omm=# alter index idx_test_id_new rebuild;
REINDEX
omm=# reindex index idx_test_id_new;
REINDEX
omm=# reindex table test;
REINDEX

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

omm=# create tablespace myindex_ts relative location 'tablespace/myindex_ts1';
CREATE TABLESPACE
omm=# 
omm=# alter index idx_test_id_new set tablespace myindex_ts;
omm=# ALTER INDEX

omm=#  select * from pg_indexes where tablename = 'test';
 schemaname | tablename |    indexname    | tablespace |                                  indexdef                                   
------------+-----------+-----------------+------------+-----------------------------------------------------------------------------
 public     | test      | idx_test_id_new | myindex_ts | CREATE INDEX idx_test_id_new ON test USING btree (id) TABLESPACE myindex_ts
(1 row)

6.删除索引

omm=# drop index idx_test_id_new;
DROP INDEX