Index statistics collected bug
2017-06-17 08:49 tlnshuju 阅读(243) 评论(0) 编辑 收藏 举报
SQL运行引擎会从pg_stats、pg_class等相关系统字典表、视图获取生成最佳运行计划的数据,假设相关字典视图的数据不准确就没有办法生成良好的运行计划。
发现下面Bug一枚。
-EOF-
发现下面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-