Skip to content

Instantly share code, notes, and snippets.

@xvaara
Last active October 14, 2017 23:13
Show Gist options
  • Save xvaara/e63866503b2bac27c3e56e6eeb6aeb4e to your computer and use it in GitHub Desktop.
Save xvaara/e63866503b2bac27c3e56e6eeb6aeb4e to your computer and use it in GitHub Desktop.
TimescaleDB index stats
SELECT
p.parent,
con.consrc as chunk_constraint,
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || 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, c.oid as tbloid 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
LEFT OUTER JOIN
( SELECT c.relname AS child, p.relname AS parent FROM pg_inherits
JOIN pg_class AS c ON (inhrelid=c.oid)
JOIN pg_class as p ON (inhparent=p.oid) )
AS p
ON t.tablename = p.child
LEFT OUTER JOIN pg_constraint con ON con.conrelid = tbloid and conname like 'constraint_%'
WHERE t.schemaname in ('public','_timescaledb_internal')
ORDER BY 5 desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment