Customized Statistics Views in PostgreSQL

Posted by on Monday, September 26, 2016

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;