Query using CASE in left outer join takes long time to run-CodePudding

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

CodePudding user response：

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