Optimise this report query

[mahalakshme]

Issue:

This query, going to be used on superset reports for APF org, is very slow - takes 37 secs when load on prodread db is low

Technical analysis:

Done using explain analyse

1. Replacing c.base_date::DATE + ((cicv.item_detail_status::JSON ->> 0)::JSON ->> 'end')::INT AS due_date, with c.base_date::DATE + cicv.item_detail_end/(86400) AS due_date, saves 15 secs since we are avoiding JSON parsing.
2. Adding index on ETL address tables on id column - might help since nested loop join is done between ETL address and public.individual table
3. To check if the slowness caused by join with checklist_item_checklist_view can be minimised, dumped the data of view into apf_temp_from_view table in prerelease, created an index on it and used this table instead of the view. But found it to be slow. Further analysis here can be made.

AC:

• Identify all hotspots and suggest ways to optimise
• Is creating ETL tables for checklist related querying, is worth the effort?
• Add the optimised query here

Out of scope:

Correcting the logic of the query which is incorrect

[himeshr]

• Updated the optimised query here

• Adding index on ETL address tables on id column was very helpful.

• postgres12 automoatically does materialization of view, so no need to do it explicitly

Query Plan is available here for reference https://explain.dalibo.com/plan/d705hf8faae4893f

[himeshr]

Sample org Address Table schema before and after ETL run to create index for Address "ID" columns

[himeshr]

If needed, implementation team can manually create index for their target organisation address.id column using command

set role <db_schema>;
create unique index concurrently address_id_idx on address (id);

[AchalaBelokar]

As per discussion with @himeshr no need to QA this card.