OpenAtlas

Like suspected the reason was a combination of a feature and growing data. Because of #1752 (Add a place column for subunits to type view) and a lot of linked entities the view was slow because looking up the "root" place for every entry takes a while.
As a quick fix I disabled this feature and it's much faster now. I also switched THANADOS to the release_candidate branch which is safer for now.

I understand that the place column is very handy so I will look into it. But I'm not sure if I manage to solve the related performance issues before the next release in the upcoming weekend.
Worst case scenario is that we release without the #1752 feature which is still better than to have an unusable slow system.

I will keep you updated.

Thank you for the quick workaround!

If you could post details on the desired result and the data sources (tables, fields etc.) I will design the recursive query.

WITH RECURSIVE items AS (
    SELECT range_id, domain_id
    FROM model.link
    WHERE range_id IN (177676, 145352) AND property_code = 'P46'
    UNION
    SELECT l.range_id, l.domain_id FROM model.link l
    INNER JOIN items i ON
    l.range_id = i.domain_id
    AND l.property_code = 'P46'
) SELECT range_id, domain_id FROM items;

This should be possible, we looked at an working example given here: https://thanados.openatlas.eu/entity/25. But table structure is different and we didn't manage to adapt it to work for our case too.
Help with that would be appreciated. Feel free to ask if more information is needed.
Bonus points if the result also contains the place name, than we could use that to build a link :)

This should do the job:

WITH RECURSIVE parent_tree AS (
  SELECT p.parent_id, p.child_id, ARRAY [p.child_id] AS path, 1 AS depth
  FROM (SELECT domain_id as parent_id, range_id as child_id FROM model.link WHERE property_code = 'P46') p
  WHERE p.child_id IN (177676, 145352, 81239, 176118)
  UNION ALL
  SELECT t.parent_id, t.child_id, pt.path || ARRAY [t.child_id], pt.depth + 1
  FROM (SELECT domain_id as parent_id, range_id as child_id FROM model.link WHERE property_code = 'P46') t
  JOIN parent_tree pt ON pt.parent_id = t.child_id
), 
root_nodes AS (
  SELECT DISTINCT ON (path[1]) path[1] AS child_id, parent_id AS top_level
  FROM parent_tree
  WHERE parent_id IS NOT NULL
  ORDER BY path[1], depth DESC
)
SELECT DISTINCT a.child_id AS start_node, r.top_level, e.name
FROM root_nodes r
JOIN parent_tree a ON a.child_id = r.child_id
JOIN model.entity e ON e.id = r.top_level
ORDER BY a.child_id

It gets the top level parent of a respective starting point entity and the name of that top level entity.

The array of child_ids, in this example "(177676, 145352, 81239, 176118)" should be modified to your needs as well as the property code.

Also if the property is inverse e.g. if the domain_id does not mark the parent but the range_id (e.g. for P127) then exchange domain_id and range_id in the subquery:

WITH RECURSIVE parent_tree AS (
  SELECT p.parent_id, p.child_id, ARRAY [p.child_id] AS path, 1 AS depth
  FROM (SELECT range_id as parent_id, domain_id as child_id FROM model.link WHERE property_code = 'P127') p
  WHERE p.child_id IN (26469, 26467)
  UNION ALL
  SELECT t.parent_id, t.child_id, pt.path || ARRAY [t.child_id], pt.depth + 1
  FROM (SELECT range_id as parent_id, domain_id as child_id FROM model.link WHERE property_code = 'P127') t
  JOIN parent_tree pt ON pt.parent_id = t.child_id
), 
root_nodes AS (
  SELECT DISTINCT ON (path[1]) path[1] AS child_id, parent_id AS top_level
  FROM parent_tree
  WHERE parent_id IS NOT NULL
  ORDER BY path[1], depth DESC
)
SELECT DISTINCT a.child_id AS start_node, r.top_level, e.name
FROM root_nodes r
JOIN parent_tree a ON a.child_id = r.child_id
JOIN model.entity e ON e.id = r.top_level
ORDER BY a.child_id

I decided to make the function more generalized now when already at it.
So now we have the function get_roots() at model/entity which takes a property code, a list of ids and an inverse flag as parameters.
It returns a dict with the provided ids as keys and a dict with the root id and root name (needed to build links) as values. For root places it would look like this.

Entity.get_roots('P46', [123, 128], inverse=True)
{
   123: {'id': 5, 'name': 'first_root_name'},
   128: {'id': 8, 'name': 'second_root_name'}
}