Postgres - Index Summary

til.duyet.net
Search…
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!
Source: https://wiki.postgresql.org/wiki/Index_Maintenance​
Amazon Redshift Utilities
Postgres - List tables
Last modified 1yr ago
Copy link
Edit on GitHub