|
CREATE INDEX refactored_pipeline_instances_full_text_search_idx
|
|
ON pipeline_instances
|
|
USING gin (to_tsvector( 'english'::regconfig,
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(pipeline_template_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
COALESCE(components, ''::character varying)::text || ' '::text ||
|
|
COALESCE(properties, ''::character varying)::text || ' '::text ||
|
|
COALESCE(state, ''::character varying)::text || ' '::text ||
|
|
COALESCE(components_summary, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text)
|
|
);
|
|
|
|
|
|
CREATE INDEX refactored_collections_full_text_search_idx
|
|
ON collections
|
|
USING gin (to_tsvector('english'::regconfig,
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(portable_data_hash, ''::character varying)::text || ' '::text ||
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
COALESCE(properties, ''::character varying)::text || ' '::text ||
|
|
COALESCE(file_names, ''::character varying)::text)
|
|
);
|
|
|
|
CREATE INDEX refactored_container_requests_full_text_search_idx
|
|
ON container_requests
|
|
USING gin (to_tsvector('english'::regconfig,
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
COALESCE(properties, ''::character varying)::text || ' '::text ||
|
|
COALESCE(state, ''::character varying)::text || ' '::text ||
|
|
COALESCE(requesting_container_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(container_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(mounts, ''::character varying)::text || ' '::text ||
|
|
COALESCE(runtime_constraints, ''::character varying)::text || ' '::text ||
|
|
COALESCE(container_image, ''::character varying)::text || ' '::text ||
|
|
COALESCE(environment, ''::character varying)::text || ' '::text ||
|
|
COALESCE(cwd, ''::character varying)::text || ' '::text ||
|
|
COALESCE(command, ''::character varying)::text || ' '::text ||
|
|
COALESCE(output_path, ''::character varying)::text || ' '::text ||
|
|
COALESCE(filters, ''::character varying)::text)
|
|
);
|
|
|
|
CREATE INDEX refactored_groups_full_text_search_idx
|
|
ON groups
|
|
USING gin (to_tsvector('english'::regconfig,
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
COALESCE(group_class, ''::character varying)::text)
|
|
);
|
|
|
|
CREATE INDEX refactored_jobs_full_text_search_idx
|
|
ON jobs
|
|
USING gin (to_tsvector('english'::regconfig,
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(submit_id, ''::character varying)::text || ' '::text ||
|
|
COALESCE(script, ''::character varying)::text || ' '::text ||
|
|
COALESCE(script_version, ''::character varying)::text || ' '::text ||
|
|
COALESCE(script_parameters, ''::character varying)::text || ' '::text ||
|
|
COALESCE(cancelled_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(cancelled_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(output, ''::character varying)::text || ' '::text ||
|
|
COALESCE(is_locked_by_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(log, ''::character varying)::text || ' '::text ||
|
|
COALESCE(tasks_summary, ''::character varying)::text || ' '::text ||
|
|
COALESCE(runtime_constraints, ''::character varying)::text || ' '::text ||
|
|
COALESCE(repository, ''::character varying)::text || ' '::text ||
|
|
COALESCE(supplied_script_version, ''::character varying)::text || ' '::text ||
|
|
COALESCE(docker_image_locator, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
COALESCE(state, ''::character varying)::text || ' '::text ||
|
|
COALESCE(arvados_sdk_version, ''::character varying)::text || ' '::text ||
|
|
COALESCE(components, ''::character varying)::text || ' '::text ||
|
|
COALESCE(script_parameters_digest, ''::character varying)::text)
|
|
);
|
|
|
|
CREATE INDEX refactored_pipeline_templates_full_text_search_idx
|
|
ON pipeline_templates
|
|
USING gin (to_tsvector('english'::regconfig,
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
COALESCE(components, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text)
|
|
);
|
|
|
|
|
|
-- THIS INDEX MATCHES THE ONE IN THE ARVADOS REPO, BUT NOT THE ONE IN PRODUCTION
|
|
--
|
|
-- CREATE INDEX refactored_workflows_full_text_search_idx
|
|
-- ON workflows
|
|
-- USING gin (to_tsvector('english'::regconfig,
|
|
-- COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
-- COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
-- COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
-- COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
-- COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
-- COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
-- COALESCE(definition, ''::character varying)::text)
|
|
-- );
|
|
|
|
|
|
CREATE INDEX refactored_workflows_full_text_search_idx
|
|
ON workflows
|
|
USING gin (to_tsvector('english'::regconfig,
|
|
COALESCE(uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(owner_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_client_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(modified_by_user_uuid, ''::character varying)::text || ' '::text ||
|
|
COALESCE(name, ''::character varying)::text || ' '::text ||
|
|
COALESCE(description, ''::character varying)::text || ' '::text ||
|
|
COALESCE(workflow, ''::character varying)::text)
|
|
);
|
|
|
|
|
|
|
|
\timing
|
|
|
|
SELECT "pipeline_instances".*
|
|
FROM "pipeline_instances"
|
|
WHERE ((to_tsvector('english',
|
|
coalesce(uuid,'') || ' ' ||
|
|
coalesce(owner_uuid,'') || ' ' ||
|
|
coalesce(modified_by_client_uuid,'') || ' ' ||
|
|
coalesce(modified_by_user_uuid,'') || ' ' ||
|
|
coalesce(pipeline_template_uuid,'') || ' ' ||
|
|
coalesce(name,'') || ' ' ||
|
|
coalesce(components,'') || ' ' ||
|
|
coalesce(properties,'') || ' ' ||
|
|
coalesce(state,'') || ' ' ||
|
|
coalesce(components_summary,'') || ' ' ||
|
|
coalesce(description,'')) @@ to_tsquery('HWI:*')))
|
|
ORDER BY pipeline_instances.modified_at desc,
|
|
pipeline_instances.uuid
|
|
LIMIT 200
|
|
OFFSET 0;
|
|
|
|
SELECT COUNT(DISTINCT "pipeline_instances"."id")
|
|
FROM "pipeline_instances"
|
|
WHERE (
|
|
(to_tsvector('english',
|
|
coalesce(uuid,'') || ' ' ||
|
|
coalesce(owner_uuid,'') || ' ' ||
|
|
coalesce(modified_by_client_uuid,'') || ' ' ||
|
|
coalesce(modified_by_user_uuid,'') || ' ' ||
|
|
coalesce(pipeline_template_uuid,'') || ' ' ||
|
|
coalesce(name,'') || ' ' ||
|
|
coalesce(components,'') || ' ' ||
|
|
coalesce(properties,'') || ' ' ||
|
|
coalesce(state,'') || ' ' ||
|
|
coalesce(components_summary,'') || ' ' ||
|
|
coalesce(description,'')) @@ to_tsquery('hash:*')
|
|
)
|
|
);
|
|
|
|
SELECT "jobs".*
|
|
FROM "jobs"
|
|
WHERE (to_tsvector('english',
|
|
coalesce(uuid,'') || ' ' ||
|
|
coalesce(owner_uuid,'') || ' ' ||
|
|
coalesce(modified_by_client_uuid,'') || ' ' ||
|
|
coalesce(modified_by_user_uuid,'') || ' ' ||
|
|
coalesce(submit_id,'') || ' ' ||
|
|
coalesce(script,'') || ' ' ||
|
|
coalesce(script_version,'') || ' ' ||
|
|
coalesce(script_parameters,'') || ' ' ||
|
|
coalesce(cancelled_by_client_uuid,'') || ' ' ||
|
|
coalesce(cancelled_by_user_uuid,'') || ' ' ||
|
|
coalesce(output,'') || ' ' ||
|
|
coalesce(is_locked_by_uuid,'') || ' ' ||
|
|
coalesce(log,'') || ' ' ||
|
|
coalesce(tasks_summary,'') || ' ' ||
|
|
coalesce(runtime_constraints,'') || ' ' ||
|
|
coalesce(repository,'') || ' ' ||
|
|
coalesce(supplied_script_version,'') || ' ' ||
|
|
coalesce(docker_image_locator,'') || ' ' ||
|
|
coalesce(description,'') || ' ' ||
|
|
coalesce(state,'') || ' ' ||
|
|
coalesce(arvados_sdk_version,'') || ' ' ||
|
|
coalesce(components,'') || ' ' ||
|
|
coalesce(script_parameters_digest,'')
|
|
) @@ to_tsquery('HWI:*')) ORDER BY jobs.modified_at desc, jobs.uuid LIMIT 200 OFFSET 0;
|