安装pg_stat_statments插件

这个安装基础是POSTGRESQL数据库已经安装好，想使用Pg_stat_statments插件，以下是测试环境安装的步骤，后续查看慢SQL持续更新中：

1.进入到安装包的里面make &&make install

[root@d2-apptest203 extension]# cd /u01/postgresql-15.3/contrib/pg_stat_statements/
[root@d2-apptest203 pg_stat_statements]# ll
total 244
drwxrwxrwx 2 postgre postgre    74 May  9  2023 expected
-rw-r--r-- 1 postgre postgre  1212 May  9  2023 Makefile
-rw-r--r-- 1 postgre postgre  1246 May  9  2023 pg_stat_statements--1.0--1.1.sql
-rw-r--r-- 1 postgre postgre  1336 May  9  2023 pg_stat_statements--1.1--1.2.sql
-rw-r--r-- 1 postgre postgre  1454 May  9  2023 pg_stat_statements--1.2--1.3.sql
-rw-r--r-- 1 postgre postgre   345 May  9  2023 pg_stat_statements--1.3--1.4.sql
-rw-r--r-- 1 postgre postgre   305 May  9  2023 pg_stat_statements--1.4--1.5.sql
-rw-r--r-- 1 postgre postgre  1427 May  9  2023 pg_stat_statements--1.4.sql
-rw-r--r-- 1 postgre postgre   376 May  9  2023 pg_stat_statements--1.5--1.6.sql
-rw-r--r-- 1 postgre postgre   806 May  9  2023 pg_stat_statements--1.6--1.7.sql
-rw-r--r-- 1 postgre postgre  1744 May  9  2023 pg_stat_statements--1.7--1.8.sql
-rw-r--r-- 1 postgre postgre  2128 May  9  2023 pg_stat_statements--1.8--1.9.sql
-rw-r--r-- 1 postgre postgre  2114 May  9  2023 pg_stat_statements--1.9--1.10.sql
-rw-r--r-- 1 postgre postgre 85071 May  9  2023 pg_stat_statements.c
-rw-r--r-- 1 postgre postgre    48 May  9  2023 pg_stat_statements.conf
-rw-r--r-- 1 postgre postgre   205 May  9  2023 pg_stat_statements.control
-rw-r----- 1 postgre postgre 51512 Sep  9 18:50 pg_stat_statements.o
-rwxr-x--- 1 postgre postgre 49232 Sep  9 18:50 pg_stat_statements.so
drwxrwxrwx 2 postgre postgre    74 May  9  2023 sql

[root@d2-apptest203 pg_stat_statements]#** make && make install**

make -C ../../src/backend generated-headers
make[1]: Entering directory '/u01/postgresql-15.3/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/u01/postgresql-15.3/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 '/u01/postgresql-15.3/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/u01/postgresql-15.3/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 '/u01/postgresql-15.3/src/backend/utils'
make[1]: Leaving directory '/u01/postgresql-15.3/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory '/u01/postgresql-15.3/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/u01/postgresql-15.3/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 '/u01/postgresql-15.3/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/u01/postgresql-15.3/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 '/u01/postgresql-15.3/src/backend/utils'
make[1]: Leaving directory '/u01/postgresql-15.3/src/backend'
/bin/mkdir -p '/u01/postgre/lib/postgresql'
/bin/mkdir -p '/u01/postgre/share/postgresql/extension'
/bin/mkdir -p '/u01/postgre/share/postgresql/extension'
/bin/install -c -m 755  pg_stat_statements.so '/u01/postgre/lib/postgresql/pg_stat_statements.so'
/bin/install -c -m 644 ./pg_stat_statements.control '/u01/postgre/share/postgresql/extension/'
/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.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  '/u01/postgre/share/postgresql/extension/'

2.修改编译后文件的权限
[root@d2-apptest203 pg_stat_statements]# cd /u01/postgre/share/postgresql/extension
[root@d2-apptest203 pg_stat_statements]# chown -R postgre.postgre *

