Description

Speed optimizations to the GET /api/person/orphaned endpoint that results in ~17x improvement in query time.

Additional Notes

Below is a comparison of the existing and optimized queries used to retrieve orphaned person clusters, along with the performance improvements achieved. The original query performed a join between every row in mpi_person and mpi_patient, which triggered a costly sequential scan of the entire mpi_patient table. In contrast, the optimized query uses a NOT EXISTS condition, allowing PostgreSQL to efficiently leverage indexes and quickly identify persons without any associated patient records.

🔄 Existing Query

SELECT mpi_person.id, mpi_person.reference_id
FROM mpi_person
LEFT OUTER JOIN mpi_patient ON mpi_patient.person_id = mpi_person.id
WHERE mpi_patient.id IS NULL

✅ Optimized Query

SELECT id, reference_id
FROM mpi_person
WHERE NOT EXISTS (
    SELECT 1
    FROM mpi_patient
    WHERE mpi_patient.person_id = mpi_person.id
)

Using a database with 1 million person clusters and ~8 million patient records. Calls to GET /api/person/orphaned averaged around 6.3s with the existing query. Using the optimized query, those dropped to around 370ms.

<--------------------- REMOVE THE LINES BELOW BEFORE MERGING --------------------->

Checklist

Please review and complete the following checklist before submitting your pull request:

• I have ensured that the pull request is of a manageable size, allowing it to be reviewed within a single session.
• I have reviewed my changes to ensure they are clear, concise, and well-documented.
• I have updated the documentation, if applicable.
• I have added or updated test cases to cover my changes, if applicable.
• I have minimized the number of reviewers to include only those essential for the review.

Checklist for Reviewers

Please review and complete the following checklist during the review process:

• The code follows best practices and conventions.
• The changes implement the desired functionality or fix the reported issue.
• The tests cover the new changes and pass successfully.
• Any potential edge cases or error scenarios have been considered.