Help Needed: Long-Running Study Metadata Query in DCM4CHEE Arc Light

[Shubham Kumar]

Hi all,

We are currently facing performance issues on our DCM4CHEE Arc Light instance due to a long-running query that frequently appears in our PostgreSQL logs. The query appears to be part of the study-level metadata resolution or QIDO-RS view handling.

Below is the query we’ve observed:

SELECT study0_.pk AS col_0_0_,
       patient1_.num_studies AS col_1_0_,
       patient1_.created_time AS col_2_0_,
       patient1_.updated_time AS col_3_0_,
       patient1_.verification_time AS col_4_0_,
       patient1_.verification_status AS col_5_0_,
       patient1_.failed_verifications AS col_6_0_,
       study0_.created_time AS col_7_0_,
       study0_.updated_time AS col_8_0_,
       study0_.modified_time AS col_9_0_,
       study0_.access_time AS col_10_0_,
       study0_.expiration_state AS col_11_0_,
       study0_.expiration_date AS col_12_0_,
       study0_.expiration_exporter_id AS col_13_0_,
       study0_.rejection_state AS col_14_0_,
       study0_.completeness AS col_15_0_,
       study0_.failed_retrieves AS col_16_0_,
       study0_.access_control_id AS col_17_0_,
       study0_.storage_ids AS col_18_0_,
       study0_.ext_retrieve_aet AS col_19_0_,
       study0_.study_size AS col_20_0_,
       queryattri3_.num_instances AS col_21_0_,
       queryattri3_.num_series AS col_22_0_,
       queryattri3_.mods_in_study AS col_23_0_,
       queryattri3_.cuids_in_study AS col_24_0_,
       queryattri3_.retrieve_aets AS col_25_0_,
       queryattri3_.availability AS col_26_0_,
       attributes2_.attrs AS col_27_0_,
       attributes4_.attrs AS col_28_0_
FROM study study0_
INNER JOIN patient patient1_ ON study0_.patient_fk = patient1_.pk
INNER JOIN dicomattrs attributes2_ ON patient1_.dicomattrs_fk = attributes2_.pk
LEFT OUTER JOIN study_query_attrs queryattri3_ ON study0_.pk = queryattri3_.study_fk AND queryattri3_.view_id = $1
INNER JOIN dicomattrs attributes4_ ON study0_.dicomattrs_fk = attributes4_.pk;


This query does not include a WHERE clause or pagination and includes large binary attrs columns, which makes it quite heavy when executed across a large dataset. It appears to contribute significantly to backend load and holds database connections for extended periods.

We would appreciate guidance on the following:
1. Can someone confirm the purpose of this query and when it is typically executed?
2. Are there configuration options (e.g., views, pagination, filtering) available to reduce its execution cost?
3. What indexes are recommended to support this query efficiently?
4. Is it expected behavior for this query to run without any filtering?

Any advice or best practices to handle or optimize this would be highly appreciated.


Best regards,
Shubham