KingbaseES V8R6 vacuum index_cleanup 选项

描述：

由于索引页的复用不像HEAP TABLE的PAGE复用机制那么简单只要有空闲空间就可以插入。索引页的空闲空间被复用，必须是PAGE的边界内的值才允许插入。

因此索引一旦膨胀，很难收缩，常用的方式是重建。垃圾回收对索引来说几乎起不到降低膨胀的作用（大面积删除KEY值区间数据除外，这种情况索引PAGE被直接释放（虽然占用空间，但是这些被回收的PAGE已经不在TREE中，扫描不会有IO放大））。索引膨胀还会造成索引分裂，导致检索索引时消耗大量I/O资源。

在VACUUM（垃圾回收）时，HEAP PAGE和INDEX PAGE都会被执行垃圾回收，而实际上如果你的索引已经膨胀并且打算重建索引，没有必要对索引进行垃圾回收。

KingbaseESV8R6 VACUUM引入了一个新的选项INDEX_CLEANUP，可以跳过索引的垃圾回收。

当使用跳过索引回收时， heap 里面的dead tuple行的内容会被回收， 而line point(itemid)会被保留。 例如(0,10)是需要回收的垃圾， item point不回收， row内容回收。 所以即使索引指向了(0,10)这条记录，最后也不会有问题， 不会跳到其他row因为这个itemid的page offset已经设置为空。

举例

test=# select version();
                                                       version
----------------------------------------------------------------------------------------------------------------------
 KingbaseES V008R006C005B0041 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)

test=# \h vacuum
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

1、正常vacuum操作，vacuum索引

test=# create table test (id int primary key, info text , crt_time timestamp);
CREATE TABLE
test=# insert into test select generate_series(1,1000000), md5(random()::text), clock_timestamp();
INSERT 0 1000000

test=#  vacuum verbose test;
INFO:  vacuuming "public.test"
**INFO:  index "test_pkey" now contains 1000000 row versions in 2760 pages**
**DETAIL:  0 index row versions were removed.**
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "test": found 0 removable, 62 nonremovable row versions in 1 out of 9347 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 902
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "pg_toast.pg_toast_16389"
INFO:  index "pg_toast_16389_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_16389": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 902
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

2、INDEX_CLEANUP false禁止vacuum索引(免去无用功)

test=# update test set info='test' where id in (select (random()*1000)::int from generate_series(1,100));  
UPDATE 95  
test=# vacuum (verbose, INDEX_CLEANUP false) test;  
INFO:  vacuuming "public.test"
INFO:  "test": found 95 removable, 1155 nonremovable row versions in 12 out of 9347 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 903
There were 19 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "pg_toast.pg_toast_16389"
INFO:  "pg_toast_16389": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 903
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

总结：遇到索引膨胀问题时，index_cleanup 跳过vacuum index，再使用reindex concurrently重建索引。