openGauss每日一练第9天 | 学习心得体会

1.创建表products, 分别为表创建一个unique索引1，指定b-tree索引2和表达式索引3

create table products (id int, name char(30), price int, sale_date date);
create unique index products_id_idx1 on products(id);
create index products_id_idx2  on products using btree(id);
create index products_sale_date_idx3 on products(substr(sale_date,1,4));

2.设置索引1不可用，修改索引2的表空间，重命名索引3

alter index  products_id_idx1  unusable;
create tablespace example relative location 'tablespace/example';
alter index products_id_idx2 set tablespace example; 
alter index products_sale_date_idx3 rename to products_sale_date_idx3_1;

3.重建索引2和products的所有索引

alter index products_id_idx2  rebuild;
reindex table products ;

4.使用\d+和系统视图pg_indexes查看索引信息

omm=# \d+ products 
                            Table "public.products"
  Column   |     Type      | Modifiers | Storage  | Stats target | Description 
-----------+---------------+-----------+----------+--------------+-------------
 id        | integer       |           | plain    |              | 
 name      | character(30) |           | extended |              | 
 price     | integer       |           | plain    |              | 
 sale_date | date          |           | plain    |              | 
Indexes:
    "products_id_idx1" UNIQUE, btree (id) TABLESPACE pg_default
    "products_id_idx2" btree (id) TABLESPACE example, tablespace "example"
    "products_sale_date_idx3_1" btree (substr(sale_date::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no


omm=# select * from pg_indexes where tablename='products';
 schemaname | tablename |         indexname         | tablespace |                                                        indexdef                                                        
------------+-----------+---------------------------+------------+------------------------------------------------------------------------------------------------------------------------
 public     | products  | products_id_idx1          |            | CREATE UNIQUE INDEX products_id_idx1 ON products USING btree (id) TABLESPACE pg_default
 public     | products  | products_id_idx2          | example    | CREATE INDEX products_id_idx2 ON products USING btree (id) TABLESPACE example
 public     | products  | products_sale_date_idx3_1 |            | CREATE INDEX products_sale_date_idx3_1 ON products USING btree (substr((sale_date)::text, 1, 4)) TABLESPACE pg_default

5.删除索引、表和表空间

drop index products_id_idx1;
drop index products_id_idx2;
drop index products_sale_date_idx3_1;
drop table products;
drop tablespace example;