数据库中表的统计信息保存在pg_statistic中，pg_stats是pg_statistic数据字典上的一个视图，可以方便我们看pg_statistic的内容。

现要通过数据字段获取数据库中所有的表和对应的schema名字返回给服务端，这个时候pg_stats视图就派上用场了！

但是遇到了一些表在pg_stats中没有信息，今天就一探究竟什么时候pg_stats才有表的统计信息

1、新建表看下数据字典信息

edw=# create table test.t1(id int4);

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

edw=# select * from pg_stats where tablename='t1';

------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

(0 rows)

edw=# select relname,relnamespace,relpages,reltuples from pg_class where relname='t1';

relname | relnamespace | relpages | reltuples

---------+--------------+----------+-----------

t1 | 79136 | 1 | 0

(1 row)

pg_stats没有表信息，但是pg_class中有信息

2、analyse收集统计信息

edw=# analyse test.t1;

ANALYZE

edw=# select * from pg_stats where tablename='t1';

------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

(0 rows)

pg_stats中仍然没有信息

3、插入数据再查看

edw=# insert into test.t1 values(1);

INSERT 0 1

edw=# select * from pg_stats where tablename='t1';

------------+--

---------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

(0 rows)

pg_stats中依旧没有信息

4、analyse再收集统计信息

edw=# analyse test.t1;

ANALYZE

edw=# select * from pg_stats where tablename='t1';

------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

test | t1 | id | 0 | 4 | -1 | {1} | {1} | |

(1 row)

pg_stats中有数据了

5、删除数据再收集统计信息

edw=# truncate table test.t1;

TRUNCATE TABLE

edw=# select * from pg_stats where tablename='t1';

------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

test | t1 | id | 0 | 4 | -1 | {1} | {1} | |

(1 row)

edw=# analyse test.t1;

ANALYZE

edw=# select * from pg_stats where tablename='t1';

------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

test | t1 | id | 0 | 4 | -1 | {1} | {1} | |

(1 row)

pg_stats有表信息

结论：新建的表需要插入数据并analyse之后pg_stats中才会有表的统计信息

ITPub博客

pg_stats没有表统计信息

最新文章