PSQL Debug Slow Queries

Useful PostgreSQL Queries and Commands · GitHub

Exploring Query Locks in Postgres - Big elephants

Resources with locks¶

MVCC: Multi Version Concurrency Control - PostgreSQL: Documentation: 9.3: Concurrency Control

pg_locks table - locks held by open transactions

pg_class - catalog of tables, indexes, sequences, views, composite types, TOAST

pg_stat_activity - all server processes - query: the last query the process ran - pid: process ID

find_locks.sql

-- find_locks.sql
SELECT mode, *
FROM pg_locks l
  JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r';

blocking_pids.sql

-- blocking_pids.sql
-- https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres
SELECT pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Lock Monitoring - PostgreSQL wiki

  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;

Long running PSQL queries¶

Finding and killing long running queries on PostgreSQL

```sql TI:"kill_long_running_pids.sql" WITH long_running_queries AS ( SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes' AND state = 'active' ) SELECT pg_cancel_backend(pid) FROM long_running_queries

Result

## Kill a process

```sql
SELECT pg_cancel_backend(__pid__);

If that doesn't work

SELECT pg_terminate_backend(__pid__);

may lead to full database restart

Find large queries¶

SELECT nspname || '.' || relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 10;

-- queries that are currently running
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>'
  AND query NOT ILIKE '%pg_stat_activity%'
  AND query NOT IN ('', chr(10), 'COMMIT')
ORDER BY query_start desc;

find_duplicate_tuples.sql

SELECT (table_name.*)::text, count(*)
FROM table_name
GROUP BY table_name.*
HAVING count(*) > 1;

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE pid = 17081;
order by duration desc

17081 13484 17656

Last update: 2023-02-03