search

micronutrientsupport / database-architecture

The Postgres database code for the MAPS tool
3 stars 0 forks source link

Re-work FCT matching algorithm #349

Closed bgsandan closed 1 year ago

bgsandan commented 1 year ago

Rather than cross joining each micronutrient to the list of food genuses and then searching for each one individually, the function now searches the FCT list for each food genus and then when it finds a match it collects the nutrient values for all outstanding micronutrients present in that FCT, keeping a list of what it hasn't yet found.

Execution now takes ~5mins, for the whole function, compared to the previous ~40mins 🚀

In testing the matched data returns a different number of rows than the previous algorithm (392,040 vs 451,440). This is because the updated function only matches on MNs in the micronutrient table where is_user_visible is true (N=33) rather than all MNs (N=38). However $(441440/38)*33=392040$

Probably addresses #303 - though there may be more perf improvements available 😁

rbroth commented 1 year ago

I've created a branch and am implementing a bunch of formatting suggestions there; I'll open a merge request to this branch ASAP.