Rajaneeshs

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

Rajaneeshs

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

Rajaneeshs

# 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

Rajaneeshs

# 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;

Rajaneeshs

  SELECT blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple