How to use PostgreSQL's pg_stat_statements

This article focuses on "how to use PostgreSQL's pg_stat_statements". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use PostgreSQL's pg_stat_statements.

Pg_stat_statements provides a means to track all SQL statements.

Go to the pg_stat_statements directory and execute make/make install

[pg12@localhost pg_stat_statements] $lsexpected pg_stat_statements--1.4--1.5.sql pg_stat_statements.controlMakefile pg_stat_statements--1.4.sql pg_stat_statements.opg_stat_statements--1.0--1.1.sql pg_stat_statements--1.5--1.6.sql pg_stat_statements .sopg _ stat_statements--1.1--1.2.sql pg_stat_statements--1.6--1.7.sql pg_stat_statements--unpackaged--1.0.sqlpg_stat_statements--1.2--1.3.sql pg_stat_statements.c sqlpg_stat_statements--1.3--1.4.sql pg_stat_ statements.confs [pg12 @ localhost pg_stat_statements] $makemake-C.. / .. / src/backend generated-headersmake [1]: Entering directory `/ data/source/postgresql-12beta1/src/backend'make-C catalog distprep generated-header-symlinksmake [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-symlinksmake [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 installmake-C.. /. / src/backend generated-headersmake [1]: Entering directory `/ data/source/postgresql-12beta1/src/backend'make-C catalog distprep generated-header-symlinksmake [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 -symlinksmake [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] $

You need to add a preloaded shared link library to the configuration file before using it, otherwise an error will be reported:

Testdb=# create extension pg_stat_statements;CREATE EXTENSIONtestdb=# select * from pg_stat_statements;psql: ERROR: pg_stat_statements must be loaded via shared_preload_libraries

Modify the shared_preload_libraries parameter in the postgresql.conf file:

[pg12@localhost pg12db1] $grep 'shared_preload' postgresql.conf shared_preload_libraries =' pg_stat_statements' # (change requires restart)

Note: after modifying this parameter, you need to restart the database instance.

Here is a simple use:

Testdb=#\ xExpanded display is on.testdb=# select * from pg_stat_statements -[RECORD 1]-+-- userid | 10dbid | 16384queryid | 6343734953611294604query | select * from pg_stat_statementscalls | 2total_time | 0.618297min_time | 0.303231max_time | | 0.315066mean_time | 0.3091485stddev_time | 0.00591750000000002rows | 2shared_blks_hit | 0shared_blks_read | 0shared_blks_dirtied | 0shared_blks_written | 0local_blks_hit | 0local_blks_read | 0local_blks_dirtied | 0local_blks_written | 0temp_blks_read | 0temp_blks_written | 0blk_read_time | 0blk_write | _ time | 0-[RECORD 2]-+-userid | 10dbid | 16384queryid |-2568307067200732111query | select * from t1calls | 1total_time | 876.219199min_time | 876. 219199max_time | 876.219199mean_time | 876.219199stddev_time | 0rows | 1100000shared_blks_hit | 0shared_blks_read | 5946shared_blks_dirtied | 0shared_blks_written | 0local_blks_hit | 0local_blks_read | 0local_blks_dirtied | 0local_blks_written | 0temp_blks_read | 0temp_blks_written | 0blk_read_time | 0blk_write_time | 0

Query summary statistics

Testdb=# SELECT query, (total_time/ 1000 / 60) as total, (total_time/calls) as avgFROM 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.313922333333336 (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 avgtestdb-# 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 in the query condition, and the actual value is replaced with a placeholder ($x).

At this point, I believe you have a deeper understanding of "how to use PostgreSQL's pg_stat_statements". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!