暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第19天 | openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint

openGauss每日一练第19天 | openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint

原创 lxs_data 2021-12-19
865

ANALYZE | ANALYSE

用于收集与数据库中普通表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据，以确定最有效的执行计划。

如果没有指定参数，ANALYZE会分析当前数据库中的每个表和分区表。同时也可以通过指定table_name、column和partition_name参数把分析限定在特定的表、列或分区表中。

ANALYZE|ANALYSE VERIFY用于检测数据库中普通表（行存表、列存表）的数据文件是否损坏。

语法:

  • 收集表的统计信息。

    { ANALYZE | ANALYSE } [ VERBOSE ]
    [ table_name [ ( column_name [, ...] ) ] ];

  • 收集分区表的统计信息。

    { ANALYZE | ANALYSE } [ VERBOSE ]
    [ table_name [ ( column_name [, ...] ) ] ]
    PARTITION ( patrition_name ) ;

EXPLAIN

显示SQL语句的执行计划。

执行计划将显示SQL语句所引用的表会采用什么样的扫描方式，如：简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的JOIN算法。

执行计划的最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长的时间。

若指定了ANALYZE选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内时间总开销（毫秒为单位）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。

语法:

  • 显示SQL语句的执行计划，支持多种选项，对选项顺序无要求。

    EXPLAIN [ (  option  [, ...] )  ] statement;

    其中选项option子句的语法为。

    ANALYZE [ boolean ] |
    ANALYSE [ boolean ] |
    VERBOSE [ boolean ] |
    COSTS [ boolean ] |
    CPU [ boolean ] |
    DETAIL [ boolean ] |(不可用)
    NODES [ boolean ] |(不可用)
    NUM_NODES [ boolean ] |(不可用)
    BUFFERS [ boolean ] |
    TIMING [ boolean ] |
    PLAN [ boolean ] |
    FORMAT { TEXT | XML | JSON | YAML }

  • 显示SQL语句的执行计划，且要按顺序给出选项。

    EXPLAIN  { [  { ANALYZE  | ANALYSE  }  ] [ VERBOSE  ]  | PERFORMANCE  } statement;

EXPLAIN PLAN

通过EXPLAIN PLAN命令可以将查询执行的计划信息存储于PLAN_TABLE表中。与EXPLAIN命令不同的是，EXPLAIN PLAN仅将计划信息进行存储，而不会打印到屏幕。

语法:

EXPLAIN PLAN [ SET STATEMENT_ID = string ] FOR statement ;


openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint 操作练习

创建分区表，并用generate_series(1,N)函数对表插入数据

创建分区表

omm=# create table lxs_data
omm-# (
omm(# lxs1 int,
omm(# lxs2 CHAR(2))
omm-# partition by range (lxs1)
omm-# (
omm(# partition lxs_data_p1 values less than (10),
omm(# partition lxs_data_p2 values less than (20),
omm(# partition lxs_data_p3 values less than (30),
omm(# partition lxs_data_p4 values less than (40),
omm(# partition lxs_data_p5 values less than (50));
CREATE TABLE

用generate_series(1,N)函数对表插入数据

omm=# insert into lxs_data values(generate_series(1, 20));
INSERT 0 20
omm=# insert into lxs_data values(generate_series(1, 10));
INSERT 0 10
omm=# insert into lxs_data values(generate_series(1, 30));
INSERT 0 30
omm=# insert into lxs_data values(generate_series(1, 40));
INSERT 0 40

收集表统计信息

查看系统表中表的统计信息

omm=# select relname, relpages, reltuples from pg_class where relname = 'lxs_data';
relname | relpages | reltuples
----------+----------+-----------
lxs_data | 0 | 0
(1 row)

使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息

omm=# analyze VERBOSE lxs_data;
INFO: analyzing "public.lxs_data"(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 36 live rows and 9 dead rows; 36 rows in sample, 36 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 31 live rows and 10 dead rows; 31 rows in sample, 31 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 21 live rows and 10 dead rows; 21 rows in sample, 21 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 11 live rows and 10 dead rows; 11 rows in sample, 11 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 1 live rows and 10 dead rows; 1 rows in sample, 1 estimated total rows(gaussdb pid=1)
ANALYZE

查看系统表中表的统计信息

omm=# select relname, relpages, reltuples from pg_class where relname = 'lxs_data';
relname | relpages | reltuples
----------+----------+-----------
lxs_data | 5 | 100
(1 row)

显示简单查询的执行计划；建立索引并显示有索引条件的执行计划


omm=# SET explain_perf_mode=normal;
SET
omm=# EXPLAIN SELECT * FROM lxs_data;
QUERY PLAN
-----------------------------------------------------------------------------
Partition Iterator (cost=0.00..6.00 rows=100 width=16)
Iterations: 5
-> Partitioned Seq Scan on lxs_data (cost=0.00..6.00 rows=100 width=16)
Selected Partitions: 1..5
(4 rows)

建立索引并显示有索引条件的执行计划

omm=# create index lxs_data_idx on lxs_data(lxs1);
CREATE INDEX
omm=# EXPLAIN SELECT * FROM lxs_data WHERE lxs1<30;
QUERY PLAN
----------------------------------------------------------------------------
Partition Iterator (cost=0.00..4.25 rows=88 width=16)
Iterations: 3
-> Partitioned Seq Scan on lxs_data (cost=0.00..4.25 rows=88 width=16)
Filter: (lxs1 < 30)
Selected Partitions: 1..3
(5 rows)

更新表数据，并做垃圾收集

VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间

omm=# update lxs_data set lxs1 = lxs1 + 2 where lxs1 <40;
UPDATE 99

omm=# VACUUM (VERBOSE, ANALYZE) lxs_data;

INFO: vacuuming "public.lxs_data"(gaussdb pid=1)
INFO: scanned index "lxs_data_idx" to remove 9 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO: "lxs_data": found 9 removable, 64 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 36 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "lxs_data_idx" now contains 64 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: vacuuming "public.lxs_data"(gaussdb pid=1)
INFO: scanned index "lxs_data_idx" to remove 10 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "lxs_data_idx" now contains 64 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "lxs_data": found 10 removable, 64 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 31 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "public.lxs_data"(gaussdb pid=1)
INFO: scanned index "lxs_data_idx" to remove 10 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "lxs_data_idx" now contains 44 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "lxs_data": found 10 removable, 44 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 21 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "public.lxs_data"(gaussdb pid=1)
INFO: scanned index "lxs_data_idx" to remove 10 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.

DETAIL: 11 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "lxs_data_idx" now contains 24 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "lxs_data": found 10 removable, 24 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)INFO: vacuuming "public.lxs_data"(gaussdb pid=1)
INFO: scanned index "lxs_data_idx" to remove 10 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "lxs_data_idx" now contains 3 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "lxs_data": found 10 removable, 3 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "lxs_data_idx" to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.lxs_data"(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 28 live rows and 36 dead rows; 28 rows in sample, 28 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 33 live rows and 31 dead rows; 33 rows in sample, 33 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 13 live rows and 11 dead rows; 13 rows in sample, 13 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "lxs_data": scanned 1 of 1 pages, containing 23 live rows and 21 dead rows; 23 rows in sample, 23 estimated total rows(gaussdb pid=1)
VACUUM

omm=# CHECKPOINT;
CHECKPOINT


进行事务检查点：

检查点（CHECKPOINT）是一个事务日志中的点，所有数据文件都在该点被更新以反映日志中的信息，所有数据文件都将被刷新到磁盘

清理数据

omm=# drop table lxs_data;
DROP TABLE


持续打卡中。





opengauss墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论