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
|
--
-- VACUUM
--
CREATE TABLE vactst (i INT);
INSERT INTO vactst VALUES (1);
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst VALUES (0);
SELECT count(*) FROM vactst;
DELETE FROM vactst WHERE i != 0;
SELECT * FROM vactst;
VACUUM FULL vactst;
UPDATE vactst SET i = i + 1;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst VALUES (0);
SELECT count(*) FROM vactst;
DELETE FROM vactst WHERE i != 0;
VACUUM (FULL) vactst;
DELETE FROM vactst;
SELECT * FROM vactst;
VACUUM (FULL, FREEZE) vactst;
VACUUM (ANALYZE, FULL INPLACE) vactst;
CREATE TABLE vaccluster (i INT PRIMARY KEY);
ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
INSERT INTO vaccluster SELECT * FROM vactst;
CREATE TEMP TABLE vacid (
relid regclass,
filenode_0 oid,
filenode_1 oid,
filenode_2 oid,
filenode_3 oid
);
INSERT INTO vacid (relid, filenode_0)
SELECT oid, relfilenode FROM pg_class WHERE oid::regclass IN (
'pg_am', -- normal catalog
'pg_class', -- fundamental catalog
'pg_database', -- shared catalog
'vaccluster' , -- clustered table
'vacid', -- temp table
'vactst' -- normal table
);
-- only clusterd table should be changed
CLUSTER vaccluster;
UPDATE vacid SET filenode_1 = relfilenode
FROM pg_class WHERE oid = relid;
-- all tables should not be changed
VACUUM (FULL INPLACE) pg_am;
VACUUM (FULL INPLACE) pg_class;
VACUUM (FULL INPLACE) pg_database;
VACUUM (FULL INPLACE) vaccluster;
VACUUM (FULL INPLACE) vacid;
VACUUM (FULL INPLACE) vactst;
UPDATE vacid SET filenode_2 = relfilenode
FROM pg_class WHERE oid = relid;
-- only non-system tables should be changed
VACUUM FULL pg_am;
VACUUM FULL pg_class;
VACUUM FULL pg_database;
VACUUM FULL vaccluster;
VACUUM FULL vacid;
VACUUM FULL vactst;
UPDATE vacid SET filenode_3 = relfilenode
FROM pg_class WHERE oid = relid;
SELECT relid,
filenode_0 = filenode_1 AS cluster,
filenode_1 = filenode_2 AS full_inplace,
filenode_2 = filenode_3 AS full
FROM vacid
ORDER BY relid::text;
DROP TABLE vaccluster;
DROP TABLE vacid;
DROP TABLE vactst;
|
