[DEA] Search Performance Improvement with custom Search filters

[mahalakshme]

Search of observations(via Search All) and name via like operator is not fast. This is the place where most of the slow queries w.r to Search are executed. Identified from newrelic. One reason is looks like GIN index is not used and right indices not added on name columns, and others.

AC:

• When search done with 'Search All' should be faster
• When search done with 'Name' Search filter should be faster.
• Comment with performance improvement made before and after here in the column Time taken after improvement
• Search with other filters also like Gender, age, Address, date filters etc., wherever possible make it faster.
• Search without any params should be fast
• Currently in JSCS or Ashwini - when SearchAll or search by Name is done - it takes more than 30 secs. Search by anything should take less than or equal to 3 secs.

Technical analysis:

• Search already done as openchs user.
• But when searching for observations, make sure GIN index is used. Modify the query with right operators if not. Considering the search with observations is slow(like in the above sheet for ashwini and many such queries in newrelic), good to make sure this.
• For name, adding pg_trgm - https://niallburkley.com/blog/index-columns-for-like-in-postgres/ might help to search faster
• also add index on other applicable columns like age, gender etc.,

[petmongrels]

As is

https://explain.dalibo.com/plan/3e9977614f1gbc7c
Cost = 24000

Simplifying query for subject type

https://explain.dalibo.com/plan/d93869c16a48c705
Cost = 4200

Not seeing like, date, or age queries show up in the explain results, so not optimising for it.

Some other plans:

• https://explain.dalibo.com/plan/7hg82f5gabea99c5
• https://explain.dalibo.com/plan/49cb562046624h1f