Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- show *blocked* queries (not the same as idle)
 SELECT
  pid,
 usename,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

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

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- all connected database users
select * from pg_stat_activity where query is not null;

-- all databases and their sizes
select * from pg_user;

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

-- 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 + 0.0 ) percent_of_times_index_used, n_live_tup rows_in_table, seq_scan
FROM pg_stat_user_tables where seq_scan + idx_scan  > 0 and n_live_tup > 100 and seq_scan > 100 and 100 * idx_scan / (seq_scan + idx_scan + 0.0 ) < 99.0  
ORDER BY 2 desc, 3 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;

-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql

-- Import dump into existing database
$ psql -d newdb -f dump.sql

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

-- list views
select u.view_schema as schema_name,
       u.view_name,
       u.table_schema as referenced_table_schema,
       u.table_name as referenced_table_name,
       v.view_definition
from information_schema.view_table_usage u
join information_schema.views v 
     on u.view_schema = v.table_schema
     and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
order by u.view_schema,
         u.view_name;
         
-- List tables ending with numbers not targeted by views
select table_schema, table_name from information_schema.tables where table_name similar to  '%[0-9]'  except select 
       u.table_schema as referenced_table_schema,
       u.table_name as referenced_table_name
from information_schema.view_table_usage u
join information_schema.views v 
     on u.view_schema = v.table_schema
     and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')

-- List tables which have not been analyzed since a given date
SELECT schemaname || '.' || relname, last_autoanalyze, last_analyze 
FROM pg_stat_all_tables 
where coalesce(greatest(last_autoanalyze, last_analyze), '0001-01-01 00:00:00-04') <= '2023-02-08 00:00:00-04' 
      and relname not similar to '%[0-9]_part'  order by 1,3,2;
      
-- list tables matching a pattern used for partitions but are not partitions
select nspname || '.' || relname 
from pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
where not relispartition 
      and c.oid not in (select indexrelid from pg_catalog.pg_index)
      and relname similar to '%[0-9]_part';