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 gtlionspsql (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 TABLEgtlions=# insert into test select generate_series(1,10000),generate_series(1,10000)||'-asfd';INSERT 0 10000gtlions=# create index idxtestid on test(id);CREATE INDEXgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-----------+----------+-----------test | 14 | 10000idxtestid | 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.VACUUMgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-----------+----------+-----------test | 14 | 10000idxtestid | 12 | 10000(2 rows)gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-----------+----------+-----------test | 14 | 10000idxtestid | 12 | 10000(2 rows)gtlions=# update test set name='asdfasfdf';UPDATE 10000gtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 12 | 10000idxtestname | 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.VACUUMgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 36 | 20000idxtestname | 61 | 20000(3 rows)gtlions=# analyze testgtlions-# ;ANALYZEgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%'; relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 18 | 10000idxtestname | 32 | 10000(3 rows)gtlions=# delete from test where id<=10000;DELETE 10001gtlions=# vacuum full test;NOTICE: 'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.HINT: Use 'VACUUM' instead.VACUUMgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 56 | 20000idxtestname | 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.VACUUMgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 28 | 10000idxtestname | 46 | 10000(3 rows)gtlions=# reindex index idxtestid;REINDEXgtlions=# reindex index idxtestname;REINDEXgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 0 | 0idxtestname | 0 | 0(3 rows)gtlions=# analyze test;ANALYZEgtlions=# select relname,relpages,reltuples from pg_class where relname like '%test%';relname | relpages | reltuples-------------+----------+-----------test | 14 | 10000idxtestid | 12 | 10000idxtestname | 14 | 10000(3 rows)

-EOF-

SQL Server中PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON是什

pad_index：指定索引中間級中每個頁（節點）上保持開放的空間。PAD_INDEX 選項只有在指定了 FILLFACTOR 時才有用，因為 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。預設情況下，給定中間級頁上的鍵集，SQL Server 將確保每個索引頁上的可用空間至少可以容納一個索引允許的最大行。如果為 FILLFACTOR 指定的百分比不夠大，無法容納一行，SQL Server 將在內部使用允許的最小值替代該百分比。
ignore_dup_key=off：如果有一行或多行含有重複索引值,則 SQL Server 資料庫引擎 將拒絕語句操作的所有行。當設定為 ON 時,只有包含重複索引值的行被拒絕後,才能添加非重複索引值。
如果安裝了SQL ，你在協助裡搜尋下就可以找到
 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, A

版本問題.