合 PG或GreenPlum统计信息和执行计划总结
Tags： PGGreenPlumPostgreSQL统计信息执行计划vacuum总结EXPLAINn_distinctpg_stat_all_indexespg_stat_all_tablespg_stats
👉 本文共约4480个字，系统预计阅读时间或需17分钟。
SQL总结
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | CREATE TABLE big(user_id int4,user_name text,ctime timestamp(6) without time zone default clock_timestamp() ); INSERT INTO big(user_id,user_name) SELECT n ,n || '_data' FROM generate_series(1,30000000) n; select pg_size_pretty (pg_total_relation_size('big')); select pg_size_pretty (pg_relation_size('big')); select pg_size_pretty (pg_table_size('big')); select pg_size_pretty (pg_indexes_size('big')); show max_worker_processes ; show max_parallel_workers ; set max_parallel_maintenance_workers =8; CREATE INDEX idx_big_ctime ON big USING BTREE(ctime); CREATE INDEX idx_big_user_id ON big USING BTREE(user_id); set max_parallel_workers_per_gather=8; explain select count(1) from big; ALTER TABLE big SET (parallel_workers=8); -- 收集统计信息 ANALYZE big; vacuum (verbose,ANALYZE) big; -- 查看执行计划 explain SELECT * from big where user_id=1; explain SELECT * from big where ctime>to_timestamp('2023-06-01','yyyy-MM-dd'); EXPLAIN (ANALYZE on, TIMING on, VERBOSE on, BUFFERS on, COSTS on) SELECT * from big where user_id=1; -- PG、GP的表统计信息 select * from pg_stat_all_tables where schemaname='public' and relname = 'big'; -- 只GP select * FROM pg_stat_operations WHERE objname='big'; -- PG、GP的列统计信息 select a.* from pg_stats a where schemaname='public' and a.tablename='big' order by attname; -- 表使用情况统计 select a.* from pg_stat_all_tables a where schemaname='public' and a.relname='big'; -- 索引使用情况统计 select a.* from pg_stat_all_indexes a where schemaname='public' and a.relname='big'; |
视图pg_stats
视图pg_stats提供对存储在pg_statistic目录中信息的访问
| 名称 | 类型 | 引用 | 描述 |
|---|---|---|---|
schemaname | name | pg_namespace.nspname | 包含表的模式名 |
tablename | name | pg_class.relname | 表名 |
attname | name | pg_attribute.attname | 被此行描述的列名 |
inherited | bool | 如果为真，表示此行包括继承子列，不仅仅是指定表中的值 | |
null_frac | real | 列项中为空的比例 | |
avg_width | integer | 列项的平均字节宽度 | |
n_distinct | real | 如果大于零，表示列中可区分值的估计个数。 如果小于零，是可区分值个数除以行数的负值（当 ANALYZE认为可区分值的数量会随着表增长而增加时采用负值的形式，而如果认为列具有固定数量的可选值时采用正值的形式）。例如，-1表示一个唯一列，即其中可区分值的个数等于行数。 | |
most_common_vals | anyarray | 列中最常用值的一个列表（如果没有任何一个值看起来比其他值更常用，此列为空） | |
most_common_freqs | real[] | 最常用值的频率列表，即每一个常用值的出现次数除以总行数（如果most_common_vals为空，则此列为空） | |
histogram_bounds | anyarray | 将列值划分成大小接近的组的值列表。如果存在most_common_vals，其中的值会被直方图计算所忽略（如果列类型没有一个<操作符或者most_common_vals等于整个值集合，则此列为空） | |
correlation | real | 物理行顺序和列值逻辑顺序之间的统计关联。其范围从-1到+1。当值接近-1或+1时，在列上的一个索引扫描被认为比值接近0时的代价更低，因为这种情况减少了对磁盘的随机访问（如果列数据类型不具有一个<操作符，则此列为空） | |
most_common_elems | anyarray | 在列值中，最经常出现的非空元素列表（对标度类型为空） | |
most_common_elem_freqs | real[] | 最常用元素值的频度列表，即含有至少一个给定值实例的行的分数。在每个元素的频度之后有二至三个附加值，它们是每个元素频度的最小和最大值，以及可选的空元素的频度（如果most_common_elems为空，则此列为空） | |
elem_count_histogram | real[] | 在列值中可区分非空元素值计数的一个直方图，后面跟随可区分非空元素的平均数（对于标度类型为空） |
对于
n_distinct列，若为正数，则直接表示不同值的个数；
若为负值，则需要绝对值乘以行数才表示不同值的个数，-1表示不同值的个数等于行数，所以，约接近于-1，则不同值的个数越大。
关于“可区分值”，它是指在某个数据集中，某个属性的取值个数。例如，如果一个属性的取值只有 0 和 1，那么这个属性的可区分值就是 2。
科学计数法：
4.1E+11
410 000 000 0003.17804e+06 表示的是3.17804乘以10的6次方，即：
3.17804 * 10^6 = 3,178,040本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
