Schema Inspection Queries

The following queries can be helpful when looking for bottlenecks for slow queries. These are largely not specific to Concourse.

Show in-flight queries

SELECT pid, age(clock_timestamp(), query_start), usename, regexp_replace(query, '[\s\t\n]+', ' ', 'g')
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY age desc;

...grouped by query, showing longest-running time:

SELECT count(*), coalesce(max(age(clock_timestamp(), query_start)), '0 seconds'::interval) as age, regexp_replace(query, '[\s\t\n]+', ' ', 'g') as query
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
GROUP BY query
ORDER BY age desc;

...currently active running queries:

SELECT count(*), coalesce(max(age(clock_timestamp(), query_start)), '0 seconds'::interval) as age, regexp_replace(query, '[\s\t\n]+', ' ', 'g') as query
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state = 'active'
GROUP BY query 
ORDER BY age DESC;

Show what queries are blocked and what they are blocked on

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;

Show the top N slowest queries

SELECT
  total_time / calls AS avg_time,
  calls,
  total_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
  regexp_replace(query, '[\s\t\n]+', ' ', 'g')
FROM pg_stat_statements
WHERE query NOT LIKE '%EXPLAIN%'
AND query NOT LIKE '%INDEX%'
ORDER BY avg_time DESC LIMIT 5;

Show foreign keys without an index

CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
  SELECT ARRAY(
      SELECT $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
  )
' language sql;

SELECT conrelid::regclass, conname, reltuples::bigint
FROM pg_constraint
JOIN pg_class ON (conrelid = pg_class.oid)
WHERE contype = 'f'
AND NOT EXISTS (
  SELECT 1
  FROM pg_index
  WHERE indrelid = conrelid
  AND pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
)
ORDER BY reltuples DESC;

Show the size and number of hits for each index

SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
  CASE WHEN indisunique THEN 'Y'
    ELSE 'N'
  END AS UNIQUE,
  idx_scan AS number_of_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN (
  SELECT
    c.relname AS ctablename,
    ipg.relname AS indexname,
    x.indnatts AS number_of_columns,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    indexrelname,
    indisunique
  FROM pg_index x
  JOIN pg_class c ON c.oid = x.indrelid
  JOIN pg_class ipg ON ipg.oid = x.indexrelid
  JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
) AS foo ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1, 2;

Show top 5 tables in size

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2 DESC limit 5