The recursive query for aggregate views can be slow

[imsdu]

Describe the bug
The query related to aggregate views tends to be slow in production

2023-04-27 07:13:01 WARN  k.i.j.JdbcInstrumentation$LoggingProcessors$WarnOnSlowStatement - Query execution exceeded the [2000000000 nanoseconds] threshold and lasted [2050037418 nanoseconds]. The query was: [
 
 WITH RECURSIVE recursive_dependencies(org, project, id) AS (
 SELECT target_org, target_project, target_id
 FROM entity_dependencies
 WHERE org = ? and project = ? and id = ?
 UNION
 SELECT e.target_org, e.target_project, e.target_id
 FROM entity_dependencies e, recursive_dependencies r
 WHERE e.org = r.org
 AND e.project = r.project
 AND e.id = r.id
 ) CYCLE id SET is_cycle USING path 
 SELECT s.value
 FROM recursive_dependencies d, scoped_states s
 WHERE s.org = d.org
 AND s.project = d.project
 AND s.id = d.id
       ]

Expected behavior
The query should be reworked to be faster