[DEA] Search is slow

[mahalakshme]

https://avni.freshdesk.com/a/tickets/4778 and many more tickets

AC:

• Search in DEA esp., concept answers, name, address, Search All are very slow Eg: in JSCS, search by SS takes around 2 mins
• Overall search in DEA in any way should not take more than 5 secs. If not possible to reduce than this, you can comment in the card.
• Search for subjects by name in the subject concept in the form in DEA also need to be within 5 secs.
• We can do a devbox for this, where after development, - me, QA and the developer can discuss to see if the improvement is satisfactory and to decide on the testing strategy.
• Also find the root-cause for the performance getting downgraded after 10.0.1: https://github.com/avniproject/avni-webapp/issues/1360#issuecomment-2422403006
• With respect to functionality, we can remove the sorting that happens by default during search, but users should continue to have a way to sort(in whatever columns now available) by clicking up/down arrow icon. All other features, w.r to functionality should continue to work like before

Technical suggestions:

https://gist.github.com/petmongrels/353507f954ed0b31d434638ecacb90d0

• Search in subject type concept in DEA forms, need not do count query
• Render count on DEA search results, on click of a button
• indexes on name -

  Using pg_trgm , you can create a GiST or GIN index to speed up similarity search queries. This also helps regular expression-based searches, such as with LIKE and ILIKE operators.

• sort by first name not by all

Old: ignore:

AC:

• Search by anything in Data Entry App should not take more than 5 secs(do not move the card to next lane without this criteria getting satisfied) - search by concept filters(JSCS, Goonj are good candidates), Search All, name, DOB, Address, Registration date filters, etc., - ie., all possible and combination of filters.
• Validate the same using gatling performance setup since seems like the issue is raised now repeatedly for different reasons and also to make sure regression issues doesn't arise with respect to performance from our end. Add readme/steps for running the tests so that the tech tester can verify the same.
• The above also includes when searching by name for subject type concept. Currently(with the fixes made so far) this takes around 30secs the first time

Technical suggestions:

• This is a suggestion made in the 1st card created. Why cant we just add a index on name fields - comment the reason for the same if not possible
• Search in subject type concept in DEA forms, need not do count query
• Search in DEA main search - displaying count is needed.
• Render count on click of a button
• indexes on name -

  Using pg_trgm , you can create a GiST or GIN index to speed up similarity search queries. This also helps regular expression-based searches, such as with LIKE and ILIKE operators.

• sort by first name not by all

Inputs:

• ETL - strategy pattern
• latest registrations/
• another db - Elastic search
• should not take more than one hour for any org
• name - ?
• Elastic search db
• postgres will bumping up resources help?

Out of scope:

• removing sort, limited records ? - needs more analysis

some msg

[petmongrels]

https://avni.freshdesk.com/a/tickets/4778

[petmongrels]

https://gist.github.com/petmongrels/353507f954ed0b31d434638ecacb90d0

[mahalakshme]

@petmongrels for the 3rd case, I see the query still has distinct in the query. Cant understand what will be the effect on the search results. Aren't we searching in the latest encounter alone or in the latest enrolment alone?

[mahalakshme]

• I think by default we need not sort, but we can retain the sorting icon on the Name and whatever columns have it already, so that users can use it if needed.

• also other than sort(currently by default done), the understanding is that no other functionality of Avni will get affected.

[1t5j0y]

Changes made:

1. Don't order search results by default. (Users can still sort by specific columns after getting search results.)
2. Use exists by query to eliminate use of distinct
3. Calculate count only for initial search. Pagination or sorting on a search result will use count already calculated for initial search.
4. Added logging to keep track of time taken by DEA searches (across results and count queries) for later analysis.

Observation:

• Count accounts for >90% of the time taken in several cases so getting rid of it or showing an approximate count will help a lot.

Areas for further investigation for improving performance:

1. Bumping up db memory does improve performance for queries using joins which indicates that there might be scope for db tuning on existing instance or that it is time to bump db resources if current performance improvement isn't sufficient.

-- 1.1. Resource bump performance for joins (rough indication based on manual checks) JSCS coded concept search - db.t3.medium (1.83 minutes), db.t3.large (36.22s) Note: With the current code change, this search now roughly takes 34s on db.t3.medium

-- 1.2. DB Tuning further reading and config recommendations

• https://pgtune.leopard.in.ua/?dbVersion=12&osType=linux&dbType=mixed&cpuNum=2&totalMemory=4&totalMemoryUnit=GB&connectionNum=100&hdType=ssd
• https://aws.amazon.com/blogs/database/determining-the-optimal-value-for-shared_buffers-using-the-pg_buffercache-extension-in-postgresql/

[1t5j0y]

Removed calculation of count on server and display of count on webapp as agreed. Concept and name searches for JSCS are well under 5 seconds now (tested against prerelease) .

[mahalakshme]

@petmongrels you are reviewing this no?