pg_qualstats是PostgreSQL的一个extension,用于统计WHERE/JOIN语句中出现的谓词。 >pg_qualstats is a PostgreSQL extension keeping statistics on predicates found in WHERE statements and JOIN clauses.This is useful if you want to be able to analyze what are the most-often executed quals (predicates) on your database. The powa project makes use of this to provide advances index suggestions. **安装** 源代码位于github上,clone后可直接编译安装. ``` [pg12@localhost extensions]$ git clone https://github.com/powa-team/pg_qualstats.git fatal: could not create work tree dir 'pg_qualstats'.: Permission denied [pg12@localhost extensions]$ git clone https://github.com/powa-team/pg_qualstats.git Cloning into 'pg_qualstats'... remote: Enumerating objects: 5, done. remote: Counting objects: 100% (5/5), done. remote: Compressing objects: 100% (5/5), done. remote: Total 717 (delta 0), reused 1 (delta 0), pack-reused 712 Receiving objects: 100% (717/717), 299.35 KiB | 300.00 KiB/s, done. Resolving deltas: 100% (445/445), done. [pg12@localhost extensions]$ ls pg_qualstats rum [pg12@localhost extensions]$ cd pg_qualstats/ [pg12@localhost pg_qualstats]$ ls CHANGELOG debian expected Makefile pg_qualstats--1.0.8.sql pg_qualstats.control test CONTRIBUTORS.md doc LICENSE META.json pg_qualstats.c README.md [pg12@localhost pg_qualstats]$ make gcc -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 -o pg_qualstats.o pg_qualstats.c -MMD -MP -MF .deps/pg_qualstats.Po gcc -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 pg_qualstats.o -L/appdb/xdb/pg12beta1/lib -Wl,--as-needed -Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags -shared -o pg_qualstats.so [pg12@localhost pg_qualstats]$ make install /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 -m 644 .//pg_qualstats.control '/appdb/xdb/pg12beta1/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pg_qualstats--1.0.8.sql '/appdb/xdb/pg12beta1/share/postgresql/extension/' /usr/bin/install -c -m 755 pg_qualstats.so '/appdb/xdb/pg12beta1/lib/postgresql/' [pg12@localhost pg_qualstats]$ ``` pg_qualstats与pg_stat_statements类似,需要修改初始化参数shared_preload_libraries ``` [pg12@localhost pg12db1]$ grep 'shared_preload' postgresql.conf shared_preload_libraries = 'pg_stat_statements,pg_qualstats' # (change requires restart) [pg12@localhost pg12db1]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2019-07-26 15:47:01.241 CST [1862] LOG: starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit 2019-07-26 15:47:01.242 CST [1862] LOG: listening on IPv4 address "0.0.0.0", port 5432 2019-07-26 15:47:01.242 CST [1862] LOG: listening on IPv6 address "::", port 5432 2019-07-26 15:47:01.244 CST [1862] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2019-07-26 15:47:01.282 CST [1862] LOG: redirecting log output to logging collector process 2019-07-26 15:47:01.282 CST [1862] HINT: Future log output will appear in directory "pg_log". done server started ``` 创建extension ``` testdb=# create extension pg_qualstats; CREATE EXTENSION testdb=# show shared_preload_libraries ; shared_preload_libraries --------------------------------- pg_stat_statements,pg_qualstats (1 row) testdb=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+--------------+---------------------------------------------------- blackhole_am | 1.0 | public | template table AM eating all data bloom | 1.0 | public | bloom access method - signature file based index btree_gin | 1.3 | public | support for indexing common datatypes in GIN dblink | 1.2 | public | connect to other PostgreSQL databases from within a database hypopg | 1.1.3 | pgextensions | Hypothetical indexes for PostgreSQL pageinspect | 1.7 | public | inspect the contents of database pages at a low level pg_qualstats | 1.0.8 | public | An extension collecting statistics about quals pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed pg_trgm | 1.4 | public | text similarity measurement and index searching based on trigrams pgcrypto | 1.3 | public | cryptographic functions plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language rum | 1.3 | public | RUM index access method (12 rows) ``` **参数** 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=# ALTER SYSTEM SET pg_qualstats.sample_rate TO 1; ALTER SYSTEM testdb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) ``` 安装sysbench ``` ./autogen.sh ./configure --with-pgsql --with-pgsql-includes=/appdb/atlasdb/pg12beta1/include --with-pgsql-libs=/appdb/atlasdb/pg12beta1/lib make make install ``` 创建用于测试的db ``` testdb=# create database benchdb; CREATE DATABASE ``` 使用tpcc进行测试 ``` [pg12@localhost test]$ git clone https://github.com/Percona-Lab/sysbench-tpcc.git Cloning into 'sysbench-tpcc'... remote: Enumerating objects: 163, done. remote: Total 163 (delta 0), reused 0 (delta 0), pack-reused 163 Receiving objects: 100% (163/163), 52.38 KiB | 0 bytes/s, done. Resolving deltas: 100% (85/85), done. [pg12@localhost test]$ ls sysbench-tpcc [pg12@localhost test]$ cd sysbench-tpcc/ [pg12@localhost sysbench-tpcc]$ pwd /data/test/sysbench-tpcc [pg12@localhost sysbench-tpcc]$ ls [pg12@localhost sysbench-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=pgsql prepare sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3) Initializing worker threads... Creating tables: 2 Creating tables: 1 Adding indexes 1 ... Waiting on tables 30 sec Adding indexes 2 ... Waiting on tables 30 sec loading tables: 1 for warehouse: 1 loading tables: 1 for warehouse: 2 loading tables: 2 for warehouse: 1 loading tables: 2 for warehouse: 2 [pg12@localhost sysbench-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=pgsql run sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 2 Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 1s ] thds: 2 tps: 90.83 qps: 2492.29 (r/w/o: 1135.85/1170.79/185.65) lat (ms,95%): 59.99 err/s 0.00 reconn/s: 0.00 [ 2s ] thds: 2 tps: 87.00 qps: 2462.93 (r/w/o: 1103.97/1184.97/173.99) lat (ms,95%): 64.47 err/s 0.00 reconn/s: 0.00 [ 3s ] thds: 2 tps: 92.03 qps: 2465.76 (r/w/o: 1139.35/1142.35/184.06) lat (ms,95%): 50.11 err/s 1.00 reconn/s: 0.00 [ 4s ] thds: 2 tps: 81.98 qps: 2491.38 (r/w/o: 1126.72/1200.70/163.96) lat (ms,95%): 61.08 err/s 0.00 reconn/s: 0.00 [ 5s ] thds: 2 tps: 88.99 qps: 2519.78 (r/w/o: 1143.90/1197.89/177.98) lat (ms,95%): 51.02 err/s 0.00 reconn/s: 0.00 [ 6s ] thds: 2 tps: 82.01 qps: 2465.44 (r/w/o: 1122.20/1179.21/164.03) lat (ms,95%): 63.32 err/s 1.00 reconn/s: 0.00 [ 7s ] thds: 2 tps: 86.01 qps: 2515.42 (r/w/o: 1154.19/1189.20/172.03) lat (ms,95%): 61.08 err/s 1.00 reconn/s: 0.00 [ 8s ] thds: 2 tps: 89.99 qps: 2420.80 (r/w/o: 1114.91/1125.91/179.99) lat (ms,95%): 50.11 err/s 0.00 reconn/s: 0.00 [ 9s ] thds: 2 tps: 100.01 qps: 2632.33 (r/w/o: 1208.15/1224.15/200.02) lat (ms,95%): 50.11 err/s 1.00 reconn/s: 0.00 [ 10s ] thds: 2 tps: 104.76 qps: 2593.05 (r/w/o: 1188.27/1195.26/209.52) lat (ms,95%): 44.98 err/s 1.00 reconn/s: 0.00 SQL statistics: queries performed: read: 11477 write: 11851 other: 1814 total: 25142 transactions: 906 (90.23 per sec.) queries: 25142 (2503.86 per sec.) ignored errors: 5 (0.50 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 90.2272 time elapsed: 10.0413s total number of events: 906 Latency (ms): min: 2.64 avg: 22.15 max: 80.51 95th percentile: 53.85 sum: 20070.80 Threads fairness: events (avg/stddev): 453.0000/9.00 execution time (avg/stddev): 10.0354/0.00 [pg12@localhost sysbench-tpcc]$ ``` 查询pg_qualstats_indexes,可查看哪些列上没有index,但在这些列上存在谓词 ``` testdb=# \c benchdb You are now connected to database "benchdb" as user "pg12". benchdb=# select * from pg_qualstats_indexes; psql: ERROR: relation "pg_qualstats_indexes" does not exist LINE 1: select * from pg_qualstats_indexes; ^ benchdb=# CREATE EXTENSION hypopg; CREATE EXTENSION benchdb=# CREATE EXTENSION pg_stat_statements ; CREATE EXTENSION benchdb=# CREATE EXTENSION pg_qualstats; CREATE EXTENSION benchdb=# show shared_preload_libraries ; shared_preload_libraries --------------------------------- pg_stat_statements,pg_qualstats (1 row) benchdb=# select * from pg_qualstats_indexes; relid | attnames | possible_types | execution_count -------------+--------------+--------------------------+----------------- customer2 | {c_id} | {brin,btree,hash} | 5 customer2 | {c_last} | {brin,btree,hash,spgist} | 12 customer1 | {c_id} | {brin,btree,hash} | 8 customer1 | {c_last} | {brin,btree,hash,spgist} | 34 orders2 | {o_c_id} | {brin,btree,hash} | 1 order_line2 | {ol_o_id} | {brin,btree,hash} | 8 order_line1 | {ol_o_id} | {brin,btree} | 213 stock1 | {s_quantity} | {brin,btree} | 213 (8 rows) benchdb=# \d customer2 Table "public.customer2" Column | Type | Collation | Nullable | Default ----------------+-----------------------------+-----------+----------+--------- c_id | integer | | not null | c_d_id | smallint | | not null | c_w_id | smallint | | not null | c_first | character varying(16) | | | c_middle | character(2) | | | c_last | character varying(16) | | | c_street_1 | character varying(20) | | | c_street_2 | character varying(20) | | | c_city | character varying(20) | | | c_state | character(2) | | | c_zip | character(9) | | | c_phone | character(16) | | | c_since | timestamp without time zone | | | 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" PRIMARY KEY, btree (c_w_id, c_d_id, c_id) "idx_customer2" btree (c_w_id, c_d_id, c_last, c_first) ``` **参考资料** [pg_qualstats](https://github.com/powa-team/pg_qualstats/tree/1.0.8) [Automatic Index Recommendations in PostgreSQL using pg_qualstats and hypopg ](https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/)