Ash FrameworkAF
Ash Framework•8mo ago•
48 replies
rohan

Bad join query when using attribute tenancy

Hi! I'm noticing a major performance bug when using attribute-based multi-tenancy in our app:

We have an a resource Visit that has:
belongs_to :patient, Practices.Patient, public?: true

both tables have an attribute practice_id which is the multitenancy attribute

Then I have a calculation that looks like this:

    calculate :patient_display_name, :string do
      public? true

      calculation expr(
                    cond do
                      not is_nil(patient.last_name) ->
                        patient.first_name <> " " <> patient.last_name
                      true ->
                        nil
                    end
                  )
    end


But loading this calculation causes a terrible query:
SELECT 
    v0.id,
    -- patient_display_name calculation
    CASE 
        WHEN s1.last_name IS NOT NULL THEN 
            s1.first_name || ' ' || s1.last_name
        ELSE NULL 
    END AS patient_display_name

FROM visits v0

-- Patient subquery (loads ALL columns unnecessarily)
LEFT JOIN (
    SELECT 
        sp0.id,
        sp0.first_name,           -- ✓ needed
        sp0.last_name,            -- ✓ needed
        sp0.custom_id,            -- ❌ unnecessary
        sp0.hidden_at,            -- ❌ unnecessary
        ... [ a bunch of other unnecessary fields ] ...
        sp0.practice_id,          -- ❌ unnecessary (already filtering on it)

    FROM patients sp0 
    WHERE sp0.practice_id = $practice_id
) s1 ON v0.patient_id = s1.id

WHERE 
    v0.id = $visit_id
    AND v0.practice_id = $practice_id;


The unnecessary fields aren't terrible, but loading the entire patient table to get one causes this query to take 300ms instead of a few ms

Is there a way around this?

Ash version:
3.5.13
Ash postgres:
2.5.22
Was this page helpful?