PG open locks and queries

pg-locks-queries.sql

SELECT
  pg_locks.locktype, 
  pg_locks.mode, 
  pg_locks.granted, 
  pg_locks.pid, 
  pg_class.relname, 
  now() - pg_stat_activity.query_start "query_age", 
  now() - pg_stat_activity.xact_start "xact_age", 
  pg_stat_activity.query
FROM
  pg_locks
  JOIN
    pg_class ON pg_locks.relation = pg_class.oid
  JOIN
    pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE
  (now() - coalesce(xact_start, query_start)) > '1 second'::interval
ORDER BY
  pg_locks.mode,
  coalesce(xact_start, query_start) ASC
;

SELECT
  DISTINCT ON (
    coalesce(
      now() - pg_stat_activity.xact_start,
      now() - pg_stat_activity.query_start
    ),
    pg_stat_activity.query
  )
  now() - pg_stat_activity.query_start "query_age", 
  now() - pg_stat_activity.xact_start "xact_age",
  pg_stat_activity.state,
  pg_stat_activity.wait_event_type,
  pg_locks.granted,
  pg_locks.mode,
  pg_stat_activity.query,
  substring(
    pg_stat_activity.query
    FROM '%"company_id" = #"[0-9]+#"%'
    FOR '#'
  )::bigint "company_id"
FROM
  pg_stat_activity
  LEFT OUTER JOIN pg_locks USING (pid)
WHERE
  (now() - coalesce(xact_start, query_start)) > '1 second'::interval
ORDER BY
  coalesce(
    now() - pg_stat_activity.xact_start,
    now() - pg_stat_activity.query_start
  ) DESC,
  pg_stat_activity.query
;

SELECT
  pg_dist_node.nodename
FROM
  pg_dist_shard
  JOIN pg_dist_placement USING (shardid)
  JOIN pg_dist_node USING (groupid)
WHERE
  pg_dist_shard.logicalrelid::text = 'companies'
  AND pg_dist_shard.shardid = (SELECT get_shard_id_for_distribution_column('companies', 312938))
  AND pg_dist_node.noderole = 'primary'
;

WITH active_queries AS (
  SELECT
    DISTINCT ON (
      coalesce(
        now() - pg_stat_activity.xact_start,
        now() - pg_stat_activity.query_start
      ),
      pg_stat_activity.query
    )
    now() - pg_stat_activity.query_start "query_age", 
    now() - pg_stat_activity.xact_start "xact_age",
    pg_locks.granted,
    pg_locks.mode,
    pg_stat_activity.query,
    substring(
      pg_stat_activity.query
      FROM '%?"company_id?" = #"[0-9]+#"%'
      FOR '#'
    )::bigint "company_id"
  FROM
    pg_stat_activity
    LEFT OUTER JOIN pg_locks USING (pid)
  WHERE
    (now() - coalesce(xact_start, query_start)) > '1 second'::interval
    AND substring(
      pg_stat_activity.query
      FROM '%"company_id" = #"[0-9]+#"%'
      FOR '#'
    )::bigint IS NOT NULL
  ORDER BY
    coalesce(
      now() - pg_stat_activity.xact_start,
      now() - pg_stat_activity.query_start
    ) DESC,
    pg_stat_activity.query
)
SELECT
  active_queries.*,
  pg_dist_node.nodename
FROM
  active_queries
  JOIN pg_dist_shard
    ON pg_dist_shard.logicalrelid::text = 'companies'
    AND pg_dist_shard.shardid = (SELECT get_shard_id_for_distribution_column('companies', active_queries.company_id))
  JOIN pg_dist_placement USING (shardid)
  JOIN pg_dist_node USING (groupid)
WHERE
  pg_dist_node.noderole = 'primary'
;

