Stat 01
SELECT
s.schemaname, s.relname,
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
FROM (SELECT schemaname,relname,
n_tup_ins, n_tup_upd, n_tup_del,
cast((n_tup_ins + n_tup_upd + n_tup_del) AS numeric) AS total
FROM pg_stat_user_tables) AS s
ORDER BY relname;
schemaname | relname | ins_ratio | upd_ratio | del_ratio
------------+------------------+-----------+-----------+-----------
public | pgbench_accounts | 99.70 | 0.30 | 0.00
public | pgbench_branches | 0.33 | 99.67 | 0.00
public | pgbench_history | 99.35 | 0.65 | 0.00
public | pgbench_tellers | 3.17 | 96.83 | 0.00
public | userlist | 50.04 | 0.25 | 49.71
(5 rows)
SELECT schemaname, relname, heap_blks_read, heap_blks_hit,
CASE WHEN heap_blks_read = 0 THEN 0.00
ELSE round(100*heap_blks_read/(heap_blks_read+heap_blks_hit),2)
END AS hit_ratio
FROM pg_statio_user_tables
ORDER BY relname;
sampledb-# ORDER BY relname;
schemaname | relname | heap_blks_read | heap_blks_hit | hit_ratio
------------+------------------+----------------+---------------+-----------
public | pgbench_accounts | 14738 | 25730 | 36.00
public | pgbench_branches | 25 | 769 | 3.00
public | pgbench_history | 25 | 359 | 6.00
public | pgbench_tellers | 25 | 747 | 3.00
public | userlist | 10 | 1289 | 0.00
(5 rows)
SELECT
schemaname, relname, seq_scan,
CASE WHEN idx_scan IS NULL THEN 0 ELSE idx_scan END AS idx_scan,
CASE WHEN idx_scan IS NULL THEN 0.00
ELSE round(100 * idx_scan/(seq_scan + idx_scan), 2) END AS idx_scan_ratio
FROM pg_stat_user_tables
ORDER BY relname;
schemaname | relname | seq_scan | idx_scan | idx_scan_ratio
------------+------------------+----------+----------+----------------
public | pgbench_accounts | 0 | 2230 | 100.00
public | pgbench_branches | 1024 | 0 | 0.00
public | pgbench_history | 0 | 0 | 0.00
public | pgbench_tellers | 890 | 0 | 0.00
public | userlist | 24 | 18611 | 99.00
(5 rows)
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY relname;
schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
------------+------------------+-----------------------+----------+--------------+---------------
public | pgbench_accounts | pgbench_accounts_pkey | 2220 | 2919 | 2220
public | pgbench_branches | pgbench_branches_pkey | 0 | 0 | 0
public | pgbench_tellers | pgbench_tellers_pkey | 0 | 0 | 0
public | userlist | userlist_pkey | 18611 | 18611 | 18611
public | userlist | userlist_attr_idx | 0 | 0 | 0
(5 rows)
SELECT schemaname, relname, n_tup_upd, n_tup_hot_upd,
round(100*cast(cast(n_tup_hot_upd as numeric)/n_tup_upd as numeric), 2)
AS hot_upd_ratio
FROM pg_stat_user_tables WHERE n_tup_upd > 0
ORDER BY relname;
schemaname | relname | n_tup_upd | n_tup_hot_upd | hot_upd_ratio
------------+------------------+-----------+---------------+---------------
public | pgbench_accounts | 395 | 21 | 5.32
public | pgbench_branches | 395 | 395 | 100.00
public | pgbench_history | 5 | 5 | 100.00
public | pgbench_tellers | 395 | 395 | 100.00
public | userlist | 15 | 15 | 100.00
(5 rows)
sampledb=# \d pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Modifiers
----------+---------------+-----------
aid | integer | not null
bid | integer |
abalance | integer |
filler | character(84) |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
SELECT schemaname, relname,
pg_size_pretty(pg_relation_size(relid)) AS "table size",
pg_size_pretty(pg_total_relation_size(relid)) AS "total size"
FROM pg_statio_user_tables
ORDER BY relname;
schemaname | relname | table size | total size
------------+------------------+------------+------------
public | pgbench_accounts | 13 MB | 15 MB
public | pgbench_branches | 8192 bytes | 56 kB
public | pgbench_history | 0 bytes | 0 bytes
public | pgbench_tellers | 8192 bytes | 56 kB
public | userlist | 8192 bytes | 48 kB
(5 rows)
SELECT schemaname, relname, indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS "index size"
FROM pg_statio_user_indexes
ORDER BY relname;
schemaname | relname | indexrelname | index size
------------+------------------+-----------------------+------------
public | pgbench_accounts | pgbench_accounts_pkey | 2208 kB
public | pgbench_branches | pgbench_branches_pkey | 16 kB
public | pgbench_tellers | pgbench_tellers_pkey | 16 kB
public | userlist | userlist_pkey | 32 kB
public | userlist | userlist_attr_idx | 40 kB
(5 rows)
SELECT datname,usename, pid, backend_start,
(current_timestamp - backend_start)::interval(3) AS "login time"
FROM pg_stat_activity;
datname | usename | pid | backend_start | login time
----------+----------+-------+-------------------------------+--------------
postgres | postgres | 2385 | 2014-02-15 02:30:05.1476+09 | 00:22:00.204
sampledb | postgres | 30413 | 2014-02-15 01:53:39.242691+09 | 00:58:26.109
sampledb | postgres | 5549 | 2014-02-15 02:52:03.047571+09 | 00:00:02.304
sampledb | postgres | 5550 | 2014-02-15 02:52:03.056086+09 | 00:00:02.295
sampledb | postgres | 5551 | 2014-02-15 02:52:03.070362+09 | 00:00:02.281
sampledb | postgres | 5552 | 2014-02-15 02:52:03.090171+09 | 00:00:02.261
(6 rows)
SELECT datname, usename, pid,
(current_timestamp - xact_start)::interval(3) AS duration,
waiting, query
FROM pg_stat_activity WHERE pid != pg_backend_pid();
-[ RECORD 1 ]--------------------------------------------------------------------------
datname | postgres
usename | postgres
pid | 2385
duration |
waiting | f
query | SELECT statsinfo.sample()
-[ RECORD 2 ]--------------------------------------------------------------------------
datname | postgres
usename | postgres
pid | 5649
duration |
waiting | f
query | INSERT INTO statsrepo.autoanalyze VALUES
($1, $2::timestamptz - interval '1sec' * $6, $3, $4, $5, $6)
-[ RECORD 3 ]--------------------------------------------------------------------------
datname | sampledb
usename | postgres
pid | 5728
duration | 00:00:00.004
waiting | f
query | UPDATE pgbench_accounts SET abalance = abalance + 4944 WHERE aid = 61886;
-[ RECORD 4 ]--------------------------------------------------------------------------
datname | sampledb
usename | postgres
pid | 5730
duration | 00:00:00.008
waiting | f
query | UPDATE pgbench_branches SET bbalance = bbalance + -4699 WHERE bid = 1;
-[ RECORD 5 ]--------------------------------------------------------------------------
datname | sampledb
usename | postgres
pid | 5731
duration | 00:00:00.005
waiting | f
query | UPDATE pgbench_tellers SET tbalance = tbalance + 3034 WHERE tid = 7;
Version 9.2 or later,
SELECT pid, waiting, (current_timestamp - xact_start)::interval(3)
AS duration, query FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
pid | waiting | duration | query
--------+---------+--------------+---------------------------------------------------------
21968 | f | 00:27:21.195 | in transaction
21972 | t | 00:26:50.01 | UPDATE pgbench_accounts SET abalance = 1 WHERE aid = 1;
(2 rows)
Version 9.1 or earler,
SELECT pid, waiting, (current_timestamp - xact_start)::interval(3)
AS duration, current_query FROM pg_stat_activity
WHERE procpid <> pg_backend_pid();
pid | waiting | duration | query
--------+---------+--------------+---------------------------------------------------------
21968 | f | 00:27:21.195 | in transaction
21972 | t | 00:26:50.01 | UPDATE pgbench_accounts SET abalance = 1 WHERE aid = 1;
(2 rows)
Version 9.2 or later,
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;
locktype | relname | pid | mode | query | duration
---------------+------------------+-------+---------------+--------+--------------
transactionid | | 21972 | ShareLock | UPDATE | 00:26:54.681
transactionid | | 22073 | ShareLock | UPDATE | 00:00:00.062
transactionid | | 22079 | ShareLock | UPDATE | 00:00:00.061
tuple | pgbench_branches | 22080 | ExclusiveLock | UPDATE | 00:00:00.057
(4 rows)
Version 9.1 or earler,
SELECT l.locktype, c.relname, l.procpid, l.mode,
substring(a.current_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.procpid = a.procpid
LEFT OUTER JOIN pg_class AS c ON l.relation = c.oid
WHERE NOT l.granted ORDER BY l.procpid;
locktype | relname | pid | mode | query | duration
---------------+------------------+-------+---------------+--------+--------------
transactionid | | 21972 | ShareLock | UPDATE | 00:26:54.681
transactionid | | 22073 | ShareLock | UPDATE | 00:00:00.062
transactionid | | 22079 | ShareLock | UPDATE | 00:00:00.061
tuple | pgbench_branches | 22080 | ExclusiveLock | UPDATE | 00:00:00.057
(4 rows)