We need to improve this SQL

[dadiorchen]

The dag: refresh_webmap_db_org_location_view is trying to update the view in db:

org_location_view, the SQL of creating this materialized view is shown below, the problem is that it is too slow, and the updating will block following request against this view, so it makes everything down and gives a cascaded bad impact.

So to solve this problem, two possible solution:

1. Find a way to safely update the view without blocking following request.
2. Improve the performance of this SQL ( now it takes hours)

 SELECT entity.id,
    country.id AS country_id,
    country.name AS country_name,
    continent.id AS continent_id,
    continent.name AS continent_name
   FROM entity
     JOIN trees ON trees.id = (( SELECT tr.id
           FROM trees tr
          WHERE (tr.planter_id IN ( SELECT planter.id
                   FROM planter
                  WHERE (planter.organization_id IN ( SELECT getentityrelationshipchildren.entity_id
                           FROM getentityrelationshipchildren(entity.id) getentityrelationshipchildren(entity_id, p
arent_id, depth, type, relationship_role)))))
         LIMIT 1))
     JOIN region country ON st_within(trees.estimated_geometric_location, country.geom) AND (country.type_id IN ( S
ELECT region_type.id
           FROM region_type
          WHERE region_type.type::text = 'country'::text))
     JOIN region continent ON st_within(trees.estimated_geometric_location, continent.geom) AND (continent.type_id
IN ( SELECT region_type.id
           FROM region_type
          WHERE region_type.type::text = 'continents'::text))
  WHERE entity.type::text = 'o'::text OR entity.type::text = 'O'::text;