pg_qualstats

os: centos 7.4
db: postgresql 10.11

pg_qualstats 是 postgresql 的一个 extension,用于保存"where"语句和"join"子句中谓词的统计信息。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i postgresql
postgresql10.x86_64                10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-contrib.x86_64        10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-debuginfo.x86_64      10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-devel.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-docs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-libs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-odbc.x86_64           12.00.0000-1PGDG.rhel7              @pgdg10  
postgresql10-plperl.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-plpython.x86_64       10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-pltcl.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-server.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-tcl.x86_64            2.4.0-1.rhel7                       @pgdg10  
postgresql10-tcl-debuginfo.x86_64  2.3.1-1.rhel7                       @pgdg10  
postgresql10-test.x86_64           10.11-2PGDG.rhel7                   @pgdg10 

# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

下载安装

# yum list all |grep -i pg_qualstats
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
pg_qualstats10.x86_64                     1.0.9-1.rhel7.1              pgdg10

# yum install pg_qualstats10

# repoquery -ql pg_qualstats10
/usr/pgsql-10/doc/extension/README-pg_qualstats.md
/usr/pgsql-10/lib/pg_qualstats.so
/usr/pgsql-10/share/extension/pg_qualstats--2.0.1.sql
/usr/pgsql-10/share/extension/pg_qualstats.control
/usr/share/licenses/pg_qualstats10-2.0.1
/usr/share/licenses/pg_qualstats10-2.0.1/LICENSE

修改配置文件 shared_preload_libraries

# vi /var/lib/pgsql/10/data/postgresql.conf

shared_preload_libraries = 'pg_stat_statements, pg_qualstats'


# systemctl restart postgresql-10.service 

初步使用

创建 extension pg_qualstats pg_stat_statements

# su - postgres
$ psql

postgres=# create extension pg_stat_statements;
postgres=# create extension pg_qualstats;
postgres=# 
postgres=# \dx
                                         List of installed extensions
        Name        | Version |   Schema   |                            Description                            
--------------------+---------+------------+-------------------------------------------------------------------
 pg_qualstats       | 2.0.1   | public     | An extension collecting statistics about quals
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# \dx+ pg_qualstats
             Objects in extension "pg_qualstats"
                     Object description                      
-------------------------------------------------------------
 function pg_qualstats()
 function pg_qualstats_deparse_qual(qual)
 function pg_qualstats_example_queries()
 function pg_qualstats_example_query(bigint)
 function pg_qualstats_get_idx_col(bigint,boolean)
 function pg_qualstats_get_qualnode_rel(bigint)
 function pg_qualstats_index_advisor(integer,integer,text[])
 function pg_qualstats_names()
 function pg_qualstats_reset()
 type qual
 type qualname
 view pg_qualstats
 view pg_qualstats_all
 view pg_qualstats_by_query
 view pg_qualstats_pretty
(15 rows)

postgres=# select name,setting from pg_settings where name like 'pg_qualstats%';
                name                 | setting 
-------------------------------------+---------
 pg_qualstats.enabled                | on
 pg_qualstats.max                    | 1000
 pg_qualstats.min_err_estimate_num   | 0
 pg_qualstats.min_err_estimate_ratio | 0
 pg_qualstats.resolve_oids           | off
 pg_qualstats.sample_rate            | 0.001
 pg_qualstats.track_constants        | on
 pg_qualstats.track_pg_catalog       | off
(8 rows)

postgres=# select name,setting from pg_settings where name like 'pg_stat_statements%';
               name               | setting 
----------------------------------+---------
 pg_stat_statements.max           | 5000
 pg_stat_statements.save          | on
 pg_stat_statements.track         | top
 pg_stat_statements.track_utility | on
(4 rows)

pg_qualstats.enabled (boolean, default true): whether or not pg_qualstats should be enabled
pg_qualstats.max: the maximum number of predicated and query text tracked (defaults to 1000)
pg_qualstats.resolve_oids (boolean, default false): whether or not pg_qualstats should resolve oids at query time, or juste store the oids. Enabling this parameter makes the data analysis much more easy, since a connection to the database where the query was executed won’t be necessary, but it will eat much more space (624 bytes per entry instead of 176). Additionnaly, this will require some catalog lookups, which aren’t free.
pg_qualstats.sample_rate (double, default -1): the fraction of queries that should be sampled. For example, 0.1 means that only one out of ten queries will be sampled. The default (-1) means automatic, and results in a value of 1 / max_connections, so that statiscally, concurrency issues will be rare.
pg_qualstats.track_constants (bolean, default true): whether or not pg_qualstats should keep track of each constant value individually. Disabling this GUC will considerably reduce the number of entries necessary to keep track of predicates.
pg_qualstats.track_pg_catalog (boolean, default false): whether or not pg_qualstats should compute predicates on object in pg_catalog schema.

生成测试表

postgres=# create table tmp_t0(id int8,name1 varchar(100),name2 varchar(100));

postgres=# create table tmp_t1(id int8,name1 varchar(100),name2 varchar(100));

postgres=# 
postgres=# insert into tmp_t0 
select id,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,1000000) as id;

postgres=# insert into tmp_t1 
select id,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,1000000) as id;

postgres=# set pg_qualstats.sample_rate=1;

postgres=# select t0.*,t1.* from tmp_t0 t0,tmp_t1 t1 where t0.id=t1.id and t0.id<=10;

pg_qualstats.sample_rate=1 会确保所有的query都会被抓取到.

查看 pg_stat_statements

postgres=# \x

postgres=# select * from pg_stat_statements where query like '%t0.id%';
-[ RECORD 1 ]-------+--------------------------------------------------------------------------
userid              | 10
dbid                | 13808
queryid             | 3486472503
query               | select t0.*,t1.* from tmp_t0 t0,tmp_t1 t1 where t0.id=t1.id and t0.id<=$1
calls               | 6
total_time          | 1877.331149
min_time            | 299.02719
max_time            | 336.694227
mean_time           | 312.888524833333
stddev_time         | 12.7704381729133
rows                | 150
shared_blks_hit     | 321027
shared_blks_read    | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0


postgres=# with table_info as (
  select pc.relname,pgq.execution_count,pgq.uniquequalnodeid
    from pg_qualstats as pgq
         left join pg_class as pc on pgq.lrelid = pc.relfilenode
),query_info as (
  select pss.total_time/calls as average_time,pss.query,pgb.uniquequalnodeid
    from pg_qualstats_by_query as pgb 
         left join pg_stat_statements as pss on pgb.queryid = pss.queryid
)
select *
  from table_info as t 
       right join query_info as q on t.uniquequalnodeid = q.uniquequalnodeid;

-[ RECORD 1 ]----+--------------------------------------------------------------------------
relname          | tmp_t0
execution_count  | 3000000
uniquequalnodeid | 2803249348
average_time     | 441.271635
query            | select t0.*,t1.* from tmp_t0 t0,tmp_t1 t1 where t0.id=t1.id and t0.id<=$1
uniquequalnodeid | 2803249348

参考:
https://www.postgresql.org/download/products/5-reporting-tools/
https://github.com/powa-team/pg_qualstats/

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

数据库人生

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值