• Index statistics collected bug


    SQL运行引擎会从pg_stats、pg_class等相关系统字典表、视图获取生成最佳运行计划的数据,假设相关字典视图的数据不准确就没有办法生成良好的运行计划。
    发现下面Bug一枚。



    0. 插入数据之后,新创建的索引不会自己主动更新收集索引的pg_class.relpagespg_class.reltuples信息。
    1. 对一个表,当运行UPDATEDELETE之后,对表运行VACUUM FULL(首次)操作之后,pg_class.relpagespg_class.reltuples两个字段的信息是不对的。得到的结果为运行DDL之前的数据;
    2. 对一个索引运行REINDEX INDEX之后,pg_class.relpagespg_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-
  • 相关阅读:
    OD: Memory Attach Technology
    Chrome: Shockwave Flash isn't responding
    OD: Memory Attach Technology
    OD: Heap Exploit : DWORD Shooting & Opcode Injecting
    OD: Heap in Windows 2K & XP SP1
    OD: Writing Small Shellcode
    OD: Shellcode Encoding
    Set Windows IP by Batch
    OD: Universal Shellcode
    XenServer 使用笔记
  • 原文地址:https://www.cnblogs.com/tlnshuju/p/7039290.html
Copyright © 2020-2023  润新知