首页 > 代码库 > Index statistics collected bug

Index statistics collected bug

2024-07-28 03:21:07   220人阅读

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-

Index statistics collected bug


联系
我们