这篇文章主要讲解了“PostgreSQL的pg_qualstats有什么作用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL的pg_qualstats有什么作用”吧！

pg_qualstats是PostgreSQL的一个extension,用于统计WHERE/JOIN语句中出现的谓词。

安装
源代码位于github上,clone后可直接编译安装.

[pg12@localhostextensions]$gitclonehttps://github.com/powa-team/pg_qualstats.gitfatal:couldnotcreateworktreedir'pg_qualstats'.:Permissiondenied[pg12@localhostextensions]$gitclonehttps://github.com/powa-team/pg_qualstats.gitCloninginto'pg_qualstats'...remote:Enumeratingobjects:5,done.remote:Countingobjects:100%(5/5),done.remote:Compressingobjects:100%(5/5),done.remote:Total717(delta0),reused1(delta0),pack-reused712Receivingobjects:100%(717/717),299.35KiB|300.00KiB/s,done.Resolvingdeltas:100%(445/445),done.[pg12@localhostextensions]$lspg_qualstatsrum[pg12@localhostextensions]$cdpg_qualstats/[pg12@localhostpg_qualstats]$lsCHANGELOGdebianexpectedMakefilepg_qualstats--1.0.8.sqlpg_qualstats.controltestCONTRIBUTORS.mddocLICENSEMETA.jsonpg_qualstats.cREADME.md[pg12@localhostpg_qualstats]$makegcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPIC-I.-I./-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-opg_qualstats.opg_qualstats.c-MMD-MP-MF.deps/pg_qualstats.Pogcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPICpg_qualstats.o-L/appdb/xdb/pg12beta1/lib-Wl,--as-needed-Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags-shared-opg_qualstats.so[pg12@localhostpg_qualstats]$makeinstall/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/lib/postgresql'/usr/bin/install-c-m644.//pg_qualstats.control'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m644.//pg_qualstats--1.0.8.sql'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m755pg_qualstats.so'/appdb/xdb/pg12beta1/lib/postgresql/'[pg12@localhostpg_qualstats]$

pg_qualstats与pg_stat_statements类似,需要修改初始化参数shared_preload_libraries

[pg12@localhostpg12db1]$grep'shared_preload'postgresql.confshared_preload_libraries='pg_stat_statements,pg_qualstats'#(changerequiresrestart)[pg12@localhostpg12db1]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-07-2615:47:01.241CST[1862]LOG:startingPostgreSQL12beta1onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-07-2615:47:01.242CST[1862]LOG:listeningonIPv4address"0.0.0.0",port54322019-07-2615:47:01.242CST[1862]LOG:listeningonIPv6address"::",port54322019-07-2615:47:01.244CST[1862]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-07-2615:47:01.282CST[1862]LOG:redirectinglogoutputtologgingcollectorprocess2019-07-2615:47:01.282CST[1862]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted

创建extension

testdb=#createextensionpg_qualstats;CREATEEXTENSIONtestdb=#showshared_preload_libraries;shared_preload_libraries---------------------------------pg_stat_statements,pg_qualstats(1row)testdb=#\dxListofinstalledextensionsName|Version|Schema|Description--------------------+---------+--------------+----------------------------------------------------blackhole_am|1.0|public|templatetableAMeatingalldatabloom|1.0|public|bloomaccessmethod-signaturefilebasedindexbtree_gin|1.3|public|supportforindexingcommondatatypesinGINdblink|1.2|public|connecttootherPostgreSQLdatabasesfromwithinadatabasehypopg|1.1.3|pgextensions|HypotheticalindexesforPostgreSQLpageinspect|1.7|public|inspectthecontentsofdatabasepagesatalowlevelpg_qualstats|1.0.8|public|Anextensioncollectingstatisticsaboutqualspg_stat_statements|1.7|public|trackexecutionstatisticsofallSQLstatementsexecutedpg_trgm|1.4|public|textsimilaritymeasurementandindexsearchingbasedontrigramspgcrypto|1.3|public|cryptographicfunctionsplpgsql|1.0|pg_catalog|PL/pgSQLprocedurallanguagerum|1.3|public|RUMindexaccessmethod(12rows)

