emrojo
commented
1 year ago Indexes tested for this query (will be in use for SSRs UAT):
-- qc comments
explain select distinct
-- stock resource values
sr.labware_type,
sr.labware_human_barcode,
sr.labware_coordinate,
-- sample values
sample.name,
sample.id_lims as id_lims,
sample.accession_number as accession_number,
sample.common_name as common_name,
sample.taxon_id as taxon_id,
sample.country_of_origin as country_of_origin,
sample.sanger_sample_id as sanger_sample_id,
sample.supplier_name as supplier_name,
sample.donor_id as donor_id,
sample.date_of_sample_collection as date_of_sample_collection,
sample.priority_level as priority_level,
sample.donor_id as donor_id,
-- qc values
lr_shearing_qc_comments.value as qc_shearing_comments,
lr_sheared_femto_fragment_size.value as qc_sheared_femto_fragment_size,
lr_post_spri_concentration.value as qc_post_spri_concentration,
lr_post_spri_volume.value as qc_post_spri_volume,
lr_final_nano_drop_280.value as qc_final_nano_drop_280,
lr_final_nano_drop_230.value as qc_final_nano_drop_230,
lr_final_nano_drop.value as qc_final_nano_drop
from sample
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join
(
-- shearing_qc_comments
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='shearing_qc_comments'
) as lr_shearing_qc_comments on lr_shearing_qc_comments.supplier_name=sample.supplier_name
inner join
(
-- sheared_femto_fragment_size
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='sheared_femto_fragment_size'
) as lr_sheared_femto_fragment_size on lr_sheared_femto_fragment_size.supplier_name=sample.supplier_name
inner join
(
-- post_spri_concentration
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='post_spri_concentration'
) as lr_post_spri_concentration on lr_post_spri_concentration.supplier_name=sample.supplier_name
inner join
(
-- post_spri_volume
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='post_spri_volume'
) as lr_post_spri_volume on lr_post_spri_volume.supplier_name=sample.supplier_name
inner join
(
-- final_nano_drop_280
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='final_nano_drop_280'
) as lr_final_nano_drop_280 on lr_final_nano_drop_280.supplier_name=sample.supplier_name
inner join
(
-- final_nano_drop_230
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='final_nano_drop_230'
) as lr_final_nano_drop_230 on lr_final_nano_drop_230.supplier_name=sample.supplier_name
inner join
(
-- final_nano_drop
select distinct sample.supplier_name, lr.value
from long_read_qc_result as lr_base
inner join sample on sample.supplier_name=lr_base.sample_id
inner join stock_resource as sr on sr.id_sample_tmp=sample.id_sample_tmp
inner join long_read_qc_result as lr on lr.id_long_read_qc_result_tmp=lr_base.id_long_read_qc_result_tmp
where lr.assay_type_key='final_nano_drop'
) as lr_final_nano_drop on lr_final_nano_drop.supplier_name=sample.supplier_name
;
User story As a DB user I would like to be able to join long_read_qc_result with the samples using an indexed field so the queries perform better
Who are the primary contacts for this story Eduardo Martin
Who is the nominated tester for UAT Eduardo Martin
Acceptance criteria To be considered successful the solution must allow:
Example EXPLAIN:
CURRENT OUTPUT:
Dependencies This story is blocked by the following dependencies:
References This story has a non-blocking relationship with:
Additional context Add any other context or screenshots about the feature request here.