forked from vmware-archive/atc
-
Notifications
You must be signed in to change notification settings - Fork 0
/
144_add_indexes_to_a_bunch_more_stuff.go
237 lines (200 loc) · 6.47 KB
/
144_add_indexes_to_a_bunch_more_stuff.go
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
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
package migrations
import "github.com/concourse/atc/db/migration"
// We pretty much just added an index for every foreign key, plus one on
// containers for plan_id.
//
// We'll check later which ones are wasteful.
//
// We promise.
//
// Useful queries:
//
// Show fkeys without indexes:
//
// CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
// SELECT ARRAY(
// SELECT $1[i]
// FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
// ORDER BY 1
// )
// ' language sql;
// SELECT conrelid::regclass, conname, reltuples::bigint
// FROM pg_constraint
// JOIN pg_class ON (conrelid = pg_class.oid)
// WHERE contype = 'f'
// AND NOT EXISTS (
// SELECT 1
// FROM pg_index
// WHERE indrelid = conrelid
// AND pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
// )
// ORDER BY reltuples DESC;
//
// Show size and # of hits for each index:
//
// 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;
func AddIndexesToABunchMoreStuff(tx migration.LimitedTx) error {
_, err := tx.Exec(`CREATE INDEX builds_team_id ON builds (team_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX independent_build_inputs_job_id ON independent_build_inputs (job_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX independent_build_inputs_version_id ON independent_build_inputs (version_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX next_build_inputs_job_id ON next_build_inputs (job_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX next_build_inputs_version_id ON next_build_inputs (version_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_cache_uses_resource_cache_id ON resource_cache_uses (resource_cache_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_cache_uses_build_id ON resource_cache_uses (build_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_cache_uses_resource_type_id ON resource_cache_uses (resource_type_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_cache_uses_resource_id ON resource_cache_uses (resource_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_caches_resource_config_id ON resource_caches (resource_config_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_config_uses_resource_config_id ON resource_config_uses (resource_config_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_config_uses_build_id ON resource_config_uses (build_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_config_uses_resource_type_id ON resource_config_uses (resource_type_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_config_uses_resource_id ON resource_config_uses (resource_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_configs_base_resource_type_id ON resource_configs (base_resource_type_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_configs_resource_cache_id ON resource_configs (resource_cache_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX containers_resource_config_id ON containers (resource_config_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX containers_worker_resource_cache_id ON containers (worker_resource_cache_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX containers_build_id ON containers (build_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX containers_plan_id ON containers (plan_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX containers_team_id ON containers (team_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX containers_worker_name ON containers (worker_name)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX volumes_container_id ON volumes (container_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX volumes_parent_id ON volumes (parent_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX volumes_team_id ON volumes (team_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX volumes_worker_resource_cache_id ON volumes (worker_resource_cache_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX volumes_worker_base_resource_type_id ON volumes (worker_base_resource_type_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX worker_resource_caches_worker_base_resource_type_id ON worker_resource_caches (worker_base_resource_type_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX worker_resource_caches_resource_cache_id ON worker_resource_caches (resource_cache_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX worker_base_resource_types_base_resource_type_id ON worker_base_resource_types (base_resource_type_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX worker_base_resource_types_worker_name ON worker_base_resource_types (worker_name)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX workers_team_id ON workers (team_id)`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE INDEX resource_types_pipeline_id ON resource_types (pipeline_id)`)
if err != nil {
return err
}
return nil
}