Further optimising Vinay's approach of using concept map

gistfile1.txt

Apparently STABLE and IMMUTABLE only if Postgres decides that calling the function is more expensive than using the cache. See this link (https://stackoverflow.com/questions/8529690/why-is-postgresql-calling-my-stable-immutable-function-multiple-times), where the “may” keyword is mentioned. We have to test this further and what determines postgres’ decision.

In the solution where we are passing the concept map, the size of the map matters. If the size is too big it would take very long for the lookup to resolve. So in the concept map creation if you provide the uuid, the response is way faster - 2.5 seconds compared to 20 seconds.

The key change in is concepts with clause, right below. We should follow this pattern, to keep the size of map small and faster lookup.

with concepts AS (
                    SELECT hstore((array_agg(c2.uuid))::text[], (array_agg(c2.name))::text[]) AS map
                    FROM concept
                     join concept_answer a on concept.id = a.concept_id
                     join concept c2 on a.answer_concept_id = c2.id
                    where concept.uuid in ('c922c13c-1fa2-42dd-a7e8-d234b0324870', '61ab6413-5c6a-4512-ab6e-7d5cd1439569', '476a0b71-485b-4a0a-ba6f-4f3cf13568ca', 'cd83afec-d147-42b2-bd50-0ca460dbd55f', 'aa6687c9-ba4d-49a3-9b3e-bba266eb6f32', '92475d77-7cdd-4976-98f0-3847939a95d1', '1eb73895-ddba-4ddb-992c-03225f93775c', '6617408e-b89e-4f2f-ab10-d818c5d7f1bd')
  ),

     group_details as (
                    select gs.group_subject_id                       as group_subject_id,
                           unnest(array_agg(gs.member_subject_id))   as member_subject_id,
                           unnest(array_agg(gr.role))                as member_role,
                           count(*)                                  as total_member_count,
                           unnest(array_agg(head.member_subject_id)) as head_of_household_id
                    from group_subject gs
                           join group_role gr on gs.group_role_id = gr.id
                           left join group_subject head on head.group_subject_id = gs.group_subject_id
                                                             and head.group_role_id = (select id from group_role where role = 'Head of household')
                    group by 1
  )

SELECT
       individual.id                                                              as "Ind.Id",
       individual.address_id                                                      as "Ind.address_id",
       individual.uuid                                                            as "Ind.uuid",
       individual.first_name                                                      as "Ind.first_name",
       individual.last_name                                                       as "Ind.last_name",
       g.name                                                                     as "Ind.Gender",
       individual.date_of_birth                                                   as "Ind.date_of_birth",
       extract(year from age(individual.date_of_birth)) || ' Years ' ||
       extract(month from age(individual.date_of_birth)) ||
       ' Months'                                                                  as "Age",
       (current_date - date(individual.date_of_birth)) / 365.0                    as "Age In Decimal",
       individual.date_of_birth_verified                                          as "Ind.date_of_birth_verified",
       individual.registration_date                                               as "Ind.registration_date",
       phc                                                                        as "Ind.phc",
       slum                                                                       as "Ind.slum",
       individual.is_voided                                                       as "Ind.is_voided",
       get_coded_string_value(household.observations -> 'c922c13c-1fa2-42dd-a7e8-d234b0324870',
                              concepts.map)::TEXT                                 as "Houshold.Religion",
       (household.observations ->> '60c44aa2-3635-487d-8962-43000e77d382')::TEXT as "Houshold.Caste (Free Text)",
       get_coded_string_value(household.observations -> '61ab6413-5c6a-4512-ab6e-7d5cd1439569',
                              concepts.map)::TEXT                                 as "Houshold.Caste Category",
       get_coded_string_value(household.observations -> '476a0b71-485b-4a0a-ba6f-4f3cf13568ca',
                              concepts.map)::TEXT                                 as "Houshold.Ration Card",
       (individual.observations ->> '38eaf459-4316-4da3-acfd-3c9c71334041')::TEXT as "Ind.Standard upto which schooling completed",
       get_coded_string_value(individual.observations -> 'cd83afec-d147-42b2-bd50-0ca460dbd55f',
                              concepts.map)::TEXT                                 as "Ind.Occupation",
       get_coded_string_value(individual.observations -> 'aa6687c9-ba4d-49a3-9b3e-bba266eb6f32',
                              concepts.map)::TEXT                                 as "Ind.Marital status",
       (individual.observations ->> 'd685d229-e06e-42f3-90c7-ca06d2fefe17')::DATE as "Ind.Date of marriage",
       get_coded_string_value(individual.observations -> '92475d77-7cdd-4976-98f0-3847939a95d1',
                              concepts.map)::TEXT                                 as "Ind.Whether sterilized",
       get_coded_string_value(individual.observations -> '1eb73895-ddba-4ddb-992c-03225f93775c',
                              concepts.map)::TEXT                                 as "Ind.Whether any disability",
       (individual.observations ->> 'f27e9504-81a3-48d9-b7cc-4bc90dbd4a30')::TEXT as "Ind.Disability",
       get_coded_string_value(individual.observations -> '6617408e-b89e-4f2f-ab10-d818c5d7f1bd',
                              concepts.map)::TEXT                                 as "Ind.Status of the individual",
       (individual.observations ->> '43c4860f-fccf-48c9-818a-191bc0f8d0cf')::TEXT as "Ind.Aadhaar ID",
       (individual.observations ->> '82fa0dbb-92f9-4ec2-9263-49054e64d909')::TEXT as "Ind.Contact Number",
       users.name                                                                 as "Asha name",
       household.first_name                                                       as "Household name",
       (household.observations ->> '24dabc3a-6562-4521-bd42-5fff11ea5c46')::TEXT  as "Household.Number",
       concat(head_of_household.first_name, ' ', head_of_household.last_name)     as "Head of household name",
       gd.total_member_count                                                      as "Total members",
       relation.name                                                              as "Relationship with head of household"
FROM individual individual
       CROSS JOIN concepts
       JOIN gender g ON g.id = individual.gender_id
       JOIN address_level ON individual.address_id = address_level.id
       JOIN audit au on individual.audit_id = au.id
       JOIN users on users.id = au.created_by_id
       JOIN ihmp_location_view ON individual.address_id = ihmp_location_view.lowest_id
       left join group_details gd on gd.member_subject_id = individual.id
       left join individual head_of_household on head_of_household.id = gd.head_of_household_id
       left join individual household on household.id = gd.group_subject_id
       left join individual_relationship ir on
    (ir.individual_a_id = gd.head_of_household_id and ir.individual_b_id = individual.id) or
    (ir.individual_b_id = gd.head_of_household_id and ir.individual_a_id = individual.id)
       left join individual_relationship_type relation on relation.id = ir.relationship_type_id
Where individual.subject_type_id = 1 and slum is not null;