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
|
CREATE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
LANGUAGE C STRICT;
CREATE FUNCTION relation_size (text) RETURNS bigint
AS 'MODULE_PATHNAME', 'relation_size'
LANGUAGE C STRICT;
CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_tablespace_size'
LANGUAGE C STRICT;
CREATE FUNCTION pg_database_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_database_size'
LANGUAGE C STRICT;
CREATE FUNCTION pg_relation_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_relation_size'
LANGUAGE C STRICT;
CREATE FUNCTION pg_size_pretty(bigint) RETURNS text
AS 'MODULE_PATHNAME', 'pg_size_pretty'
LANGUAGE C STRICT;
CREATE FUNCTION total_relation_size (text) RETURNS bigint AS '
SELECT pg_relation_size(r.oid)
+ COALESCE(pg_relation_size(t.oid), 0)::bigint
+ COALESCE(pg_relation_size(ti.oid), 0)::bigint
+ COALESCE(SUM(pg_relation_size(i.indexrelid)), 0)::bigint
+ COALESCE(SUM(pg_relation_size(it.oid)), 0)::bigint
+ COALESCE(SUM(pg_relation_size(iti.oid)), 0)::bigint AS bytes
FROM pg_class r
LEFT JOIN pg_class t ON (r.reltoastrelid = t.oid)
LEFT JOIN pg_class ti ON (t.reltoastidxid = ti.oid)
LEFT JOIN pg_index i ON (r.oid = i.indrelid)
LEFT JOIN pg_class ir ON (ir.oid = i.indexrelid)
LEFT JOIN pg_class it ON (ir.reltoastrelid = it.oid)
LEFT JOIN pg_class iti ON (it.reltoastidxid = iti.oid)
WHERE r.relname = \$1
GROUP BY r.oid, t.oid, ti.oid
' LANGUAGE SQL;
CREATE FUNCTION indexes_size (text) RETURNS bigint
AS '
SELECT COALESCE(SUM(pg_relation_size(ir.oid)), 0)::bigint
+ COALESCE(SUM(pg_relation_size(it.oid)), 0)::bigint
+ COALESCE(SUM(pg_relation_size(iti.oid)), 0)::bigint AS bytes
FROM pg_class r
LEFT JOIN pg_index i ON (r.oid = i.indrelid)
LEFT JOIN pg_class ir ON (ir.oid = i.indexrelid)
LEFT JOIN pg_class it ON (ir.reltoastrelid = it.oid)
LEFT JOIN pg_class iti ON (it.reltoastidxid = iti.oid)
WHERE r.relname = \$1
' LANGUAGE SQL;
CREATE FUNCTION relation_size_components (text) RETURNS SETOF RECORD
AS '
-- relation size
SELECT indexes_size(r.relname) AS indexes_size,
relation_size(r.relname) AS data_size,
total_relation_size(r.relname) AS total_size,
r.relname, r.relkind, r.oid AS relid, r.relfilenode
FROM pg_class r
WHERE r.relname = \$1
UNION ALL
-- relation toast size
SELECT indexes_size(toast.relname) AS indexes_size,
relation_size(''pg_toast.''||toast.relname) AS data_size,
total_relation_size(toast.relname) AS total_size,
toast.relname, toast.relkind, toast.oid AS relid, toast.relfilenode
FROM pg_class r, pg_class toast
WHERE r.reltoastrelid = toast.oid
AND r.relname = \$1
UNION ALL
-- relation toast index size
SELECT indexes_size(toastidxr.relname) AS indexes_size,
relation_size(''pg_toast.''||toastidxr.relname) AS data_size,
total_relation_size(toastidxr.relname) AS total_size,
toastidxr.relname, toastidxr.relkind,
toastidxr.oid AS relid, toastidxr.relfilenode
FROM pg_class r, pg_index toastidx, pg_class toastidxr
WHERE r.relname = \$1
AND r.reltoastrelid = toastidx.indrelid
AND toastidx.indexrelid = toastidxr.oid
UNION ALL
-- relation indices size
SELECT indexes_size(idxr.relname) AS indexes_size,
relation_size(idxr.relname) AS data_size,
total_relation_size(idxr.relname) AS total_size,
idxr.relname, idxr.relkind, idxr.oid AS relid, idxr.relfilenode
FROM pg_class r, pg_class idxr, pg_index idx
WHERE r.relname = \$1
AND r.oid = idx.indrelid
AND idx.indexrelid = idxr.oid
UNION ALL
-- relation indices toast size
SELECT indexes_size(idxtoastr.relname) AS indexes_size,
relation_size(''pg_toast.''||idxtoastr.relname) AS data_size,
total_relation_size(idxtoastr.relname) AS total_size,
idxtoastr.relname, idxtoastr.relkind, idxtoastr.oid AS relid,
idxtoastr.relfilenode
FROM pg_class r, pg_class idxr, pg_index idx, pg_class idxtoastr
WHERE r.relname = \$1
AND r.oid = idx.indrelid
AND idx.indexrelid = idxr.oid
AND idxr.reltoastrelid = idxtoastr.oid
UNION ALL
-- relation indices toast index size
SELECT indexes_size(idxtoastidxr.relname) AS indexes_size,
relation_size(''pg_toast.''||idxtoastidxr.relname) AS data_size,
total_relation_size(idxtoastidxr.relname) AS total_size,
idxtoastidxr.relname, idxtoastidxr.relkind,
idxtoastidxr.oid AS relid, idxtoastidxr.relfilenode
FROM pg_class r, pg_class idxr, pg_index idx, pg_class idxtoast,
pg_class idxtoastidxr
WHERE r.relname = \$1
AND r.oid = idx.indrelid
AND idx.indexrelid = idxr.oid
AND idxr.reltoastrelid = idxtoast.oid
AND idxtoast.reltoastrelid = idxtoastidxr.oid
' LANGUAGE SQL;
|
