openGauss每日一练 第19天 执行计划

学习目标

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

EXPLAIN显示SQL语句的执行计划。执行计划将显示SQL语句所引用的表会采用什么样的扫描方式，如：简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的JOIN算法。执行计划的最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长的时间。若指定了ANALYZE选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内时间总开销（毫秒为单位）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

课后作业

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

-- 创建分区表
Create schema tpcds;
create table tpcds.t1
( id int,
  name CHAR(6)
)
partition by range (id)
(
        partition update_table_p0 values less than (50),
        partition update_table_p1 values less than (500),
        partition update_table_p2 values less than (1000)
);
-- 使用generate_series(1,N)函数对表插入数据
insert into tpcds.t1 values(generate_series(10, 200));
-- 查看数据
select * from tpcds.t1;

2.收集表统计信息

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

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

EXPLAIN SELECT * FROM tpcds.t1;
-- 建立索引并显示有索引条件的执行计划
create index customer_address_idx on tpcds.t1(id);
EXPLAIN SELECT * FROM tpcds.t1 WHERE id<100;

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

update tpcds.t1 set id = id + 1 where id <100;
VACUUM (VERBOSE, ANALYZE) tpcds.t1;

5.清理数据

drop table tpcds.t1;
drop schema tpcds cascade;
\dn

作业完整执行结果

omm=# create table tpcds.t1
omm-# omm(# ( id int,
)
omm-#   name CHAR(6)
omm(# partition by range (id)
omm-# (
omm(#         partition update_table_p0 values less than (50),
omm(#         partition update_table_p1 values less than (500),
omm(#         partition update_table_p2 values less than (1000)
omm(# );
CREATE TABLE
omm=# -- 使用generate_series(1,N)函数对表插入数据
omm=# insert into tpcds.t1 values(generate_series(10, 200));
INSERT 0 191
omm=# -- 查看数据
omm=# select * from tpcds.t1;
 id  | name 
-----+------
  10 | 
  11 | 
  12 | 
  13 | 
  14 | 
  15 | 
  16 | 
  17 | 
  18 | 
  19 | 
  20 | 
 ...... 
 191 | 
 192 | 
 193 | 
 194 | 
 195 | 
 196 | 
 197 | 
 198 | 
 199 | 
 200 | 
(191 rows)

omm=# 

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

omm=# EXPLAIN SELECT * FROM tpcds.t1;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..23.17 rows=1317 width=32)
   Iterations: 3
   ->  Partitioned Seq Scan on t1  (cost=0.00..23.17 rows=1317 width=32)
         Selected Partitions:  1..3
(4 rows)

omm=# -- 建立索引并显示有索引条件的执行计划
omm=# create index customer_address_idx on tpcds.t1(id);
CREATE INDEX
omm=# EXPLAIN SELECT * FROM tpcds.t1 WHERE id<100;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=7.65..20.14 rows=439 width=32)
   Recheck Cond: (id < 100)
   ->  Bitmap Index Scan on customer_address_idx  (cost=0.00..7.54 rows=439 width=0)
         Index Cond: (id < 100)
(4 rows)

omm=# update tpcds.t1 set id = id + 1 where id <100;
UPDATE 90
omm=# VACUUM (VERBOSE, ANALYZE) tpcds.t1;
INFO:  vacuuming "tpcds.t1"(gaussdb pid=1)
INFO:  index "customer_address_idx" now contains 79 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:  "t1": found 0 removable, 79 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL:  40 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 "tpcds.t1"(gaussdb pid=1)
INFO:  index "customer_address_idx" now contains 202 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:  "t1": found 0 removable, 202 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL:  50 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 "tpcds.t1"(gaussdb pid=1)
INFO:  index "customer_address_idx" now contains 0 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:  "t1": found 0 removable, 0 nonremovable row versions in 0 out of 0 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 "customer_address_idx" to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "tpcds.t1"(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 39 live rows and 40 dead rows; 39 rows in sample, 39 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 152 live rows and 50 dead rows; 152 rows in sample, 152 estimated total rows(gaussdb pid=1)
VACUUM
omm=# drop table tpcds.t1;
drop schema tpcds cascade;
DROP TABLE
omm=# DROP SCHEMA
omm=# \dn
   List of schemas
    Name     | Owner 
-------------+-------
 cstore      | omm
 dbe_perf    | omm
 pkg_service | omm
 public      | omm
 snapshot    | omm
(5 rows)