til.duyet.net
Search…
Postgres - Index Summary
Here's a sample query to pull the number of rows, indexes, and some info about those indexes for each table. Ditch the pg_size_pretty if you’re on an ancient (<= 8.2) version)
1
SELECT
2
pg_class.relname,
3
pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
4
pg_class.reltuples AS num_rows,
5
count(indexname) AS number_of_indexes,
6
CASE WHEN x.is_unique = 1 THEN 'Y'
7
ELSE 'N'
8
END AS UNIQUE,
9
SUM(case WHEN number_of_columns = 1 THEN 1
10
ELSE 0
11
END) AS single_column,
12
SUM(case WHEN number_of_columns IS NULL THEN 0
13
WHEN number_of_columns = 1 THEN 0
14
ELSE 1
15
END) AS multi_column
16
FROM pg_namespace
17
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
18
LEFT OUTER JOIN
19
(SELECT indrelid,
20
max(CAST(indisunique AS integer)) AS is_unique
21
FROM pg_index
22
GROUP BY indrelid) x
23
ON pg_class.oid = x.indrelid
24
LEFT OUTER JOIN
25
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
26
JOIN pg_class c ON c.oid = x.indrelid
27
JOIN pg_class ipg ON ipg.oid = x.indexrelid )
28
AS foo
29
ON pg_class.relname = foo.ctablename
30
WHERE
31
pg_namespace.nspname='public'
32
AND pg_class.relkind = 'r'
33
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
34
ORDER BY 2;
Copied!
Copy link
Edit on GitHub