StevenJL

WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans

StevenJL

" map kj to ESC
imap kj <ESC>

" Show line numbers in editor
set number

" Shows file name in terminal window title bar
set title

" When line length exceeds editor width, content does not wrap around

StevenJL

SHOW SHARED_BUFFERS;

#  shared_buffers
# ----------------
#  128MB
#  (1 row)

StevenJL

# See Cache Hit Ratio for a given index
SELECT 
  (heap_blks_hit::decimal / (heap_blks_hit + heap_blks_read)) as cache_hit_ratio
FROM 
  pg_statio_user_indexes
WHERE indexrelname = 'some_high_use_index';

# See overall Cache Hit Ratio across all indexes
SELECT 
  sum(idx_blks_hit)/(sum(idx_blks_read) + sum(idx_blks_hit)) as cache_hit_ratio

StevenJL

SELECT 
  SUM(heap_blks_hit) / (SUM(heap_blks_hit) + SUM(heap_blks_read)) as cache_hit_ratio;
FROM 
  pg_statio_user_tables;

StevenJL

SELECT 
  (heap_blks_hit::decimal / (heap_blks_hit + heap_blks_read)) as cache_hit_ratio
FROM 
  pg_statio_user_tables
WHERE relname = 'large_table';

StevenJL

# See the 10 SELECT queries which touch the most number of rows.
# These queries may benefit from adding indexes to reduce the number
# of rows retrieved/affected.
SELECT
  rows, # rows is the number of rows retrieved/affected by the query
  query
FROM pg_stat_statements
WHERE query iLIKE '%SELECT%'
ORDER BY rows DESC
LIMIT 10;

StevenJL

# See the 10 slowest queries with over a 1000 calls
SELECT query, calls, (total_time/calls)::integer AS avg_time_ms 
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY avg_time_ms DESC
LIMIT 10;

#  query     |  calls  | avg_time_ms
#  ----------+---------+-------------
#  INSERT .. |   52323 |          12

StevenJL

/* See the number of max connections the server supports */
SHOW max_connections;

/* 
  The number of connections being used.
  This includes both client connections
  as well as connections to WAL sender
  processes (used in replication)
*/
SELECT COUNT(*) FROM 

StevenJL

SELECT * FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT ILIKE '%SELECT%'
AND query ILIKE '%some_big_table%'
AND query NOT ILIKE '%pg_stat_activity%';