欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Indexstatisticscollectedbug

程序员文章站 2024-01-28 16:36:58
...

SQL执行引擎会从pg_stats、pg_class等相关系统字典表、视图获取生成最佳执行计划的数据,如果相关字典视图的数据不准确就没有办法生成良好的执行计划。 发现以下Bug一枚。 0. 插入数据之后,新创建的索引不会自动更新收集索引的pg_class.relpages\pg_class.re

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

-EOF-