Improve Reporting - Reminders query performance

Description

On large databases, the Reporting - Reminders - Pending tab performs poorly.
The SQL from which the following is derived ends up doing a table scan on entity_relationships (patient_owner) below. This is an odd choice by the query optimizer, but we don’t have a facility to specify an index hint via the ArchetypeQuery API.

SELECT * FROM acts reminder_item INNER JOIN act_relationships r_items ON reminder_item.act_id = r_items.target_id INNER JOIN acts reminder ON r_items.source_id = reminder.act_id INNER JOIN participations ppatient ON reminder.act_id = ppatient.act_id INNER JOIN entities patient ON ppatient.entity_id = patient.entity_id JOIN entity_relationships patient_owner ON patient_owner.arch_short_name = 'entityRelationship.patientOwner' AND patient.entity_id = patient_owner.target_id AND (patient_owner.active_end_time IS NULL) INNER JOIN entities customer ON customer.entity_id = patient_owner.source_id AND customer.arch_short_name = 'party.customerperson' AND customer.active = TRUE WHERE (reminder_item.arch_short_name = 'act.patientReminderItemPrint' OR reminder_item.arch_short_name = 'act.patientReminderItemEmail' OR reminder_item.arch_short_name = 'act.patientReminderItemExport' OR reminder_item.arch_short_name = 'act.patientReminderItemList' OR reminder_item.arch_short_name = 'act.patientReminderItemSMS') AND reminder_item.activity_start_time < '2022-11-25' AND reminder_item.status IN ('PENDING') AND r_items.arch_short_name = 'actRelationship.patientReminderItem' AND reminder.arch_short_name = 'act.patientReminder' AND reminder.status = 'IN_PROGRESS' AND ppatient.arch_short_name = 'participation.patient' AND ppatient.activity_start_time < '2022-11-25' AND patient.arch_short_name = 'party.patientpet' ORDER BY customer.name ASC, customer.entity_id ASC, reminder_item.arch_short_name ASC, patient.name ASC, patient.entity_id ASC, reminder_item.activity_start_time ASC, reminder_item.act_id ASC LIMIT 80

Adding an index on entity_relationships means that the query optimizer no longer selects it, and the query performance improves:

alter table entity_relationships add index (arch_short_name, target_id);

Environment

None

Attachments

2
  • 12 Dec 2022, 05:02 AM
  • 12 Dec 2022, 04:52 AM

Activity

Show:
Fixed

Details

Assignee

Reporter

Time tracking

5.5h logged

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created December 12, 2022 at 4:24 AM
Updated March 2, 2023 at 10:24 PM
Resolved December 12, 2022 at 7:10 AM
Who's Looking?