[DEA] - Search performance improvement

[vindeolal]

Need:

Currently the search seems to take an average of 10 secs across different times and projects. Try to optmise it since this is the entry screen and hence helps to improve the users UX.

AC:

• Check ways to optimise search performance for all usecases of search in DEA
• Also take into consideration that custom search filters in observations also can be applied when spiking, other than default filters
• we need to optimise time as well as IO(so that we are not in need to do a full table scan on any transaction table) in the query response.

Thoughts:

• Can the query to retrieve count and results be merged - it is both of these that occupy around 50% of time each (If not possible, then we can think of removing count?)

• From the newrelic query it looked like we are always joining with program encounter and enrolment tables, but as per code looks otherwise. Below is the newrelic query:

  select distinct i.id as "id", i.first_name as "firstName", i.last_name as "lastName", i.profile_picture as "profilePicture", cast(concat_ws(?,i.first_name,i.middle_name,i.last_name)as text) as "fullName", i.uuid as "uuid", cast(tllv.title_lineage as text) as "addressLevel", st.name as "subjectTypeName", gender.name as "gender", i.date_of_birth as "dateOfBirth" from individual i left outer join title_lineage_locations_view tllv on i.address_id = tllv.lowestpoint_id left outer join gender on i.gender_id = gender.id left outer join subject_type st on i.subject_type_id = st.id and st.is_voided is ? left outer join program_enrolment penr on i.id = penr.individual_id and penr.is_voided is ? left outer join program_encounter pe on penr.id = pe.program_enrolment_id and pe.encounter_date_time is not ? and pe.is_voided is ? where i.is_voided is ? and (cast(i.observations as text) ilike ? or cast(penr.observations as text) ilike ?) and st.uuid = ? and (case when JSONB_TYPEOF(pe.observations->?) = ?then pe.observations -> ? @> to_jsonb(cast(? as text)) else pe.observations ->> ?= ? end or case when JSONB_TYPEOF(pe.observations->?) = ?then pe.observations -> ? @> to_jsonb(cast(? as text)) else pe.observations ->> ?= ? end) order by concat_ws(?,i.first_name,i.middle_name,i.last_name) asc, i.id desc offset ? limit ?

• Removing of sorting by subject type, date of birth, gender and title lineage?

• Joins after pagination SubjectSearchQueryBuilder? Some joins like title_lineage_locations_view performing after pagination?

• to fix search for simple regular lookup and name, any of the below?

  1. Remove distinct keyword
  2. Use a combined name field (first_name + middle_name + last_name) and search through tokens there
  3. Index on name + organisation_id
  4. Search after doing a reset_role (like queries will not use any index, not even regular b-tree index within RLS)

• Any strategies mentioned here

• Searching of observations outside of organisation context when entered?

• @Searchable annotation

• need to analyse the above via the execution plan of SQL to figure out the strategy

Out of scope:

• Using tools like Lucene
• Optimising 'Search All'

Old: Ignore:

• Right now there is no default subject type selected when the DEA loads due to which search becomes slow. We should search with a default subject type.
• By default search should not show the voided subjects.
• Can we get rid of title_lineage_locations_view join?

[vinayvenu]

Removing voided subjects and title_lineage_locations_view or adding subject type will not fix the search performance. Some analysis details.

1. The search performance will improve only if we get rid of the "distinct" AND the "order by name" in the queries. Neither of these are required for the default page. Alternatively, we can default to the search page itself and not the results to fix this performance.
2. In general, removing the "order by name" will be useful on all search results. We are anyway paginating by individual_id, so pagination will not suffer.
3. Removing distinct will work in all cases where we search only at the subject or program enrolment level. For program enrolments, we might have a few duplicates, but I think that is ok.

[petmongrels]

More analysis is required for all the different search configurations to know what all needs to be functionally working before we fix performance.

[mahalakshme]

Test results: doesn't cover all cases - but covers the most commonly used cases. Since the fix that we are intending to do will not be for very specific cases and will be something that will impact all cases, didn't test more deeply. Found 2 cases not working: group subject and concept with scope program enrolment

• Name - anything matching partly
• Age - only exact age displays in search results
• Address - everything in say hierarchy level though not registered at that level displays
• Concepts: ○ Text - even if partially correct shows ○ Coded multiselect ○ Multiple filters applied together works ○ Numeric range widget working ○ Concepts with scope program encounter ○ Numeric concept with scope program enrolment - not working always as expected - RCH no in sakhiapp - seems to be used only in one implementation ○ GroupSubject concept filter doesn't appear - not working - used only in one implementation JSS
• Search all in - searches in registeration and enrolment observations ○ For all fields including numeric even if partially correct displays
• Sorted by name, id etc.,

[mahalakshme]

@petmongrels the above is good enough?

[petmongrels]

Dev Notes. Core issues.

• with RLS certain indexes don't work (fixed)
• distinct (significant impact on both cost and IO). Using distinct only when program enrolment is joined. (fixed)
• order by name (impact on cost, high impact on IO) (not fixed)
• Lineage Query (slight impact on both cost and IO) (not fixed)
• title_lineage_locations_view (huge impact on IO). Even standalone title_lineage_locations_view queries without joining with rest of the tables is leading to bad query plan with very high IO and cost. So implementing this in Java since the number of addresses are quite small per page. (fixed)