暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第 19 天 | 统计信息、执行计划

openGauss每日一练第 19 天 | 统计信息、执行计划

原创 谢辉元 2021-12-20
406

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

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

create table store
(
id int,
name varchar(30),
create_date date
)
partition by range (id)
(
partition store_p1000 values less than (1000),
partition store_p2000 values less than (2000),
partition store_p3000 values less than (3000),
partition store_p4000 values less than (4000),
partition store_p5000 values less than (5000),
partition store_p6000 values less than (6000),
partition store_p7000 values less than (7000)
);
insert into store values(1001,'Apple','2018-06-01');
insert into store values(2001,'Mac','2019-06-01');
insert into store values(3001,'Mate','2020-06-01');
insert into store values(4001,'Oppo','2021-06-01');
insert into store values(5001,'Orange','2022-06-01');
insert into store values(6001,'Samsung','2023-06-01');
insert into store values(generate_series(1, 6000));

2.收集表统计信息

analyze VERBOSE store;
select relname, relpages, reltuples from pg_class where relname = 'store';

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

explain select * from store where id=5001;
create index ind_store_id on store(id); 
explain select * from store where id=5001;

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

omm=# analyze VERBOSE store;
INFO:  analyzing "public.store"(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 999 live rows and 0 dead rows; 999 rows in sample, 999 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# select relname, relpages, reltuples from pg_class where relname = 'store';
 relname | relpages | reltuples 
---------+----------+-----------
 store   |       31 |      6006
(1 row)

omm=# explain select * from store where id=5001;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..79.08 rows=2 width=13)
   Iterations: 1
   ->  Partitioned Seq Scan on store  (cost=0.00..79.08 rows=2 width=13)
         Filter: (id = 5001)
         Selected Partitions:  6
(5 rows)

omm=# create index ind_store_id on store(id); 
CREATE INDEX
omm=# explain select * from store where id=5001;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Bitmap Heap Scan on store  (cost=4.27..8.05 rows=2 width=13)
   Recheck Cond: (id = 5001)
   ->  Bitmap Index Scan on ind_store_id  (cost=0.00..4.27 rows=2 width=0)
         Index Cond: (id = 5001)
(4 rows)
### 创建索引后，执行计划由 Partitioned Seq Scan 变成了 Bitmap Index Scan
cost由79.08（cost=0.00..79.08）变成了4.27（cost=0.00..4.27）还是大大的降低了

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

update store set id= id + 500 where id<3000;
VACUUM (VERBOSE, ANALYZE) store;
select relname, relpages, reltuples from pg_class where relname = 'store';

omm=# update store set id= id + 500 where id<3000;
UPDATE 3001
omm=# VACUUM (VERBOSE, ANALYZE) store;
INFO:  vacuuming "public.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 1498 row versions in 35 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:  "store": found 0 removable, 1498 nonremovable row versions in 7 out of 7 pages(gaussdb pid=1)
DETAIL:  999 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.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 2002 row versions in 35 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:  "store": found 0 removable, 2002 nonremovable row versions in 9 out of 9 pages(gaussdb pid=1)
DETAIL:  1001 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.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 2002 row versions in 35 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:  "store": found 0 removable, 2002 nonremovable row versions in 9 out of 9 pages(gaussdb pid=1)
DETAIL:  1001 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.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 1501 row versions in 35 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:  "store": found 0 removable, 1501 nonremovable row versions in 7 out of 7 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:  vacuuming "public.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 1001 row versions in 35 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:  "store": found 0 removable, 1001 nonremovable row versions in 5 out of 5 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:  vacuuming "public.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 1001 row versions in 35 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:  "store": found 0 removable, 1001 nonremovable row versions in 5 out of 5 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:  vacuuming "public.store"(gaussdb pid=1)
INFO:  index "ind_store_id" now contains 2 row versions in 35 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:  "store": found 0 removable, 2 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 "ind_store_id" to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.store"(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 7 of 7 pages, containing 499 live rows and 999 dead rows; 499 rows in sample, 499 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 9 of 9 pages, containing 1001 live rows and 1001 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 9 of 9 pages, containing 1001 live rows and 1001 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 7 of 7 pages, containing 1501 live rows and 0 dead rows; 1501 rows in sample, 1501 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 5 of 5 pages, containing 1001 live rows and 0 dead rows; 1001 rows in sample, 1001 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "store": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows(gaussdb pid=1)
VACUUM
omm=# select relname, relpages, reltuples from pg_class where relname = 'store';
 relname | relpages | reltuples 
---------+----------+-----------
 store   |       43 |      6006
(1 row)

5.清理数据

drop table store;

总结：学会获取查看执行计划是SQL优化的第一步，而准确的统计信息又是SQL能有一个正确的执行计划的基本要求。

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

评论