Index statistics collected bug，collected

Index statistics collected bug，collected

SQL执行引擎会从pg_stats、pg_class等相关系统字典表、视图获取生成最佳执行计划的数据，如果相关字典视图的数据不准确就没有办法生成良好的执行计划。
发现以下Bug一枚。

0. 插入数据之后，新创建的索引不会自动更新收集索引的pg_class.relpages\pg_class.reltuples信息；
1. 对一个表，当执行UPDATE\DELETE之后，对表执行VACUUM FULL(首次)操作之后，pg_class.relpages\pg_class.reltuples两个字段的信息是不正确的，得到的结果为执行DDL之前的数据；
2. 对一个索引执行REINDEX INDEX之后，pg_class.relpages\pg_class.reltuples信息会被清空；

第1个问题在新版本得到修复；对于第0、2个问题没有任何改进，貌似默认情况就是这样。

[gpadmin@wx60 ~]$ psql gtlions
psql (8.2.15)
Type "help" for help.

gtlions=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.15 (Greenplum Database 4.2.7.2 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 25 2014 18:05:04
(1 row)
gtlions=# create table test(id int,name varchar(200));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gtlions=# insert into test select generate_series(1,10000),generate_series(1,10000)||'-asfd';
INSERT 0 10000
gtlions=# create index idxtestid on test(id);
CREATE INDEX
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-----------+----------+-----------
test | 14 | 10000
idxtestid | 0 | 0
(2 rows)

gtlions=# vacuum full test;
NOTICE: 'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use 'VACUUM' instead.
VACUUM
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-----------+----------+-----------
test | 14 | 10000
idxtestid | 12 | 10000
(2 rows)

gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-----------+----------+-----------
test | 14 | 10000
idxtestid | 12 | 10000
(2 rows)


gtlions=# update test set name='asdfasfdf';
UPDATE 10000
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 12 | 10000
idxtestname | 14 | 10000
(3 rows)

gtlions=# vacuum full test;
NOTICE: 'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use 'VACUUM' instead.
VACUUM
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 36 | 20000
idxtestname | 61 | 20000
(3 rows)

gtlions=# analyze test
gtlions-# ;
ANALYZE
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%'; relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 18 | 10000
idxtestname | 32 | 10000
(3 rows)

gtlions=# delete from test where id<=10000;
DELETE 10001
gtlions=# vacuum full test;
NOTICE: 'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use 'VACUUM' instead.
VACUUM
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 56 | 20000
idxtestname | 92 | 20000
(3 rows)

gtlions=# vacuum full test;
NOTICE: 'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use 'VACUUM' instead.
VACUUM
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 28 | 10000
idxtestname | 46 | 10000
(3 rows)


gtlions=# reindex index idxtestid;
REINDEX
gtlions=# reindex index idxtestname;
REINDEX
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 0 | 0
idxtestname | 0 | 0
(3 rows)

gtlions=# analyze test;
ANALYZE
gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 12 | 10000
idxtestname | 14 | 10000
(3 rows)

-EOF-

pad_index：指定索引中间级中每个页（节点）上保持开放的空间。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。默认情况下，给定中间级页上的键集，SQL Server 将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果为 FILLFACTOR 指定的百分比不够大，无法容纳一行，SQL Server 将在内部使用允许的最小值替代该百分比。
ignore_dup_key=off：如果有一行或多行含有重复键值,则 SQL Server 数据库引擎 将拒绝语句操作的所有行。当设置为 ON 时,只有包含重复键值的行被拒绝后,才能添加非重复键值。
如果安装了SQL ，你在帮助里搜索下就可以找到
 

版本问题.