gms_stats使用
创建Extension
创建gms_stats Extension可直接使用CREATE Extension命令进行创建:
openGauss=# CREATE Extension gms_stats;
使用Extension
创建用于测试的schema,table和materialized view.
openGauss=# create schema gms_stats_test;
CREATE SCHEMA
openGauss=# set search_path=gms_stats_test;
SET
openGauss=# create table normal_table(a int, b char(10));
CREATE TABLE
openGauss=# insert into normal_table select generate_series(1,500), 'abc';
INSERT 0 500
openGauss=#
openGauss=# create table partition_table(a int) partition by range(a) (partition p1 values less than(100),partition p2 values less than(maxvalue));
CREATE TABLE
openGauss=# insert into partition_table select generate_series(1,600);
INSERT 0 600
openGauss=#
openGauss=# create materialized view mv_tb as select * from normal_table;
CREATE MATERIALIZED VIEW
openGauss=#
openGauss=# select schemaname, tablename, attname, avg_width, most_common_vals, most_common_freqs from pg_stats where schemaname='gms_stats_test' order by tablename, attname;
schemaname | tablename | attname | avg_width | most_common_vals | most_common_freqs
------------+-----------+---------+-----------+------------------+-------------------
(0 rows)
查询结果
openGauss=# begin
openGauss$# gms_stats.gather_schema_stats('gms_stats_test');
openGauss$# end;
openGauss$# /
NOTICE: PL/SQL procedure successfully completed.
CONTEXT: SQL statement "CALL gms_stats.gather_schema_stats('gms_stats_test')"
PL/pgSQL function inline_code_block line 2 at PERFORM
ANONYMOUS BLOCK EXECUTE
openGauss=# select schemaname, tablename, attname, avg_width, most_common_vals, most_common_freqs from pg_stats where schemaname='gms_stats_test' order by tablename, attname;
schemaname | tablename | attname | avg_width | most_common_vals | most_common_freqs
----------------+-----------------+---------+-----------+------------------+-------------------
gms_stats_test | mv_tb | a | 4 | |
gms_stats_test | mv_tb | b | 11 | {"abc "} | {1}
gms_stats_test | normal_table | a | 4 | |
gms_stats_test | normal_table | b | 11 | {"abc "} | {1}
gms_stats_test | partition_table | a | 4 | |
(5 rows)
删除Extension
在openGauss中删除gms_stats Extension的方法如下所示:
openGauss=# DROP Extension gms_stats [CASCADE];
说明:
如果Extension被其它对象依赖,需要加入CASCADE(级联)关键字,删除所有依赖对象。
意见反馈