forked from NikolayS/postgres_dba
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathb1_table_estimation.sql
128 lines (118 loc) · 5.48 KB
/
b1_table_estimation.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
--Table bloat (estimated)
--This SQL is derived from https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql
/*
* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read.
* This query is compatible with PostgreSQL 9.0 and more
*/
with step1 as (
select
tbl.oid tblid,
ns.nspname as schema_name,
tbl.relname as table_name,
tbl.reltuples,
tbl.relpages as heappages,
coalesce(toast.relpages, 0) as toastpages,
coalesce(toast.reltuples, 0) as toasttuples,
coalesce(substring(array_to_string(tbl.reloptions, ' ') from '%fillfactor=#"__#"%' for '#')::int2, 100) as fillfactor,
current_setting('block_size')::numeric as bs,
case when version() ~ 'mingw32|64-bit|x86_64|ppc64|ia64|amd64' then 8 else 4 end as ma, -- NS: TODO: check it
24 as page_hdr,
23 + case when max(coalesce(null_frac, 0)) > 0 then (7 + count(*)) / 8 else 0::int end
+ case when bool_or(att.attname = 'oid' and att.attnum < 0) then 4 else 0 end as tpl_hdr_size,
sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) as tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype)
or sum(case when att.attnum > 0 then 1 else 0 end) <> count(s.attname) as is_na
from pg_attribute as att
join pg_class as tbl on att.attrelid = tbl.oid and tbl.relkind = 'r'
join pg_namespace as ns on ns.oid = tbl.relnamespace
join pg_stats as s on s.schemaname = ns.nspname and s.tablename = tbl.relname and not s.inherited and s.attname = att.attname
left join pg_class as toast on tbl.reltoastrelid = toast.oid
where not att.attisdropped and s.schemaname not in ('pg_catalog', 'information_schema')
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
order by 2, 3
), step2 as (
select
*,
(
4 + tpl_hdr_size + tpl_data_size + (2 * ma)
- case when tpl_hdr_size % ma = 0 then ma else tpl_hdr_size % ma end
- case when ceil(tpl_data_size)::int % ma = 0 then ma else ceil(tpl_data_size)::int % ma end
) as tpl_size,
bs - page_hdr as size_per_block,
(heappages + toastpages) as tblpages
from step1
), step3 as (
select
*,
ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) as est_tblpages,
ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) as est_tblpages_ff
-- , stattuple.pgstattuple(tblid) as pst
from step2
), step4 as (
select
*,
tblpages * bs as real_size,
(tblpages - est_tblpages) * bs as extra_size,
case when tblpages - est_tblpages > 0 then 100 * (tblpages - est_tblpages) / tblpages::float else 0 end as extra_ratio,
(tblpages - est_tblpages_ff) * bs as bloat_size,
case when tblpages - est_tblpages_ff > 0 then 100 * (tblpages - est_tblpages_ff) / tblpages::float else 0 end as bloat_ratio
-- , (pst).free_percent + (pst).dead_tuple_percent as real_frag
from step3
left join pg_stat_user_tables su on su.relid = tblid
-- WHERE NOT is_na
-- AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1
)
select
case is_na when true then 'TRUE' else '' end as "Is N/A",
coalesce(nullif(schema_name, 'public') || '.', '') || table_name as "Table",
pg_size_pretty(real_size::numeric) as "Size",
case
when extra_size::numeric >= 0
then '~' || pg_size_pretty(extra_size::numeric)::text || ' (' || round(extra_ratio::numeric, 2)::text || '%)'
else null
end as "Extra",
case
when bloat_size::numeric >= 0
then '~' || pg_size_pretty(bloat_size::numeric)::text || ' (' || round(bloat_ratio::numeric, 2)::text || '%)'
else null
end as "Bloat estimate",
case
when (real_size - bloat_size)::numeric >=0
then '~' || pg_size_pretty((real_size - bloat_size)::numeric)
else null
end as "Live",
greatest(last_autovacuum, last_vacuum)::timestamp(0)::text
|| case greatest(last_autovacuum, last_vacuum)
when last_autovacuum then ' (auto)'
else '' end as "Last Vaccuum",
(
select
coalesce(substring(array_to_string(reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 100)
from pg_class
where oid = tblid
) as "Fillfactor"
from step4
order by bloat_size desc nulls last
;
/*
Author of the original version:
2015, Jehan-Guillaume (ioguix) de Rorthais
License of the original version:
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this
list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/