88.sys_qualstats
88.1.插件sys_qualstats简介
sys_qualstats 是一个 Kingbase 的扩展，用于保存WHERE语句和JOIN子句中谓词的统计信息。如果希望能够分析数据库中的最常执行的quals（谓词），这非常的有用，该插件利用这点来提供索引建议。
插件名为 sys_qualstats
插件版本 V1.0.9
88.2.插件sys_qualstats加载方式
在使用 sys_qualstats 之前，我们需要将他添加到 kingbase.conf 文件的 shared_preload_libraries 中，并重启 KingbaseES 数据库
shared_preload_libraries = 'sys_qualstats,sys_stat_statements'# (change requires restart)
sys_qualstats 插件依赖 sys_stat_statements 插件
进入 KingBase 创建插件
create extension sys_qualstats;create extension sys_stat_statements;
88.3.插件sys_qualstats的参数配置
可以在 kingbase.conf 中配置以下信息：
sys_qualstats.enable(boolen,default true):是否应该启用 sys_qualstats。
sys_qualstats.max默认为 1000，要保存的条目数。
sys_qualstats.track_pg_catalog:默认为 false，确定是否跟踪 sys_catalog 表上的谓词。
sys_qualstats.resolve_oids:默认为 false。确定在谓词收集期间，对象的实际名称是否应该和 OID 一起存储，开销是不可忽视的，因为每个条目将占用616个字节而不是168个字节。
sys_qualstats.track_constants:默认为false，是否单独跟踪每个常量值。如果为 true，则每个谓词的每个新值都会产生一个新条目。
sys_qualstats.sample_rate:默认为 -1，应该被采样的查询比例。1 表示对每个查询进行采样，0 表示基本禁用该功能， -1 表示自动调整。
sys_stat_statements.track='top'
88.4.插件sys_qualstats的使用方法
88.4.1.sys_qualstats
返回每个限定符的计数
userid: 执行查询的用户 oid
dbid: 已经执行查询的数据库的 oid
lrelid, lattnum: 如果存在左侧 var，表示左侧 var 的关系和属性编号的 oid
rrelid, rattnum: 如果存在右侧 var，表示右侧 var 的关系和属性编号的 oid
opno:表达式中使用运算符的 oid
qualid父 AND 表达式的规范化标识符，该标识符的计算不包括常量
qualnodeid简单谓词的规范标识符，该标识符的计算包括常量
uniquequalid简单谓词的唯一标识符，该标识符计算包括常量
parenthash: 父 AND 表达式的 hash 值
nbfiltered此谓词丢弃的元组数
nodehash: 谓词 hash
count: 此谓词出现的总次数
queryid: 如果安装了 sys_stat_statements，表示此查询的 queryid，用来追溯查询的sql
constvalue: 右侧常量字符串表示形式，截断为 80 个字符
execution_count 表示这个语句执行了多少次
eval_type评估类型，f 表示扫描后评估的谓词，i 表示索引谓词
test=# select * from sys_qualstats;-[ RECORD 1 ]-----+---------------------userid | 10dbid | 16249lrelid | 17227lattnum | 1opno | 96rrelid |rattnum |qualid | 570210875uniquequalid| 420950748qualnodeid | 271185uniquequalnodeid | 2320559360occurences | 1execution_count | 3nbfiltered | 2constant_position | 28queryid | -3480922324520548892constvalue | 100::integereval_type | f
88.4.2.sys_qualstats_reset()
重置内部计数器并清除每个遇到的 qual（谓词）
test=# select * from sys_qualstats_reset();sys_qualstats_reset--------------------(1 row)
88.4.3.sys_qualstats_example_query
返回给定 queryid 的 SQL 语句
test=# select * from sys_qualstats_example_query(-3480922324520548892);sys_qualstats_example_query---------------------------------------------------select * from t1 where id = 100 and name = 'abc';(1 row)
88.4.4.sys_qualstats_names
返回所有存储的 SQL 语句
test=# select * from sys_qualstats_names();-[ RECORD 1 ]-----+---------------------userid | 10dbid | 16249lrelid | 17227lattnum | 1opno | 96rrelid |rattnum |qualid | 570210875uniquequalid| 420950748qualnodeid | 271185uniquequalnodeid | 2320559360occurences | 1execution_count | 3nbfiltered | 2constant_position | 28queryid | -3480922324520548892constvalue | 100::integereval_type | frolname |dbname |lrelname|lattname|opname |rrelname|rattname|
88.4.5.sys_qualstats_example_queries
返回所有存储的查询 sql
test=# select query from sys_qualstats_example_queries();query---------------------------------------------------select * from t1 where id = 100;select * from t1 where id = 100 and name = 'abc';select * from t1 where id = 100 and age = 300;(3 rows)
88.4.6.sys_qualstats_pretty
显示 sys_qualstats 视图中每个属性的可读形式
test=# select * from sys_qualstats_pretty;left_schema | left_table | left_column | operator | right_schema | right_table | right_column | occurences | execution_count | nbfiltered-------------+------------+-------------+--------------+--------------+-------------+--------------+------------+-----------------+------------public| t1 | age | pg_catalog.= | | | |1 |3 |3public| t1 | id| pg_catalog.= | | | |4 | 12 |9public| t1 | name | pg_catalog.= | | | |1 |3 |2
88.4.7.sys_qualstats_all
对每个属性/运算符的计数求和
test=# select * from sys_qualstats_all;dbid | relid | userid | queryid | attnums | opno | qualid | occurences | execution_count | nbfiltered | qualnodeid-------+-------+--------+----------------------+---------+------+------------+------------+-----------------+------------+------------16249 | 17227 |10 | -3480922324520548892 | {1}| 96 | 570210875 |1 |3 |2 | 57021087516249 | 17227 |10 | -3480922324520548892 | {3}| 98 | 570210875 |1 |3 |2 | 57021087516249 | 17227 |10 | 6307461809864648304 | {1,2} | 96 | 2519621490 |2 |6 |6 | 251962149016249 | 17227 |10 | 6828434147671954623 | {1}| 96 | |2 |6 |4 | 271185
88.4.8.sys_qualstats_indexes
查找不存在索引的属性，并给出需要创建索引建议
test=# select * from sys_qualstats_indexes;relid | attnames |possible_types| execution_count-------+-----------+--------------------------+-----------------t1 | {age,id} | {brin,btree,hash} |6t1 | {id}| {brin,btree,hash} |6t1 | {id,name} | {brin,btree,hash,spgist} |3
88.4.9.sys_qualstats_by_query
仅返回形式为 VAR OPERATOR CONSTANT 的谓词
test=# select * from sys_qualstats_by_query;-[ RECORD 1 ]----+-------------------------------------------------------------------------uniquequalnodeid | 41333584dbid | 16249userid | 10qualnodeid | 1621559404occurences | 1execution_count | 9nbfiltered | 0queryid| -6920971673199143593constvalues| {2::integer,2::integer}quals | {"(16457,1,523,i)","(16457,1,525,i)"}
88.4.10.使用索引建议
88.4.10.1.参数配置
kingbase.conf 中设置
shared_preload_libraries = 'sys_qualstats,sys_stat_statements，sys_hypo' # (change requires restart)sys_stat_statements.track='top'sys_qualstats.sample_rate=1
88.4.10.2.创建插件
create extension sys_qualstats;create extension sys_stat_statements;create extension sys_hypo;
88.4.10.3.示例
88.4.10.3.1.准备测试数据
create table t1(id int, name text);INSERT INTO t1 SELECT (random() * 1000000)::int, md5(g::text) FROM generate_series(1, 1000000) g;Select * from t1 where id = 100;id |name-----+----------------------------------100 | 71735fbd796ae9e347ad82f208f1232b100 | 4fea58f776ef5d6e22156e08c2191b09100 | 177848a70c96523b9c7a18dae7f9631d(3 rows)
88.4.10.3.2.查看执行计划
explain analyze Select * from t1 where id = 100;QUERY PLAN-----------------------------------------------------------Gather (cost=1000.00..15375.79 rows=5292 width=36) (actual time=18.471..183.579 rows=3 loops=1)Workers Planned: 2Workers Launched: 0-> Parallel Seq Scan on t1 (cost=0.00..13846.59 rows=2205 width=36) (actual time=18.043..183.089 rows=3loops=1)Filter: (id = 100)Rows Removed by Filter: 999997Planning Time: 0.134 msExecution Time: 183.658 ms(8 rows)
88.4.10.3.3.查看索引建议
new_cost:创建索引以后的 cost 值
old_cost无索引情况的 cost 值
ddl_index创建 index 的sql 语句
benefit创建索引后的收益值
select * from index_recommendation_by_qual;-[ RECORD 1 ]---+-----------------------------------------nspname | publicrelid | t1attnames | {id}possible_types | {brin,btree,hash}execution_count | 1000000queryid | -8700169081888605904query | Select * from t1 where id = 100;ddl_index | CREATE INDEX ON public.t1 USING btree (id)old_cost | 15375.79new_cost | 7864.65benefit | 48.85 %maintenece_cost | 95.000000
88.4.10.4.根据索引建议创建索引
CREATE INDEX ON public.t1 USING btree (id);
88.4.10.5.查看创建完索引后的执行计划
explain analyze Select * from t1 where id = 100;QUERY PLAN--------------------------------------------------------Index Scan using t1_id_idx on t1 (cost=0.42..12.46 rows=2 width=37) (actual time=0.053..0.060 rows=2 loops=1)Index Cond: (id = 100)Planning Time: 0.171 msExecution Time: 0.165 ms(4 rows)
88.5.插件sys_qualstats卸载方法¶
进入 Kingbase 创建插件
drop extension sys_qualstats ;drop extension sys_hypo ;
88.6.插件sys_qualstats升级方法
sys_qualstats扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级些插件。
