Index statistics collected bug, collected

Index statistics collected bug, collected
The SQL Execution engine obtains the data that generates the optimal execution plan from the dictionary tables and views of related systems, such as pg_stats and pg_class. If the data in the dictionary view is inaccurate, no good execution plan can be generated.
One of the following bugs was found.

0. After data is inserted, the newly created index will not automatically update the pg_class.relpages \ pg_class.reltuples information of the collected index;
1. for a table, after performing UPDATE \ DELETE and vacuum full (first) operations on the table, the information of the pg_class.relpages \ pg_class.reltuples fields is incorrect, the result is the data before the DDL execution;
2. After executing reindex index on an INDEX, the pg_class.relpages \ pg_class.reltuples information will be cleared;

1st problems are fixed in the new version. There are no improvements to 0th and 2 problems, which seems to be the case by default.

[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-

In SQL Server, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = what is ON

Pad_index: Specify the storage space for each page (node) in the middle of the index. The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. By default, given the key set on the intermediate page, SQL Server ensures that the available space on each index page can accommodate at least one of the largest rows allowed by the index. If the percentage specified for FILLFACTOR is not large enough to accommodate a row, SQL Server uses the allowed minimum internally to replace the percentage.
Ignore_dup_key = off: if one or more rows contain duplicate key values, the SQL Server database engine rejects all rows operated by statements. When it is set to ON, a non-repeated key value can be added only when the row containing the duplicate key value is rejected.
If SQL is installed, search for it in the help box.

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

Version problems.