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;