Postgres SQL queries

performance.sql

SELECT
  calls,
  rows,
  ROUND((total_time::numeric / 1000 / 60), 4) AS total_min,
  -- newer versions of PostgreSQL have mean_exec_time field, don't need to calculate
  --ROUND((total_exec_time / 1000 / calls)::numeric, 4) AS average_secs,
  ROUND(mean_time::numeric / 1000 / 60, 4) AS average_min,
  ROUND(min_time::numeric / 1000 / 60, 4) AS min_min,
  ROUND(max_time::numeric / 1000 / 60, 4) AS max_min,
  ROUND(stddev_time::numeric / 1000 / 60, 4) AS stddev_min,
  query
FROM
  pg_stat_statements
ORDER BY
  average_min DESC,
  calls DESC,
  rows DESC
LIMIT 100;

postgres_query.sql

CREATE EXTENSION pg_stat_statements;

SELECT (TOTAL_TIME / 1000 / 60) AS TOTAL_MIN,
	MEAN_TIME AS AVG_MS,
	CALLS,
	QUERY
FROM PG_STAT_STATEMENTS
ORDER BY 1 DESC
LIMIT 500;

-- Longest running queries

SELECT *
FROM PG_STAT_STATEMENTS
ORDER BY TOTAL_TIME / CALLS DESC
LIMIT 10;

-- top 10 tables with size

SELECT SCHEMANAME AS TABLE_SCHEMA,
	RELNAME AS TABLE_NAME,
	PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(RELID)) AS TOTAL_SIZE,
	PG_SIZE_PRETTY(PG_RELATION_SIZE(RELID)) AS DATA_SIZE,
	PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(RELID) - PG_RELATION_SIZE(RELID)) AS EXTERNAL_SIZE
FROM PG_CATALOG.PG_STATIO_USER_TABLES
ORDER BY PG_TOTAL_RELATION_SIZE(RELID) DESC, PG_RELATION_SIZE(RELID) DESC
LIMIT 10;

-- all tables and their size, with/without indexes

SELECT DATNAME,
	PG_SIZE_PRETTY(PG_DATABASE_SIZE(DATNAME))
FROM PG_DATABASE
ORDER BY PG_DATABASE_SIZE(DATNAME) DESC;


-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- all database users
select * from pg_stat_activity where current_query not like '<%';

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

-- see all locks
SELECT
	t.relname,
	l.locktype,
	page,
	virtualtransaction,
	pid,
	mode,
	granted
FROM
	pg_locks l,
	pg_stat_all_tables t
WHERE
	l.relation = t.relid
ORDER BY
	relation ASC;
	
-- Most running quer
SELECT QUERY,
	CALLS,
	TOTAL_TIME,
	ROWS,
	100.0 * SHARED_BLKS_HIT / NULLIF(SHARED_BLKS_HIT + SHARED_BLKS_READ, 0) AS HIT_PERCENT
FROM PG_STAT_STATEMENTS
ORDER BY TOTAL_TIME DESC
LIMIT 10;

-- Get all table sizes

SELECT SCHEMA_NAME,
	RELNAME,
	PG_SIZE_PRETTY(TABLE_SIZE) AS SIZE,
	TABLE_SIZE
FROM
	(SELECT PG_CATALOG.PG_NAMESPACE.NSPNAME AS SCHEMA_NAME,
			RELNAME,
			PG_RELATION_SIZE(PG_CATALOG.PG_CLASS.OID) AS TABLE_SIZE
		FROM PG_CATALOG.PG_CLASS
		JOIN PG_CATALOG.PG_NAMESPACE ON RELNAMESPACE = PG_CATALOG.PG_NAMESPACE.OID) T
WHERE SCHEMA_NAME NOT LIKE 'pg_%'
ORDER BY TABLE_SIZE DESC ;

-- Show unused indexes
SELECT RELNAME AS TABLE_NAME,
	INDEXRELNAME AS INDEX_NAME,
	IDX_SCAN,
	IDX_TUP_READ,
	IDX_TUP_FETCH,
	PG_SIZE_PRETTY(PG_RELATION_SIZE(INDEXRELNAME::REGCLASS))
FROM PG_STAT_ALL_INDEXES
WHERE SCHEMANAME = 'public'
	AND IDX_SCAN = 0
	AND IDX_TUP_READ = 0
	AND IDX_TUP_FETCH = 0
ORDER BY PG_RELATION_SIZE(INDEXRELNAME::REGCLASS) DESC;