I am running a Postgres query with a CASE
expression in a join condition. The query takes a long time to run. Is there a better way to optimize this query?
Code snippet:
LEFT OUTER JOIN analyticsdatamart_gen_nontemporal_v1.dimension_organisations org ON org.unique_key = fo.dimension__order__responsible_organisation_key LEFT OUTER JOIN analyticsdatamart_gen_nontemporal_v1.dimension_work_sites site ON site.unique_key = fo.dimension__order__responsible_work_site_key LEFT OUTER JOIN analyticsdatamart_gen_nontemporal_v1.dimension_priorities prio ON fo.dimension__maximum_priority_procedure__priority_key = prio.unique_key LEFT OUTER JOIN analyticsdatamart_gen_nontemporal_v1.dimension_actors da ON CASE WHEN da.unique_key = fo.dimension__first_report__primary_releasing_actor_key IS NOT NULL THEN da.unique_key = fo.dimension__first_report__primary_releasing_actor_key ELSE da.unique_key = fo.dimension__first_dictation__dictating_actor_key END LEFT OUTER JOIN analyticsdatamart_gen_nontemporal_v1.fact_series fse ON fse.dimension__study__key = st.unique_key LEFT OUTER JOIN analyticsdatamart_gen_nontemporal_v1.dimension_series dse ON dse.unique_key = fse.dimension__series__key
Advertisement
Answer
For proper answer attach full query, table structure (with indexes) and execution plan.
Original CASE
is quite complicated, but hard to say if it’s responsible for query performance without information from execution plan.
CASE WHEN da.unique_key = fo.dimension__first_report__primary_releasing_actor_key IS NOT NULL THEN da.unique_key = fo.dimension__first_report__primary_releasing_actor_key ELSE da.unique_key = fo.dimension__first_dictation__dictating_actor_key END
This case can be transformed to
da.unique_key = CASE WHEN da.unique_key = fo.dimension__first_report__primary_releasing_actor_key IS NOT NULL THEN fo.dimension__first_report__primary_releasing_actor_key ELSE fo.dimension__first_dictation__dictating_actor_key END
or even
da.unique_key = coalesce (fo.dimension__first_report__primary_releasing_actor_key ,fo.dimension__first_dictation__dictating_actor_key)
This should give a optimizer (and everybody else) better understanding which column (in da table) is key for joining