Description

Updating MPI indices to improve blocking queries.

Additional Notes

The changes identified below created a 25x performance boost in running blocking queries on a Postgres database with ~750k person clusters and 5 million patient records.

For reference, the blocking queries generated look similar to the following

SELECT mpi_patient.id, mpi_patient.person_id, mpi_patient.data, mpi_patient.external_patient_id, mpi_patient.external_person_id, mpi_patient.external_person_source, mpi_patient.reference_id 
FROM mpi_patient 
WHERE mpi_patient.person_id IN (
  SELECT DISTINCT mpi_patient.person_id
  FROM mpi_patient
  JOIN mpi_blocking_value AS bv0 ON
    mpi_patient.id = bv0.patient_id AND bv0.blockingkey = 1 AND bv0.value IN ('YYYY-MM-DD')
  JOIN mpi_blocking_value AS bv1 ON
    mpi_patient.id = bv1.patient_id AND bv1.blockingkey = 3 AND bv1.value IN ('X')
  JOIN mpi_blocking_value AS bv2 ON
    mpi_patient.id = bv2.patient_id AND bv2.blockingkey = 10 AND bv2.value IN ('XXXX:SS', 'XXXXMR')
);

• Adding an index to mpi_patient.person_id: The subquery returns a list of person_id values, and we use the query's where clause to join on that. Adding an index to this column vastly improves performance, as we no longer have to scan every value in the mpi_patient table.
• Reorder mpi_blocking_value composite index: In the subquery we join on the mpi_blocking_value table, once for every blocking criteria. While patient_id, blockingkey and value are used in the join clause, only the latter two are used first to filter out values based on the blocking data. Moving the order of the composite key to use blockingkey and value first, allows us to reuse the index for both this initial filtering step and the overall join clause used in the subquery.
• Adding an index to mpi_blocking_value.patient_id: Not actually needed for blocking, however the MPI API has an endpoint that requires deleting mpi_blocking_value rows related to a patient. This endpoint benefits greatly from having an index on patient_id. This was previously available in the composite index, as patient_id was the first value, however when we move it to the end its no longer available as a solo index. Thus, we are adding a separate key to make sure we don't lose performance when delete_blocking_values_for_patient is called.

<--------------------- 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.