Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start 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;

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state NOT LIKE '%idle%'
AND age(clock_timestamp(), query_start) > interval '1 min'
ORDER BY 2 DESC NULLS LAST;

select now()-xact_start, query, pid from pg_stat_activity where (now()-xact_start)>'5minute'::interval and state != 'idle' order by 1 desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

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

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

-- search for locks
SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.objid,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

SELECT
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.granted              AS other_granted
FROM
    pg_catalog.pg_locks AS waiting
JOIN
    pg_catalog.pg_stat_activity AS waiting_stm
    ON (
        waiting_stm.pid = waiting.pid
    )
JOIN
    pg_catalog.pg_locks AS other
    ON (
        (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        )
        OR waiting.transactionid = other.transactionid
    )
JOIN
    pg_catalog.pg_stat_activity AS other_stm
    ON (
        other_stm.pid = other.pid
    )
WHERE
    NOT waiting.granted
AND
    waiting.pid <> other.pid;
                                                                                               
-- search for mutual locks
SELECT blocked_locks.pid     AS blocked_pid,
     blocked_activity.usename  AS blocked_user,
     blocking_locks.pid     AS blocking_pid,
     blocking_activity.usename AS blocking_user,
     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
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

-- dead tuples
select relname, n_dead_tup, last_vacuum, last_autovacuum from
pg_catalog.pg_stat_all_tables
where n_dead_tup > 0
order by n_dead_tup desc;

-- table spaces
SELECT relname AS "relation",
   pg_size_pretty(
       pg_total_relation_size(C.oid)
   )   AS "total_size",
   pg_total_relation_size(C.oid) / (1024 * 1024)
FROM pg_class C
     LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC;

-- check that indexes are valid
SELECT n.nspname, c.relname, i.indisvalid
FROM pg_catalog.pg_class c,
     pg_catalog.pg_namespace n,
     pg_catalog.pg_index i
WHERE i.indexrelid = c.oid
  AND c.relnamespace = n.oid
  AND n.nspname != 'pg_catalog'
  AND n.nspname != 'information_schema'
  AND n.nspname != 'pg_toast';

-- query stats
SELECT rolname
     , queryid
     , round(total_time :: numeric, 2)                                     AS total_time
     , calls
     , pg_size_pretty((shared_blks_hit + shared_blks_read) * 8192 - reads) AS memory_hit
     , pg_size_pretty(reads)                                               AS disk_read
     , pg_size_pretty(writes)                                              AS disk_write
     , round(blk_read_time :: numeric, 2)                                  AS blk_read_time
     , round(blk_write_time :: numeric, 2)                                 AS blk_write_time
     , round(user_time :: numeric, 2)                                      AS user_time
     , round(system_time :: numeric, 2)                                    AS system_time
     , s.*
FROM pg_stat_statements s
       JOIN pg_stat_kcache() k USING (userid, dbid, queryid)
       JOIN pg_database d ON s.dbid = d.oid
       JOIN pg_roles r ON r.oid = userid
WHERE datname != 'postgres'
  AND datname NOT LIKE 'template%'
ORDER BY s.total_time DESC;

-- indexes usage
with foreign_key_indexes as (
    select i.indexrelid
    from pg_catalog.pg_constraint c
             join lateral unnest(c.conkey) with ordinality as u(attnum, attposition) on true
             join pg_catalog.pg_index i on i.indrelid = c.conrelid and (c.conkey::int[] <@ i.indkey::int[])
    where c.contype = 'f'
)
select psui.relid::regclass::text as table_name,
       psui.indexrelid::regclass::text as index_name,
       pg_relation_size(i.indexrelid) as index_size,
       psui.idx_scan as index_scans
from pg_catalog.pg_stat_user_indexes psui
         join pg_catalog.pg_index i on psui.indexrelid = i.indexrelid
where psui.schemaname = 'public'
  and not i.indisunique
  and i.indexrelid not in (select * from foreign_key_indexes) /*retain indexes on foreign keys*/
order by psui.relname, pg_relation_size(i.indexrelid) desc;

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

-- grant permission
GRANT USAGE ON SCHEMA public TO support;

-- jdbc connect documentation
-- https://jdbc.postgresql.org/documentation/head/connect.html
         
https://explain.dalibo.com/ -- visualize query planes