pgsql-scripts/index-stats.sql at main · relationaldba/pgsql-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
-- Details of all existing indexes
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,
    indisunique,
    indisprimary,
    indisclustered,
    indisvalid,
    indisready,
    indislive
FROM
    pg_tables AS t
    LEFT OUTER JOIN pg_class AS 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,
            x.indisunique,
            x.indisprimary,
            x.indisclustered,
            x.indisvalid,
            x.indisready,
            x.indislive
        FROM
            pg_index AS x
            JOIN pg_class AS c ON c.oid = x.indrelid
            JOIN pg_class AS ipg ON ipg.oid = x.indexrelid
            JOIN pg_stat_all_indexes AS psai ON x.indexrelid = psai.indexrelid) AS foo ON t.tablename = foo.ctablename
WHERE
    t.schemaname = 'public'
    -- AND t.tablename = 'hfj_res_ver'
ORDER BY
    1,
    2;

-- Index status
SELECT
    nsp.nspname AS schemaname,
    cr.relname AS tablename,
    ci.relname AS indexname,
    i.indisunique AS is_unique,
    i.indisprimary AS is_primary,
    i.indisclustered AS is_clustered,
    i.indisvalid AS is_valid,
    i.indisready AS is_ready,
    i.indislive AS is_live
    -- , (pg_relation_size('"' || nsp.nspname || '"."' || ci.relname ||
    -- '"') / 1024.0 / 1024.0 / 1024.0)::decimal(18, 2) AS size_gb
FROM
    pg_index AS i
    JOIN pg_class AS ci ON i.indexrelid = ci.oid
        AND ci.relkind = 'i'
    JOIN pg_class AS cr ON i.indrelid = cr.oid
        AND cr.relkind = 'r'
    JOIN pg_namespace AS nsp ON cr.relnamespace = nsp.oid
        AND nsp.nspname NOT LIKE 'pg_%'
WHERE
    cr.relname LIKE '%';

-- Details of all existing indexes
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;

--List Indexes on a table
SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;

-- Index Size
SELECT
    i.relname "table_name",
    indexrelname "index_name",
    pg_size_pretty(pg_total_relation_size(relid)) AS "total_size",
    pg_size_pretty(pg_relation_size(relid)) AS "table_size",
    pg_size_pretty(pg_indexes_size(relid)) AS "total_index_size",
    pg_size_pretty(pg_relation_size(indexrelid)) "index_size",
    reltuples::bigint "estimated_table_rows"
FROM
    pg_stat_all_indexes i
    JOIN pg_class c ON i.relid = c.oid
WHERE
    i.relname = 'employees'