参数
pg_qualstats.enabled: true or false (to enable or to disable pg_qualstats). ON by default.

pg_qualstats.track_constants: true or false (to enable tracking of each constant. False would reduce the number of entries to track predicates.)
pg_qualstats.max: The number of queries tracked. Defaults to 1000.

pg_qualstats.resolve_oids: Just store the oids or resolve them and store at query time. This takes additional space.

pg_qualstats.track_pg_catalog: Defaults to false. Whether or not the predicates of the objects in pg_catalog schema should be computed.

pg_qualstats.sample_rate: Default is -1. The fraction of queries to be sampled. -1 defaults to (1/max_connections). When set to 1, everything is sampled. Similarly when set to 0.1, one out of 10 queries are sampled.

使用
配置参数:收集所有的谓词

testdb=#ALTERSYSTEMSETpg_qualstats.sample_rateTO1;ALTERSYSTEMtestdb=#selectpg_reload_conf();pg_reload_conf----------------t(1row)

安装sysbench

./autogen.sh./configure--with-pgsql--with-pgsql-includes=/appdb/atlasdb/pg12beta1/include--with-pgsql-libs=/appdb/atlasdb/pg12beta1/libmakemakeinstall

创建用于测试的db

testdb=#createdatabasebenchdb;CREATEDATABASE

使用tpcc进行测试

[pg12@localhosttest]$gitclonehttps://github.com/Percona-Lab/sysbench-tpcc.gitCloninginto'sysbench-tpcc'...remote:Enumeratingobjects:163,done.remote:Total163(delta0),reused0(delta0),pack-reused163Receivingobjects:100%(163/163),52.38KiB|0bytes/s,done.Resolvingdeltas:100%(85/85),done.[pg12@localhosttest]$lssysbench-tpcc[pg12@localhosttest]$cdsysbench-tpcc/[pg12@localhostsysbench-tpcc]$pwd/data/test/sysbench-tpcc[pg12@localhostsysbench-tpcc]$ls[pg12@localhostsysbench-tpcc]$./tpcc.lua--pgsql-user=pg12--pgsql-port=5432--pgsql-db=benchdb--time=10--threads=2--report-interval=1--tables=2--scale=2--use_fk=0--trx_level=RC--db-driver=pgsqlpreparesysbench1.1.0-174f3aa(usingbundledLuaJIT2.1.0-beta3)Initializingworkerthreads...Creatingtables:2Creatingtables:1Addingindexes1...Waitingontables30secAddingindexes2...Waitingontables30secloadingtables:1forwarehouse:1loadingtables:1forwarehouse:2loadingtables:2forwarehouse:1loadingtables:2forwarehouse:2[pg12@localhostsysbench-tpcc]$./tpcc.lua--pgsql-user=pg12--pgsql-port=5432--pgsql-db=benchdb--time=10--threads=2--report-interval=1--tables=2--scale=2--use_fk=0--trx_level=RC--db-driver=pgsqlrunsysbench1.1.0-174f3aa(usingbundledLuaJIT2.1.0-beta3)Runningthetestwithfollowingoptions:Numberofthreads:2Reportintermediateresultsevery1second(s)InitializingrandomnumbergeneratorfromcurrenttimeInitializingworkerthreads...Threadsstarted![1s]thds:2tps:90.83qps:2492.29(r/w/o:1135.85/1170.79/185.65)lat(ms,95%):59.99err/s0.00reconn/s:0.00[2s]thds:2tps:87.00qps:2462.93(r/w/o:1103.97/1184.97/173.99)lat(ms,95%):64.47err/s0.00reconn/s:0.00[3s]thds:2tps:92.03qps:2465.76(r/w/o:1139.35/1142.35/184.06)lat(ms,95%):50.11err/s1.00reconn/s:0.00[4s]thds:2tps:81.98qps:2491.38(r/w/o:1126.72/1200.70/163.96)lat(ms,95%):61.08err/s0.00reconn/s:0.00[5s]thds:2tps:88.99qps:2519.78(r/w/o:1143.90/1197.89/177.98)lat(ms,95%):51.02err/s0.00reconn/s:0.00[6s]thds:2tps:82.01qps:2465.44(r/w/o:1122.20/1179.21/164.03)lat(ms,95%):63.32err/s1.00reconn/s:0.00[7s]thds:2tps:86.01qps:2515.42(r/w/o:1154.19/1189.20/172.03)lat(ms,95%):61.08err/s1.00reconn/s:0.00[8s]thds:2tps:89.99qps:2420.80(r/w/o:1114.91/1125.91/179.99)lat(ms,95%):50.11err/s0.00reconn/s:0.00[9s]thds:2tps:100.01qps:2632.33(r/w/o:1208.15/1224.15/200.02)lat(ms,95%):50.11err/s1.00reconn/s:0.00[10s]thds:2tps:104.76qps:2593.05(r/w/o:1188.27/1195.26/209.52)lat(ms,95%):44.98err/s1.00reconn/s:0.00SQLstatistics:queriesperformed:read:11477write:11851other:1814total:25142transactions:906(90.23persec.)queries:25142(2503.86persec.)ignorederrors:5(0.50persec.)reconnects:0(0.00persec.)Throughput:events/s(eps):90.2272timeelapsed:10.0413stotalnumberofevents:906Latency(ms):min:2.64avg:22.15max:80.5195thpercentile:53.85sum:20070.80Threadsfairness:events(avg/stddev):453.0000/9.00executiontime(avg/stddev):10.0354/0.00[pg12@localhostsysbench-tpcc]$

