pg_stat_statements提供了一種跟蹤所有SQL語句的手段。

進入pg_stat_statements目錄,執行make/make install
```
[pg12@localhost pg_stat_statements]$ ls
expected                          pg_stat_statements--1.4--1.5.sql  pg_stat_statements.control
Makefile                          pg_stat_statements--1.4.sql       pg_stat_statements.o
pg_stat_statements--1.0--1.1.sql  pg_stat_statements--1.5--1.6.sql  pg_stat_statements.so
pg_stat_statements--1.1--1.2.sql  pg_stat_statements--1.6--1.7.sql  pg_stat_statements--unpackaged--1.0.sql
pg_stat_statements--1.2--1.3.sql  pg_stat_statements.c              sql
pg_stat_statements--1.3--1.4.sql  pg_stat_statements.conf
[pg12@localhost pg_stat_statements]$ make
make -C ../../src/backend generated-headers
make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils'
make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend'

[pg12@localhost pg_stat_statements]$ make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils'
make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/lib/postgresql'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/install -c -m 755  pg_stat_statements.so '/appdb/xdb/pg12beta1/lib/postgresql/pg_stat_statements.so'
/usr/bin/install -c -m 644 ./pg_stat_statements.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'
/usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql ./pg_stat_statements--unpackaged--1.0.sql  '/appdb/xdb/pg12beta1/share/postgresql/extension/'
[pg12@localhost pg_stat_statements]$ 
```
使用前需在配置檔案中新增預載入共享連結庫,否則會報錯:
```
testdb=# create extension pg_stat_statements;
CREATE EXTENSION
testdb=# select * from pg_stat_statements;
psql: ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
```
在postgresql.conf檔案中修改shared_preload_libraries引數:
```
[pg12@localhost pg12db1]$ grep 'shared_preload' postgresql.conf 
shared_preload_libraries = 'pg_stat_statements'	# (change requires restart)
```
注意:修改該引數後需要重啟資料庫例項.

```
查詢彙總統計資訊
```
testdb=# SELECT query, 
  (total_time / 1000 / 60) as total, 
  (total_time/calls) as avg
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;
                query                |         total          |         avg         
-------------------------------------+------------------------+---------------------
 SELECT                             +| 1.5928199999999998e-05 |            0.318564
   (total_time / $1 / $2) as total, +|                        | 
   (total_time/calls) as avg,       +|                        | 
   query                            +|                        | 
 FROM pg_stat_statements            +|                        | 
 ORDER BY 1 DESC                    +|                        | 
 LIMIT $3                            |                        | 
 SELECT                             +| 4.9146500000000005e-06 |            0.294879
   (total_time / $1 / $2) as total, +|                        | 
   (total_time/calls) as avg        +|                        | 
 FROM pg_stat_statements            +|                        | 
 ORDER BY 1 DESC                    +|                        | 
 LIMIT $3                            |                        | 
 select * from t1                    |   0.014603653316666666 |          876.219199
 select * from pg_stat_statements    | 1.5696116666666667e-05 | 0.31392233333333336
(4 rows)
...
testdb=# 
testdb=# SELECT 
testdb-#   query, 
testdb-#   round((total_time / 1000 / 60)::numeric,4) as total, 
testdb-#   round((total_time/calls)::numeric,4) as avg
testdb-# FROM pg_stat_statements 
testdb-# WHERE query like '%t1%'
testdb-# ORDER BY 1 DESC 
testdb-# LIMIT 100;
             query              | total  |   avg    
--------------------------------+--------+----------
 select * from t1 where id = $1 | 0.0022 | 129.9684
 select * from t1 where id < $1 | 0.0020 | 121.3334
 select * from t1               | 0.0146 | 876.2192
(3 rows)
```
查詢條件中id = xx,實際值被替換為佔位符($x).

**參考資料**
[PG doc : pg_stat_statements](http://www.postgres.cn/docs/9.6/pgstatstatements.html)

PostgreSQL DBA(63) - Extension(pg_stat_statements)

相關文章