Indexstatisticscollectedbug

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个问题没有任何改进，貌&＃20284;默认情况就是这样。

[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&＃39;t have &＃39;DISTRIBUTED BY&＃39; clause -- Using column named &＃39;id&＃39; as the Greenplum Database data distribution key for this table.
HINT: The &＃39;DISTRIBUTED BY&＃39; 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)||&＃39;-asfd&＃39;;
INSERT 0 10000
gtliOns=# create index idxtestid on test(id);
CREATE INDEX
gtliOns=# select relname,relpages,reltuples from pg_class where relname like &＃39;%test%&＃39;;
relname | relpages | reltuples
-----------+----------+-----------
test | 14 | 10000
idxtestid | 0 | 0
(2 rows)

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

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


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

gtliOns=# vacuum full test;
NOTICE: &＃39;VACUUM FULL&＃39; is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use &＃39;VACUUM&＃39; instead.
VACUUM
gtliOns=# select relname,relpages,reltuples from pg_class where relname like &＃39;%test%&＃39;;
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 &＃39;%test%&＃39;; 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: &＃39;VACUUM FULL&＃39; is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use &＃39;VACUUM&＃39; instead.
VACUUM
gtliOns=# select relname,relpages,reltuples from pg_class where relname like &＃39;%test%&＃39;;
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 56 | 20000
idxtestname | 92 | 20000
(3 rows)

gtliOns=# vacuum full test;
NOTICE: &＃39;VACUUM FULL&＃39; is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use &＃39;VACUUM&＃39; instead.
VACUUM
gtliOns=# select relname,relpages,reltuples from pg_class where relname like &＃39;%test%&＃39;;
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 &＃39;%test%&＃39;;
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 &＃39;%test%&＃39;;
relname | relpages | reltuples
-------------+----------+-----------
test | 14 | 10000
idxtestid | 12 | 10000
idxtestname | 14 | 10000
(3 rows)

-EOF-