普通表和分区表在线重建索引

功能描述

支持在线rebuild普通行存表索引和行存一级分区表索引，其中行存一级分区表索引包括本地分区索引和全局索引。

注意事项

• 目前仅支持并行重建普通行存表和行存一级分区表。
• 支持行级一级分区表上local和global类型的主表索引重建。
• 支持行级一级分区表上local类型索引在某一特定分区上的分区索引的重建。
• 不支持行级一级分区表上global类型索引在某一特定分区上的重建。

语法格式

ALTER INDEX index_name
REBUILD [ PARTITION partition_name ] CONCURRENTLY;

REINDEX INDEX CONCURRENTLY
index_name [ PARTITION partition_name ];

参数说明

• index_name

  自定义的索引名称。

• CONCURRENTLY

  并行的含义是不会阻塞DML和DQL语句。

• partiotion_name

  分区名称。

示例

1、创建测试表和索引，并插入数据。

CREATE TABLE t_rebuild_row(id int,name varchar(20));
INSERT INTO t_rebuild_row values(1,'liuyi');
INSERT INTO t_rebuild_row values(2,'chener');
INSERT INTO t_rebuild_row values(3,'zhangsan');
INSERT INTO t_rebuild_row values(4,'lisi');
INSERT INTO t_rebuild_row values(5,'wangwu');
INSERT INTO t_rebuild_row values(6,'zhaoliu');
INSERT INTO t_rebuild_row values(7,'sunqi');
INSERT INTO t_rebuild_row values(8,'zhouba');
INSERT INTO t_rebuild_row values(9,'wujiu');
CREATE INDEX idx_row ON t_rebuild_row (id);

2、查看索引是否创建成功。

\d t_rebuild_row 

返回结果如下，则表示索引创建成功：

   Table "public.t_rebuild_row"
 Column |    Type     | Modifiers
--------+-------------+-----------
 id     | integer     |
 name   | varchar(20) |
Indexes:
    "idx_row" btree (id) TABLESPACE pg_default

3、设置扫描开关。

set enable_seqscan =off;

4、检查执行计划是否为索引扫描。

explain select /*+tablescan(t_rebuild_row) */ count(*) from t_rebuild_row where id <6;
explain select /*+indexonlyscan(t_rebuild_row idx_row )*/ count(*) from t_rebuild_row where id <6;

两个执行计划分别为顺序扫描及索引扫描,返回结果为：

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=1000000000111.26..1000000000111.27 rows=1 width=8)
   ->  Seq Scan on t_rebuild_row  (cost=10000000000.00..1000000000111.25 rows=3 width=0)
         Filter: (id < 6)
(3 rows)

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=8.31..8.32 rows=1 width=8)
   ->  Index Only Scan using idx_row on t_rebuild_row  (cost=0.00..8.30 rows=3 width=0)
         Index Cond: (id < 6)
(3 rows)

5、打开两个会话，会话1不断执行DML和DQL操作。

do language plpgsql $$    
declare    
begin    
  for i in 5..100000 loop    
    INSERT INTO t_rebuild_row values(10,'zhengshi');
     DELETE FROM t_rebuild_row WHERE id=10;
     perform name FROM t_rebuild_row WHERE id<6 order by name;
    commit;    
  end loop;    
end;    
$$;   

6、切换至会话2，重建索引。

ALTER  INDEX idx_row REBUILD concurrently;

或

REINDEX INDEX CONCURRENTLY idx_row;

7、会话2中执行如下命令，检查索引是否创建成功(会话1执行过程中，会话2重建索引成功，且会话1不报错)。

explain select /*+tablescan(t_rebuild_row) */ count(*) from t_rebuild_row where id <6;
explain select /*+indexonlyscan(t_rebuild_row idx_row )*/ count(*) from t_rebuild_row where id <6;

两个执行计划分别为顺序扫描及索引扫描,返回结果如下则表示索引创建成功：

                                QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=1390.03..1390.04 rows=1 width=8)
   ->  Seq Scan on t_rebuild_row  (cost=0.00..1332.40 rows=23051 width=0)
         Filter: (id < 6)
(3 rows)

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=2637.27..2637.28 rows=1 width=8)
   ->  Index Only Scan using idx_row on t_rebuild_row  (cost=0.00..2579.64 rows=23051 width=0)
         Index Cond: (id < 6)
(3 rows)

8、在会话2中执行如下命令，检查两者的结果是否相同。

select /*+tablescan(t_rebuild_row) */ count(*) from t_rebuild_row where id <6;
select /*+indexonlyscan(t_rebuild_row idx_row )*/ count(*) from t_rebuild_row where id <6;

返回结果相同，均为：

 count
-------
     5
(1 row)

9、还原扫描开关。

reset enable_seqscan ;