3.切换到postgre用户下，安装插件
[root@d2-apptest203 extension]# su - postgre
Last login: Mon Sep  9 19:04:32 CST 2024 on pts/1
[postgre@d2-apptest203 ~]$ psql postgres
psql (15.3)
Type "help" for help.

postgres=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION

4.查看插件是否安装成功
select * from pg_extension;

postgres=# select * from pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 13756 | plpgsql            |       10 |           11 | f              | 1.0        |           |
 16393 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           |
(2 rows)

5.查看数据库插件的配置
postgres=# select * from pg_settings  where name='shared_preload_libraries' and setting ='pg_stat_statements';
 name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
------+---------+------+----------+------------+------------+---------+---------+--------+---------+---------+----------+----------+-----------+------------+------------+-----------------
(0 rows)

6.修改postgresql.conf配置信息； 配置文件中进行一些配置来使用 pg_stat_statements, shared_preload_libraries：需要将 pg_stat_statements 添加到这个参数中，以便在 PostgreSQL 启动时加载该扩展。修改配置后，你需要重启 PostgreSQL 服务。
shared_preload_libraries = 'pg_stat_statements'

7.查看数据库扩展的配置
postgres=# select * from pg_settings  where name='shared_preload_libraries' and setting ='pg_stat_statements';
           name           |      setting       | unit |                        category                        |                   short_desc                   | extra_desc |  context   |
vartype |       source       | min_val | max_val | enumvals | boot_val |     reset_val      |            sourcefile             | sourceline | pending_restart
--------------------------+--------------------+------+--------------------------------------------------------+------------------------------------------------+------------+------------+-
--------+--------------------+---------+---------+----------+----------+--------------------+-----------------------------------+------------+-----------------
 shared_preload_libraries | pg_stat_statements |      | Client Connection Defaults / Shared Library Preloading | Lists shared libraries to preload into server. |            | postmaster |
string  | configuration file |         |         |          |          | pg_stat_statements | /u01/postgre/data/postgresql.conf |        740 | f
(1 row)

8.查看SQL效率的表
select * from pg_stat_statments;

postgres=# select * from pg_stat_statements;
 userid | dbid | toplevel |       queryid       |                          query                           | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stdd
ev_plan_time | calls |  total_exec_time   |   min_exec_time    |   max_exec_time    |   mean_exec_time   | stddev_exec_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtie
d | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | temp_blk_read_t
ime | temp_blk_write_time | wal_records | wal_fpi | wal_bytes | jit_functions | jit_generation_time | jit_inlining_count | jit_inlining_time | jit_optimization_count | jit_optimization_tim
e | jit_emission_count | jit_emission_time
--------+------+----------+---------------------+----------------------------------------------------------+-------+-----------------+---------------+---------------+----------------+-----
-------------+-------+--------------------+--------------------+--------------------+--------------------+------------------+------+-----------------+------------------+-------------------
--+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------+----------------
----+---------------------+-------------+---------+-----------+---------------+---------------------+--------------------+-------------------+------------------------+---------------------
--+--------------------+-------------------
     10 |    5 | t        | 5833584272004145926 | select * from pg_settings  where name=$1 and setting =$2 |     0 |               0 |             0 |             0 |              0 |
           0 |     1 | 0.9313220000000001 | 0.9313220000000001 | 0.9313220000000001 | 0.9313220000000001 |                0 |    1 |               0 |                0 |
0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0 |
  0 |                   0 |           0 |       0 |         0 |             0 |                   0 |                  0 |                 0 |                      0 |
0 |                  0 |                 0
(1 row)

9.查看缓存的命中率
postgres=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
postgres-# FROM pg_stat_statements ORDER BY total_exec_time DESC;
                          query                           | calls |  total_exec_time   | rows | hit_percent
----------------------------------------------------------+-------+--------------------+------+-------------
 select * from pg_settings  where name=$1 and setting =$2 |     1 | 0.9313220000000001 |    1 |
 select * from pg_stat_statements                         |     1 |           0.184435 |    1 |
(2 rows)

10.清理过多的统计数据
pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

重置ps_stat_statements表
select pg_stat_statements_reset();