WITH active_queries AS (
  SELECT
    DISTINCT ON (
      coalesce(
        now() - pg_stat_activity.xact_start,
        now() - pg_stat_activity.query_start
      ),
      pg_stat_activity.query
    )
    now() - pg_stat_activity.query_start "query_age", 
    now() - pg_stat_activity.xact_start "xact_age",
    pg_locks.granted,
    pg_locks.mode,
    pg_stat_activity.query,
    substring(
      pg_stat_activity.query
      FROM '%?"company_id?" = #"[0-9]+#"%'
      FOR '#'
    )::bigint "company_id"
  FROM
    pg_stat_activity
    LEFT OUTER JOIN pg_locks USING (pid)
  WHERE
    (now() - coalesce(xact_start, query_start)) > '1 second'::interval
    AND substring(
      pg_stat_activity.query
      FROM '%"company_id" = #"[0-9]+#"%'
      FOR '#'
    )::bigint IS NOT NULL
  ORDER BY
    coalesce(
      now() - pg_stat_activity.xact_start,
      now() - pg_stat_activity.query_start
    ) DESC,
    pg_stat_activity.query
),
active_queries_by_node AS (
  SELECT
    active_queries.*,
    pg_dist_node.nodename
  FROM
    active_queries
    JOIN pg_dist_shard
      ON pg_dist_shard.logicalrelid::text = 'companies'
      AND pg_dist_shard.shardid = (SELECT get_shard_id_for_distribution_column('companies', active_queries.company_id))
    JOIN pg_dist_placement USING (shardid)
    JOIN pg_dist_node USING (groupid)
  WHERE
    pg_dist_node.noderole = 'primary'
)
SELECT
  nodename, 
  max(query_age) "max_query_age", 
  max(xact_age) "max_xact_age", 
  count(*)
FROM
  active_queries_by_node
GROUP BY
  nodename
ORDER BY
  coalesce(max(xact_age), max(query_age)) DESC
;

WITH raw_results AS (
  SELECT *
  FROM run_command_on_shards('cloud_objects', $cmd$
    SELECT string_agg(a,'###') as infos
    FROM (
      SELECT (
        jsonb_build_object(
          'schemaname',   schemaname,
          'relname',      relname,
          'indexrelname', indexrelname,
          'size',         pg_relation_size(i.indexrelid),
          'scans',        idx_scan
        )::text
      ) AS a
      FROM  pg_stat_user_indexes ui
      JOIN  pg_index i
      USING (indexrelid)
      WHERE NOT indisunique
      AND   (schemaname || '.' || relname)::regclass = '%s'::regclass
    ) sub
  $cmd$)
),
shard_results AS (
  SELECT
    record.schemaname,
    record.relname,
    record.indexrelname,
    substring(record.indexrelname FROM '#"%[a-z0-9_]+#"_[0-9]{6,}%' FOR '#') "indexname",
    record.size,
    record.scans
  FROM
    raw_results,
    unnest(regexp_split_to_array(raw_results.result, '###')) items (item),
    jsonb_to_record(item::jsonb) AS record(
      schemaname text, 
      relname text, 
      indexrelname text,
      size bigint,
      scans bigint
    )
)
SELECT
  shard_results.indexname,
  sum(shard_results.scans) "total_scans",
  pg_size_pretty(sum(shard_results.size)) "total_size"
FROM
  shard_results
GROUP BY
  shard_results.indexname
ORDER BY
  sum(shard_results.scans)
;


WITH raw_results AS (
  SELECT *
  FROM run_command_on_shards('correspondences', $cmd$
    SELECT string_agg(a,'###') as infos
    FROM (
      SELECT (
        jsonb_build_object(
          'schemaname',   schemaname,
          'relname',      relname,
          'indexrelname', indexrelname,
          'size',         pg_relation_size(i.indexrelid),
          'scans',        idx_scan
        )::text
      ) AS a
      FROM  pg_stat_user_indexes ui
      JOIN  pg_index i
      USING (indexrelid)
      WHERE NOT indisunique
      AND   (schemaname || '.' || relname)::regclass = '%s'::regclass
    ) sub
  $cmd$)
),
shard_results AS (
  SELECT
    record.schemaname,
    record.relname,
    record.indexrelname,
    #"%[a-z0-9_]+#"_[0-9]{6,}%' FOR '#') "indexname",
    record.size,
    record.scans
  FROM
    raw_results,
    unnest(regexp_split_to_array(raw_results.result, '###')) items (item),
    jsonb_to_record(item::jsonb) AS record(
      schemaname text, 
      relname text, 
      indexrelname text,
      size bigint,
      scans bigint
    )
)
SELECT
  shard_results.indexname,
  sum(shard_results.scans) "total_scans",
  pg_size_pretty(sum(shard_results.size)) "total_size"
FROM
  shard_results
GROUP BY
  shard_results.indexname
ORDER BY
  sum(shard_results.scans)
;