|
1
|
CREATE INDEX refactored_pipeline_instances_full_text_search_idx
|
|
2
|
ON pipeline_instances
|
|
3
|
USING gin (to_tsvector( 'english'::regconfig,
|
|
4
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
5
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
6
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
7
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
8
|
COALESCE(pipeline_template_uuid, ''::character varying)::text || ' '::text ||
|
|
9
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
10
|
COALESCE(components, ''::character varying)::text || ' '::text ||
|
|
11
|
COALESCE(properties, ''::character varying)::text || ' '::text ||
|
|
12
|
COALESCE(state, ''::character varying)::text || ' '::text ||
|
|
13
|
COALESCE(components_summary, ''::character varying)::text || ' '::text ||
|
|
14
|
COALESCE(description, ''::character varying)::text)
|
|
15
|
);
|
|
16
|
|
|
17
|
|
|
18
|
CREATE INDEX refactored_collections_full_text_search_idx
|
|
19
|
ON collections
|
|
20
|
USING gin (to_tsvector('english'::regconfig,
|
|
21
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
22
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
23
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
24
|
COALESCE(portable_data_hash, ''::character varying)::text || ' '::text ||
|
|
25
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
26
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
27
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
28
|
COALESCE(properties, ''::character varying)::text || ' '::text ||
|
|
29
|
COALESCE(file_names, ''::character varying)::text)
|
|
30
|
);
|
|
31
|
|
|
32
|
CREATE INDEX refactored_container_requests_full_text_search_idx
|
|
33
|
ON container_requests
|
|
34
|
USING gin (to_tsvector('english'::regconfig,
|
|
35
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
36
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
37
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
38
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
39
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
40
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
41
|
COALESCE(properties, ''::character varying)::text || ' '::text ||
|
|
42
|
COALESCE(state, ''::character varying)::text || ' '::text ||
|
|
43
|
COALESCE(requesting_container_uuid, ''::character varying)::text || ' '::text ||
|
|
44
|
COALESCE(container_uuid, ''::character varying)::text || ' '::text ||
|
|
45
|
COALESCE(mounts, ''::character varying)::text || ' '::text ||
|
|
46
|
COALESCE(runtime_constraints, ''::character varying)::text || ' '::text ||
|
|
47
|
COALESCE(container_image, ''::character varying)::text || ' '::text ||
|
|
48
|
COALESCE(environment, ''::character varying)::text || ' '::text ||
|
|
49
|
COALESCE(cwd, ''::character varying)::text || ' '::text ||
|
|
50
|
COALESCE(command, ''::character varying)::text || ' '::text ||
|
|
51
|
COALESCE(output_path, ''::character varying)::text || ' '::text ||
|
|
52
|
COALESCE(filters, ''::character varying)::text)
|
|
53
|
);
|
|
54
|
|
|
55
|
CREATE INDEX refactored_groups_full_text_search_idx
|
|
56
|
ON groups
|
|
57
|
USING gin (to_tsvector('english'::regconfig,
|
|
58
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
59
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
60
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
61
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
62
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
63
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
64
|
COALESCE(group_class, ''::character varying)::text)
|
|
65
|
);
|
|
66
|
|
|
67
|
CREATE INDEX refactored_jobs_full_text_search_idx
|
|
68
|
ON jobs
|
|
69
|
USING gin (to_tsvector('english'::regconfig,
|
|
70
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
71
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
72
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
73
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
74
|
COALESCE(submit_id, ''::character varying)::text || ' '::text ||
|
|
75
|
COALESCE(script, ''::character varying)::text || ' '::text ||
|
|
76
|
COALESCE(script_version, ''::character varying)::text || ' '::text ||
|
|
77
|
COALESCE(script_parameters, ''::character varying)::text || ' '::text ||
|
|
78
|
COALESCE(cancelled_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
79
|
COALESCE(cancelled_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
80
|
COALESCE(output, ''::character varying)::text || ' '::text ||
|
|
81
|
COALESCE(is_locked_by_uuid, ''::character varying)::text || ' '::text ||
|
|
82
|
COALESCE(log, ''::character varying)::text || ' '::text ||
|
|
83
|
COALESCE(tasks_summary, ''::character varying)::text || ' '::text ||
|
|
84
|
COALESCE(runtime_constraints, ''::character varying)::text || ' '::text ||
|
|
85
|
COALESCE(repository, ''::character varying)::text || ' '::text ||
|
|
86
|
COALESCE(supplied_script_version, ''::character varying)::text || ' '::text ||
|
|
87
|
COALESCE(docker_image_locator, ''::character varying)::text || ' '::text ||
|
|
88
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
89
|
COALESCE(state, ''::character varying)::text || ' '::text ||
|
|
90
|
COALESCE(arvados_sdk_version, ''::character varying)::text || ' '::text ||
|
|
91
|
COALESCE(components, ''::character varying)::text || ' '::text ||
|
|
92
|
COALESCE(script_parameters_digest, ''::character varying)::text)
|
|
93
|
);
|
|
94
|
|
|
95
|
CREATE INDEX refactored_pipeline_templates_full_text_search_idx
|
|
96
|
ON pipeline_templates
|
|
97
|
USING gin (to_tsvector('english'::regconfig,
|
|
98
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
99
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
100
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
101
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
102
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
103
|
COALESCE(components, ''::character varying)::text || ' '::text ||
|
|
104
|
COALESCE(description, ''::character varying)::text)
|
|
105
|
);
|
|
106
|
|
|
107
|
|
|
108
|
-- THIS INDEX MATCHES THE ONE IN THE ARVADOS REPO, BUT NOT THE ONE IN PRODUCTION
|
|
109
|
--
|
|
110
|
-- CREATE INDEX refactored_workflows_full_text_search_idx
|
|
111
|
-- ON workflows
|
|
112
|
-- USING gin (to_tsvector('english'::regconfig,
|
|
113
|
-- COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
114
|
-- COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
115
|
-- COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
116
|
-- COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
117
|
-- COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
118
|
-- COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
119
|
-- COALESCE(definition, ''::character varying)::text)
|
|
120
|
-- );
|
|
121
|
|
|
122
|
|
|
123
|
CREATE INDEX refactored_workflows_full_text_search_idx
|
|
124
|
ON workflows
|
|
125
|
USING gin (to_tsvector('english'::regconfig,
|
|
126
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
127
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
128
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
129
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
130
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
131
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
132
|
COALESCE(workflow, ''::character varying)::text)
|
|
133
|
);
|
|
134
|
|
|
135
|
|
|
136
|
|
|
137
|
\timing
|
|
138
|
|
|
139
|
SELECT "pipeline_instances".*
|
|
140
|
FROM "pipeline_instances"
|
|
141
|
WHERE ((to_tsvector('english',
|
|
142
|
coalesce(uuid,'') || ' ' ||
|
|
143
|
coalesce(owner_uuid,'') || ' ' ||
|
|
144
|
coalesce(modified_by_client_uuid,'') || ' ' ||
|
|
145
|
coalesce(modified_by_user_uuid,'') || ' ' ||
|
|
146
|
coalesce(pipeline_template_uuid,'') || ' ' ||
|
|
147
|
coalesce(name,'') || ' ' ||
|
|
148
|
coalesce(components,'') || ' ' ||
|
|
149
|
coalesce(properties,'') || ' ' ||
|
|
150
|
coalesce(state,'') || ' ' ||
|
|
151
|
coalesce(components_summary,'') || ' ' ||
|
|
152
|
coalesce(description,'')) @@ to_tsquery('HWI:*')))
|
|
153
|
ORDER BY pipeline_instances.modified_at desc,
|
|
154
|
pipeline_instances.uuid
|
|
155
|
LIMIT 200
|
|
156
|
OFFSET 0;
|
|
157
|
|
|
158
|
SELECT COUNT(DISTINCT "pipeline_instances"."id")
|
|
159
|
FROM "pipeline_instances"
|
|
160
|
WHERE (
|
|
161
|
(to_tsvector('english',
|
|
162
|
coalesce(uuid,'') || ' ' ||
|
|
163
|
coalesce(owner_uuid,'') || ' ' ||
|
|
164
|
coalesce(modified_by_client_uuid,'') || ' ' ||
|
|
165
|
coalesce(modified_by_user_uuid,'') || ' ' ||
|
|
166
|
coalesce(pipeline_template_uuid,'') || ' ' ||
|
|
167
|
coalesce(name,'') || ' ' ||
|
|
168
|
coalesce(components,'') || ' ' ||
|
|
169
|
coalesce(properties,'') || ' ' ||
|
|
170
|
coalesce(state,'') || ' ' ||
|
|
171
|
coalesce(components_summary,'') || ' ' ||
|
|
172
|
coalesce(description,'')) @@ to_tsquery('hash:*')
|
|
173
|
)
|
|
174
|
);
|
|
175
|
|
|
176
|
SELECT "jobs".*
|
|
177
|
FROM "jobs"
|
|
178
|
WHERE (to_tsvector('english',
|
|
179
|
coalesce(uuid,'') || ' ' ||
|
|
180
|
coalesce(owner_uuid,'') || ' ' ||
|
|
181
|
coalesce(modified_by_client_uuid,'') || ' ' ||
|
|
182
|
coalesce(modified_by_user_uuid,'') || ' ' ||
|
|
183
|
coalesce(submit_id,'') || ' ' ||
|
|
184
|
coalesce(script,'') || ' ' ||
|
|
185
|
coalesce(script_version,'') || ' ' ||
|
|
186
|
coalesce(script_parameters,'') || ' ' ||
|
|
187
|
coalesce(cancelled_by_client_uuid,'') || ' ' ||
|
|
188
|
coalesce(cancelled_by_user_uuid,'') || ' ' ||
|
|
189
|
coalesce(output,'') || ' ' ||
|
|
190
|
coalesce(is_locked_by_uuid,'') || ' ' ||
|
|
191
|
coalesce(log,'') || ' ' ||
|
|
192
|
coalesce(tasks_summary,'') || ' ' ||
|
|
193
|
coalesce(runtime_constraints,'') || ' ' ||
|
|
194
|
coalesce(repository,'') || ' ' ||
|
|
195
|
coalesce(supplied_script_version,'') || ' ' ||
|
|
196
|
coalesce(docker_image_locator,'') || ' ' ||
|
|
197
|
coalesce(description,'') || ' ' ||
|
|
198
|
coalesce(state,'') || ' ' ||
|
|
199
|
coalesce(arvados_sdk_version,'') || ' ' ||
|
|
200
|
coalesce(components,'') || ' ' ||
|
|
201
|
coalesce(script_parameters_digest,'')
|
|
202
|
) @@ to_tsquery('HWI:*')) ORDER BY jobs.modified_at desc, jobs.uuid LIMIT 200 OFFSET 0;
|