查询pg_qualstats_indexes,可查看哪些列上没有index,但在这些列上存在谓词

testdb=#\cbenchdbYouarenowconnectedtodatabase"benchdb"asuser"pg12".benchdb=#select*frompg_qualstats_indexes;psql:ERROR:relation"pg_qualstats_indexes"doesnotexistLINE1:select*frompg_qualstats_indexes;^benchdb=#CREATEEXTENSIONhypopg;CREATEEXTENSIONbenchdb=#CREATEEXTENSIONpg_stat_statements;CREATEEXTENSIONbenchdb=#CREATEEXTENSIONpg_qualstats;CREATEEXTENSIONbenchdb=#showshared_preload_libraries;shared_preload_libraries---------------------------------pg_stat_statements,pg_qualstats(1row)benchdb=#select*frompg_qualstats_indexes;relid|attnames|possible_types|execution_count-------------+--------------+--------------------------+-----------------customer2|{c_id}|{brin,btree,hash}|5customer2|{c_last}|{brin,btree,hash,spgist}|12customer1|{c_id}|{brin,btree,hash}|8customer1|{c_last}|{brin,btree,hash,spgist}|34orders2|{o_c_id}|{brin,btree,hash}|1order_line2|{ol_o_id}|{brin,btree,hash}|8order_line1|{ol_o_id}|{brin,btree}|213stock1|{s_quantity}|{brin,btree}|213(8rows)benchdb=#\dcustomer2Table"public.customer2"Column|Type|Collation|Nullable|Default----------------+-----------------------------+-----------+----------+---------c_id|integer||notnull|c_d_id|smallint||notnull|c_w_id|smallint||notnull|c_first|charactervarying(16)|||c_middle|character(2)|||c_last|charactervarying(16)|||c_street_1|charactervarying(20)|||c_street_2|charactervarying(20)|||c_city|charactervarying(20)|||c_state|character(2)|||c_zip|character(9)|||c_phone|character(16)|||c_since|timestampwithouttimezone|||c_credit|character(2)|||c_credit_lim|bigint|||c_discount|numeric(4,2)|||c_balance|numeric(12,2)|||c_ytd_payment|numeric(12,2)|||c_payment_cnt|smallint|||c_delivery_cnt|smallint|||c_data|text|||Indexes:"customer2_pkey"PRIMARYKEY,btree(c_w_id,c_d_id,c_id)"idx_customer2"btree(c_w_id,c_d_id,c_last,c_first)

感谢各位的阅读，以上就是“PostgreSQL的pg_qualstats有什么作用”的内容了，经过本文的学习后，相信大家对PostgreSQL的pg_qualstats有什么作用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！