This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select pid, query_start, state, query from pg_stat_activity where pid in ( | |
select pid from pg_locks where granted = true and relation in ( | |
select distinct relation from pg_locks where granted = false) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT *, pg_size_pretty(total_bytes) AS total | |
, pg_size_pretty(index_bytes) AS INDEX | |
, pg_size_pretty(toast_bytes) AS toast | |
, pg_size_pretty(table_bytes) AS TABLE | |
FROM ( | |
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( | |
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME | |
, c.reltuples AS row_estimate | |
, pg_total_relation_size(c.oid) AS total_bytes | |
, pg_indexes_size(c.oid) AS index_bytes |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select pid, now() - query_start, query | |
from pg_stat_activity | |
where state = 'active' and now() - query_start > '5min'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT blocks.pid, | |
(select query from pg_stat_activity where pid = blocks.pid) as blocked_query, | |
blocks.blocking_pid, | |
(select query from pg_stat_activity where pid = blocks.blocking_pid) AS blocking_query | |
FROM ( | |
SELECT pid, unnest(pg_blocking_pids(pid)) AS blocking_pid | |
FROM pg_locks | |
WHERE granted = false | |
) AS blocks; |