Fix performance of location and locationMapping sync

[vinayvenu]

location and locationMapping syncs perform badly when there are a lot of locations. Looking at the queries, both use the @> operator, which does not use indices when in an RLS context. Moving these out of RLS can improve the cost of these queries (from around 64,000 to 700 based on test against a decent sized database with around 500k records and 50k records for the organisation).

Acceptance criteria

• Response should give same results, but perform better for /location and /locationMapping sync queries

select al1.*
from catchment c
         inner join catchment_address_mapping cam on [c.id](http://c.id/) = cam.catchment_id
         inner join address_level al on cam.addresslevel_id = [al.id](http://al.id/)
         inner join address_level al1 on al.lineage @> al1.lineage
where [c.id](http://c.id/) = 21182
  and al1.last_modified_date_time > '2021-01-01'
  limit 100 offset 100;

select llm.*
from catchment c
         inner join catchment_address_mapping cam on [c.id](http://c.id/) = cam.catchment_id
         inner join address_level al on cam.addresslevel_id = [al.id](http://al.id/)
         inner join address_level al1 on al.lineage @> al1.lineage and [al.id](http://al.id/) <> [al1.id](http://al1.id/)
         inner join location_location_mapping llm on [al1.id](http://al1.id/) = llm.location_id
where [c.id](http://c.id/) = 21182
  and llm.last_modified_date_time between '2021-01-01' and '2024-10-01'
order by llm.last_modified_date_time asc, [llm.id](http://llm.id/) asc
limit 100 offset 100;

[petmongrels]

For testing

• pls check sync for organisations that have complex location hierarchy and verify whether all locations have synced to the device for the user
• also check incremental location sync

[AchalaBelokar]

• [x] tested with goonj which is having more location.. checked with different catchment
• [x] checked with rwb which is having more location and checked on client
• [x] tried with lahi which is having large location ...