PG或GreenPlum统计信息和执行计划总结

由小麦苗 · 2023年6月2日

0 158 2

Tags： EXPLAIN GreenPlum PG PostgreSQL vacuum 总结执行计划统计信息

👉 本文共约4200个字，系统预计阅读时间或需16分钟。

本页目录 隐藏

SQL总结

视图pg_stats

视图pg_stat_all_tables

视图pg_stat_all_indexes

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';

-- 表使用情况统计 
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';

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';

-- 表使用情况统计

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[]`		在列值中可区分非空元素值计数的一个直方图，后面跟随可区分非空元素的平均数（对于标度类型为空）

视图pg_stat_all_tables

pg_stat_all_tables视图将为当前数据库中的每一个表（包括 TOAST 表）包含一行，该行显示与对该表的访问相关的统计信息。 pg_stat_user_tables和pg_stat_sys_tables视图包含相同的信息，但是被过滤得分别只显示用户和系统表。

表 27.23. pg_stat_all_tables 视图

列类型描述
`relid` `oid`表的OID
`schemaname` `name`该表所在的模式的名称
`relname` `name`这个表的名称
`seq_scan` `bigint`在此表上启动的顺序扫描数
`seq_tup_read` `bigint`连续扫描获取的实时行数
`idx_scan` `bigint`对这个表发起的索引扫描数
`idx_tup_fetch` `bigint`索引扫描获取的实时行数
`n_tup_ins` `bigint`插入的行数
`n_tup_upd` `bigint`更新的行数(包括HOT更新的行)
`n_tup_del` `bigint`删除的行数
`n_tup_hot_upd` `bigint`HOT更新的行数(即，不需要单独的索引更新)
`n_live_tup` `bigint`活的行的估计数量
`n_dead_tup` `bigint`僵死行的估计数量
`n_mod_since_analyze` `bigint`自上次分析此表以来修改的行的估计数量
`n_ins_since_vacuum` `bigint`自上次清空此表以来插入的行的估计数量
`last_vacuum` `timestamp with time zone`最后一次手动清理这个表(不包括`VACUUM FULL`)
`last_autovacuum` `timestamp with time zone`这个表最后一次被自动清理守护进程清理的时间
`last_analyze` `timestamp with time zone`上一次手动分析这个表
`last_autoanalyze` `timestamp with time zone`自动清理守护进程最后一次分析这个表
`vacuum_count` `bigint`这个表被手动清理的次数(`VACUUM FULL`不计数)
`autovacuum_count` `bigint`这个表被autovacuum守护进程清理的次数
`analyze_count` `bigint`手动分析这个表的次数
`autoanalyze_count` `bigint`这个表被autovacuum守护进程分析的次数

视图pg_stat_all_indexes

pg_stat_all_indexes视图将为当前数据库中的每个索引包含一行，该行显示关于对该索引访问的统计信息。pg_stat_user_indexes和pg_stat_sys_indexes视图包含相同的信息，但是被过滤得只分别显示用户和系统索引。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(2)

标签： EXPLAIN GreenPlum PG PostgreSQL vacuum 总结执行计划统计信息

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 9月
一	二	三	四	五	六	日
	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

AI (2)
ClickHouse (6)
DM达梦 (9)
Docker (46)
GBase (37)
Greenplum (109)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (5)
MySQL (211)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (11)
Oracle (635)
OS (383)
PostgreSQL (259)
Python (26)
Redis (21)
SQL Server (157)
SQLite (1)
StarRocks (21)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (10)
云 (10)
人大金仓KingBase (9)
其它 (83)
培训考证 (45)
大数据 (6)
存储 (8)
巡检监控 (47)
工具 (100)
数据迁移 (16)
架构篇 (12)
生活 (10)
电脑 (18)
网络 (94)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

PG或GreenPlum统计信息和执行计划总结

SQL总结

视图pg_stats

视图pg_stat_all_tables

视图pg_stat_all_indexes

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

PG或GreenPlum统计信息和执行计划总结

SQL总结

视图pg_stats

视图pg_stat_all_tables

视图pg_stat_all_indexes

相关文章

您可能还喜欢...

GreenPlum7单机安装

GreenPlum中的FTS故障检测原理

GreenPlum数据库的备份和恢复概述

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复