Customized Statistics Views in PostgreSQL
I always use my customized statistics views shown below because the original ones provide only minimum information.
I show my tools in this post.
Extension
I made an extension to use them easily.
Repository
Download the extension from here:
git clone https://github.com/s-hironobu/pg_stats.git
Installation
Execute make install on the contrib directory, or Put two files, pg_stats.control and pg_stats–1.0.sql, on the ~/share/postgresql/extension/ subdirectory.
Then, execute CREATE EXTENSION pg_stats on all databases you want to use this extension.
TABLES
The view pg_stat_tables is created by joining with pg_stat_user_tables and pg_statio_user_tables, and is added some columns.
Additional columns
| column | description |
|---|---|
idx_scan_ratio |
Ratio [%] of number of index scans to number of total scans, i.e., 100 * idx_scan/(seq_scan + idx_scan) |
hit_ratio |
Ratio [%] of number of buffer hits to total number of read blocks, i.e., 100 * heap_blks_hit/(heap_blks_read + heap_blks_hit) |
ins_ratio |
Ratio [%] of number of INSERT operations to number of total operations, i.e., 100 * n_tup_ins/(n_tup_ins + n_tup_upd + n_tup_del) |
upd_ratio |
Ratio [%] of number of UPDATE operations to number of total operations, i.e., 100 * n_tup_upd/(n_tup_ins + n_tup_upd + n_tup_del) |
del_ratio |
Ratio [%] of number of DELETE operations to number of total operations, i.e., 100 * n_tup_del/(n_tup_ins + n_tup_upd + n_tup_del) |
hot_upd_ratio |
Ratio [%] of number of rows HOT updated to number of rows updated, i.e., 100 * n_tup_hot_upd / n_tup_upd |
table_size |
Disk space used by the specified fork (‘main’, ‘fsm’, ‘vm’) of the specified table |
total_size |
Total disk space used by the specified table, including all indexes and TOAST data |
Example
# SELECT * FROM pg_stat_tables;
schemaname | relname | relid | seq_scan | idx_scan | idx_scan_ratio | seq_tup_read | idx_tup_fetch | heap_blks_read | heap_blks_hit | hit_ratio | n_tup_ins | n_tup_upd | n_tup_del | ins_ratio | upd_ratio | del_ratio | n_tup_hot_upd | hot_upd_ratio | table_size | total_size | last_vacuum | last_autovacuum | vacuum_count | autovacuum_count | last_analyze | last_autoanalyze | analyze_count | autoanalyze_count
------------+------------------+-------+----------+----------+----------------+--------------+---------------+----------------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+---------------+---------------+------------+------------+-------------------------------+-----------------+--------------+------------------+-------------------------------+-------------------------------+---------------+-------------------
public | pgbench_accounts | 16391 | 1 | 60 | 98 | 100000 | 60 | 1644 | 8424 | 83.00 | 100000 | 30 | 0 | 99.97 | 0.03 | 0.00 | 0 | 0 | 13 MB | 15 MB | 2016-09-25 07:33:06.279177+00 | | 1 | 0 | 2016-09-25 07:33:06.32018+00 | 2016-09-25 07:34:18.637476+00 | 1 | 1
public | pgbench_branches | 16394 | 34 | 0 | 0 | 34 | 0 | 5 | 94 | 94.00 | 1 | 30 | 0 | 3.23 | 96.77 | 0.00 | 30 | 100 | 8192 bytes | 56 kB | 2016-09-25 07:48:44.158106+00 | | 4 | 0 | 2016-09-25 07:33:06.263738+00 | | 1 | 0
public | pgbench_history | 16385 | 0 | | | 0 | | 3 | 27 | 90.00 | 30 | 0 | 0 | 100.00 | 0.00 | 0.00 | 0 | NaN | 8192 bytes | 8192 bytes | 2016-09-25 07:33:06.382247+00 | | 1 | 0 | 2016-09-25 07:33:06.382425+00 | | 1 | 0
public | pgbench_tellers | 16388 | 31 | 0 | 0 | 310 | 0 | 5 | 100 | 95.00 | 10 | 30 | 0 | 25.00 | 75.00 | 0.00 | 30 | 100 | 8192 bytes | 56 kB | 2016-09-25 07:48:44.158822+00 | | 4 | 0 | 2016-09-25 07:33:06.267694+00 | | 1 | 0
(4 rows)
INDEXES
The view pg_stat_indexes is created by joining with pg_stat_user_indexes and pg_statio_user_indexes, and is added some columns.
Additional columns
| column | description |
|---|---|
idx_hit_ratio |
Ratio [%] of number of buffer hits to number of all index read, i.e., 100 * idx_blks_hit/(idx_blks_read + idx_blks_hit) |
index_size |
Disk space used by the specified fork (‘main’, ‘fsm’) of the specified index |
Example
# SELECT * FROM pg_stat_indexes;
schemaname | relname | indexrelname | relid | idx_scan | idx_tup_read | idx_tup_fetch | idx_blks_read | idx_blks_hit | idx_hit_ratio | index_size
------------+------------------+-----------------------+-------+----------+--------------+---------------+---------------+--------------+---------------+------------
public | pgbench_accounts | pgbench_accounts_pkey | 16391 | 60 | 90 | 60 | 33 | 153 | 82 | 2208 kB
public | pgbench_branches | pgbench_branches_pkey | 16394 | 0 | 0 | 0 | 2 | 7 | 77 | 16 kB
public | pgbench_tellers | pgbench_tellers_pkey | 16388 | 0 | 0 | 0 | 2 | 4 | 66 | 16 kB
(3 rows)
USERS
The view pg_stat_users shows login time of each user.
| column | description |
|---|---|
dattname |
Name of the database this backend is connected to |
usename |
Name of the user logged into this backend |
pid |
Process ID of this backend |
backend_start |
Time when this process was started, i.e., when the client connected to the server |
login_time |
How long this backend is running |
Example
# SELECT * FROM pg_stat_users;
datname | usename | pid | backend_start | login_time
----------+---------+------+-------------------------------+--------------
sampledb | vagrant | 4972 | 2016-09-26 03:32:06.782921+00 | 00:05:09.257
sampledb | vagrant | 4988 | 2016-09-26 03:35:34.716738+00 | 00:01:41.324
sampledb | vagrant | 4998 | 2016-09-26 03:37:11.236835+00 | 00:00:04.804
sampledb | vagrant | 4999 | 2016-09-26 03:37:11.239046+00 | 00:00:04.801
sampledb | vagrant | 5000 | 2016-09-26 03:37:11.240234+00 | 00:00:04.8
sampledb | vagrant | 5001 | 2016-09-26 03:37:11.241369+00 | 00:00:04.799
(6 rows)
QUERIES
The view pg_stat_queries shows queries and their durations.
| column | description |
|---|---|
dattname |
Name of the database this backend is connected to |
usename |
Name of the user logged into this backend |
pid |
Process ID of this backend |
duration |
How long this query is running |
waiting |
True if this backend is currently waiting on a lock |
query |
Text of this backend’s most recent query |
Example
# SELECT * FROM pg_stat_queries;
datname | usename | pid | duration | waiting | query
----------+---------+------+--------------+---------+----------------------------------------------------------------------------
sampledb | vagrant | 4988 | 00:01:42.832 | f | BEGIN;
sampledb | vagrant | 4998 | 00:00:00.004 | t | UPDATE pgbench_branches SET bbalance = bbalance + -4057 WHERE bid = 1;
sampledb | vagrant | 4999 | 00:00:00.004 | f | END;
sampledb | vagrant | 5000 | 00:00:00.001 | f | UPDATE pgbench_accounts SET abalance = abalance + -1378 WHERE aid = 62207;
sampledb | vagrant | 5001 | 00:00:00.002 | t | UPDATE pgbench_branches SET bbalance = bbalance + 550 WHERE bid = 1;
(5 rows)
LONG TRANSACTIONS
The view pg_stat_long_trx catches long transactions.
| column | description |
|---|---|
pid |
Process ID of this backend. |
waiting |
True if this backend is currently waiting on a lock. |
duration |
How long this transaction is running. |
query |
Text of this backend’s most recent query. |
Example
# SELECT * FROM pg_stat_long_trx;
pid | waiting | duration | query
------+---------+--------------+------------------------------------------------------------------------
4988 | t | 00:00:34.031 | UPDATE test SET id = 10;
5026 | f | 00:05:43.063 | LOCK test;
5054 | t | 00:00:00.003 | UPDATE pgbench_branches SET bbalance = bbalance + -3093 WHERE bid = 1;
5055 | f | 00:00:00.002 | UPDATE pgbench_tellers SET tbalance = tbalance + -1561 WHERE tid = 1;
5056 | f | 00:00:00.001 | SELECT abalance FROM pgbench_accounts WHERE aid = 42689;
5057 | f | 00:00:00.007 | END;
(6 rows)
WAITING LOCKS
The view pg_stat_waiting_locks shows waiting locks.
| column | description |
|---|---|
locktype |
Type of the lockable object |
relname |
Name of the table, index, view, etc. |
pid |
Process ID of this backend |
mode |
Name of the lock mode held or desired by this process |
query |
Text of this backend’s most recent query |
duration |
How long this lock is waiting |
Example
# SELECT * FROM pg_stat_waiting_locks;
locktype | relname | pid | mode | query | duration
---------------+---------+------+------------------+--------+--------------
relation | test | 4988 | RowExclusiveLock | UPDATE | 00:01:02.087
transactionid | | 5061 | ShareLock | UPDATE | 00:00:00.001
transactionid | | 5063 | ShareLock | UPDATE | 00:00:00.006
transactionid | | 5064 | ShareLock | UPDATE | 00:00:00.01
(4 rows)
Appendix
pg_stat_tables
CREATE OR REPLACE VIEW pg_stat_tables
AS
WITH s AS (
SELECT *, cast((n_tup_ins + n_tup_upd + n_tup_del) AS numeric) AS total
FROM pg_stat_user_tables
)
SELECT s.schemaname, s.relname, s.relid,
s.seq_scan, s.idx_scan,
CASE WHEN s.seq_scan + s.idx_scan = 0 THEN 'NaN'::double precision
ELSE round(100 * s.idx_scan/(s.seq_scan+s.idx_scan),2) END AS idx_scan_ratio,
s.seq_tup_read, s.idx_tup_fetch,
sio.heap_blks_read, sio.heap_blks_hit,
CASE WHEN sio.heap_blks_read = 0 THEN 0.00
ELSE round(100*sio.heap_blks_hit/(sio.heap_blks_read+sio.heap_blks_hit),2) END AS hit_ratio,
n_tup_ins, n_tup_upd, n_tup_del,
CASE WHEN s.total = 0 THEN 0.00
ELSE round((100*cast(s.n_tup_ins AS numeric)/s.total) ,2) END AS ins_ratio,
CASE WHEN s.total = 0 THEN 0.00
ELSE round((100*cast(s.n_tup_upd AS numeric)/s.total) ,2) END AS upd_ratio,
CASE WHEN s.total = 0 THEN 0.00
ELSE round((100*cast(s.n_tup_del AS numeric)/s.total) ,2) END AS del_ratio,
s.n_tup_hot_upd,
CASE WHEN s.n_tup_upd = 0 THEN 'NaN'::double precision
ELSE round(100*cast(cast(n_tup_hot_upd as numeric)/n_tup_upd as numeric), 2) END AS hot_upd_ratio,
pg_size_pretty(pg_relation_size(sio.relid)) AS "table_size",
pg_size_pretty(pg_total_relation_size(sio.relid)) AS "total_size",
s.last_vacuum, s.last_autovacuum,
s.vacuum_count, s.autovacuum_count,
s.last_analyze, s.last_autoanalyze,
s.analyze_count, s.autoanalyze_count
FROM s, pg_statio_user_tables AS sio WHERE s.relid = sio.relid ORDER BY relname;
pg_stat_indexes
CREATE OR REPLACE VIEW pg_stat_indexes
AS
SELECT s.schemaname, s.relname, s.indexrelname, s.relid,
s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
sio.idx_blks_read, sio.idx_blks_hit,
CASE WHEN sio.idx_blks_read + sio.idx_blks_hit = 0 THEN 'NaN'::double precision
ELSE round(100 * sio.idx_blks_hit/(sio.idx_blks_read + sio.idx_blks_hit), 2) END AS idx_hit_ratio,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS "index_size"
FROM pg_stat_user_indexes AS s, pg_statio_user_indexes AS sio
WHERE s.relid = sio.relid ORDER BY relname;
pg_stat_users
CREATE OR REPLACE VIEW pg_stat_users
AS
SELECT datname, usename, pid, backend_start,
(current_timestamp - backend_start)::interval(3) AS "login_time"
FROM pg_stat_activity;
pg_stat_queries
CREATE OR REPLACE VIEW pg_stat_queries
AS
SELECT datname, usename, pid,
(current_timestamp - xact_start)::interval(3) AS duration,
waiting, query
FROM pg_stat_activity WHERE pid != pg_backend_pid();
pg_stat_long_trx
CREATE OR REPLACE VIEW pg_stat_long_trx
AS
SELECT pid, waiting,
(current_timestamp - xact_start)::interval(3) AS duration, query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
pg_stat_waiting_locks
CREATE OR REPLACE VIEW pg_stat_waiting_locks
AS
SELECT l.locktype, c.relname, l.pid, l.mode,
substring(a.query, 1, 6) AS query,
(current_timestamp - xact_start)::interval(3) AS duration
FROM pg_locks AS l
LEFT OUTER JOIN pg_stat_activity AS a ON l.pid = a.pid
LEFT OUTER JOIN pg_class AS c ON l.relation = c.oid
WHERE NOT l.granted ORDER BY l.pid;