-
Notifications
You must be signed in to change notification settings - Fork 13
/
Copy pathbloated_indexes.sql
178 lines (169 loc) · 6.98 KB
/
bloated_indexes.sql
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
/*
* Copyright (c) 2019-2025. Ivan Vakhrushev and others.
* https://github.com/mfvanek/pg-index-health-sql
*
* Licensed under the Apache License 2.0
*/
-- Finds indexes with the bloat.
--
-- Based on
-- https://wiki.postgresql.org/wiki/Show_database_bloat
-- https://blog.ioguix.net/postgresql/2014/03/28/Playing-with-indexes-and-better-bloat-estimate.html
-- https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql
-- https://github.com/pgexperts/pgx_scripts/blob/master/bloat/index_bloat_check.sql
-- https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_bloat.sql
--
-- Please note!
-- The user on whose behalf this sql query will be executed
-- have to have read permissions for the corresponding tables.
-- noqa: disable=PRS,ST06,AM06
with
indexes_data as (
select
pc.relname as inner_index_name,
pc.reltuples,
pc.relpages,
pi.indrelid as table_oid,
pi.indexrelid as index_oid,
pi.indnatts,
nsp.nspname,
coalesce(substring(array_to_string(pc.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) as fill_factor,
string_to_array(textin(int2vectorout(pi.indkey)), ' ')::int[] as indkey
from
pg_catalog.pg_index pi
inner join pg_catalog.pg_class pc on pc.oid = pi.indexrelid
inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
pc.relam = (select am.oid from pg_catalog.pg_am am where am.amname = 'btree') and
pc.relpages > 0 and
nsp.nspname = :schema_name_param::text
),
nested_indexes_attributes as (
select
inner_index_name,
reltuples,
relpages,
table_oid,
index_oid,
fill_factor,
indkey,
nspname,
pg_catalog.generate_series(1, indnatts) as attpos
from indexes_data
),
named_indexes_attributes as (
select
ic.table_oid,
ct.relnamespace,
ic.inner_index_name,
ic.attpos,
ic.indkey,
ic.indkey[ic.attpos] as indattpos,
ic.reltuples,
ic.relpages,
ic.index_oid,
ic.fill_factor,
coalesce(a1.attnum, a2.attnum) as attnum,
coalesce(a1.attname, a2.attname) as attname,
coalesce(a1.atttypid, a2.atttypid) as atttypid,
ic.nspname,
case when a1.attnum is null then ic.inner_index_name else ct.relname end as attrelname
from
nested_indexes_attributes ic
inner join pg_catalog.pg_class ct on ct.oid = ic.table_oid
left join pg_catalog.pg_attribute a1 on ic.indkey[ic.attpos] <> 0 and a1.attrelid = ic.table_oid and a1.attnum = ic.indkey[ic.attpos]
left join pg_catalog.pg_attribute a2 on ic.indkey[ic.attpos] = 0 and a2.attrelid = ic.index_oid and a2.attnum = ic.attpos
),
rows_data_stats as (
select
i.table_oid,
i.reltuples,
i.relpages,
i.index_oid,
i.fill_factor,
current_setting('block_size')::bigint as block_size,
/* max_align: 4 on 32bits, 8 on 64bits */
case when version() ~ 'mingw32' or version() ~ '64-bit|x86_64|ppc64|ia64|amd64' then 8 else 4 end as max_align,
/* per page header, fixed size: 20 for 7.x, 24 for others */
24 as page_header_size,
/* per page btree opaque data */
16 as page_opaque_data_size,
/* per tuple header: add indexattributebitmapdata if some cols are nullable */
case
when max(coalesce(s.null_frac, 0)) = 0 then 2 /* indextupledata size */
else 2 + ((32 + 8 - 1) / 8) /* indextupledata size + indexattributebitmapdata size (max num filed per index + 8 - 1 /8) */
end as index_tuple_header_size,
/* remove null values and save space using it fractional part from stats */
sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0)) as null_data_width,
max(case when i.atttypid = 'pg_catalog.name'::regtype then 1 else 0 end) > 0 as stats_not_available
from
named_indexes_attributes i
inner join pg_catalog.pg_namespace nsp on nsp.oid = i.relnamespace
inner join pg_catalog.pg_stats s on s.schemaname = nsp.nspname and s.tablename = i.attrelname and s.attname = i.attname
group by 1, 2, 3, 4, 5, 6, 7, 8, 9
),
rows_header_stats as (
select
max_align,
block_size,
reltuples,
relpages,
index_oid,
fill_factor,
table_oid,
(
index_tuple_header_size + max_align -
/* add padding to the index tuple header to align on max_align */
case when index_tuple_header_size % max_align = 0 then max_align else index_tuple_header_size % max_align end +
null_data_width + max_align -
/* add padding to the data to align on max_align */
case
when null_data_width = 0 then 0
when null_data_width::integer % max_align = 0 then max_align
else null_data_width::integer % max_align
end
)::numeric as null_data_header_width,
page_header_size,
page_opaque_data_size,
stats_not_available
from rows_data_stats
),
relation_stats as (
select
/* itemiddata size + computed avg size of a tuple (null_data_header_width) */
coalesce(1 + ceil(reltuples / floor((block_size - page_opaque_data_size - page_header_size) * fill_factor / (100 * (4 + null_data_header_width)::float))), 0)::bigint as estimated_pages_count,
block_size,
table_oid::regclass::text as table_name,
index_oid::regclass::text as index_name,
pg_relation_size(index_oid) as index_size,
relpages,
stats_not_available
from rows_header_stats
),
corrected_relation_stats as (
select
table_name,
index_name,
index_size,
block_size,
relpages,
(case when relpages - estimated_pages_count > 0 then relpages - estimated_pages_count else 0 end)::bigint as pages_ff_diff,
stats_not_available
from relation_stats
),
bloat_stats as (
select
table_name,
index_name,
index_size,
block_size * pages_ff_diff as bloat_size,
round(100 * block_size * pages_ff_diff / index_size::numeric, 2)::numeric(5, 2) as bloat_percentage,
stats_not_available
from corrected_relation_stats
)
select *
from bloat_stats
where
not stats_not_available and
bloat_percentage >= :bloat_percentage_threshold::numeric(5, 2)
order by table_name, index_name;