Content
Added by gabriel ramirez over 1 year ago
I have my environment on a postgresql database and the following statement takes 24 seconds to complete:
SELECT SUM (hours)
FROM "work_packages"
LEFT OUTER JOIN "work_package_hierarchies"
ON "work_packages"."id" =
"work_package_hierarchies"."ancestor_id"
AND "work_packages"."id" = 47386
LEFT OUTER JOIN "work_packages" "descendants"
ON "work_package_hierarchies"."descendant_id" =
"descendants"."id"
AND "descendants"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
LEFT OUTER JOIN "members"
ON "projects"."id" =
"members"."project_id"
AND "members"."user_id" = 574
AND "projects"."active" = TRUE
INNER JOIN "enabled_modules"
ON "projects"."id" =
"enabled_modules"."project_id"
AND "enabled_modules"."name" IN
('work_package_tracking')
AND "projects"."active" = TRUE
INNER JOIN "role_permissions"
ON "role_permissions"."permission" IN
('view_work_packages')
INNER JOIN "roles" "permission_roles"
ON "permission_roles"."id" =
"role_permissions"."role_id"
LEFT OUTER JOIN "member_roles"
ON "members"."id" = "member_roles"."member_id"
LEFT OUTER JOIN "roles" "assigned_roles"
ON "assigned_roles"."id" =
"permission_roles"."id"
AND "projects"."active" = TRUE
AND ( "assigned_roles"."id" =
"member_roles"."role_id"
OR "projects"."public" = TRUE
AND "assigned_roles"."builtin" = 1
AND "member_roles"."id" IS NULL)
WHERE "assigned_roles"."id" IS NOT NULL)
LEFT OUTER JOIN "time_entries"
ON "time_entries"."work_package_id" = "descendants"."id"
AND "time_entries"."id" IN
(SELECT "time_entries"."id"
FROM "time_entries"
WHERE ( "time_entries"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
LEFT OUTER JOIN "members"
ON "projects"."id" =
"members"."project_id"
AND "members"."user_id" =
574
AND "projects"."active" =
TRUE
INNER JOIN "enabled_modules"
ON "projects"."id" =
"enabled_modules"."project_id"
AND "enabled_modules"."name" IN
('costs')
AND "projects"."active" =
TRUE
INNER JOIN "role_permissions"
ON "role_permissions"."permission" IN
('view_time_entries')
INNER JOIN
"roles" "permission_roles"
ON "permission_roles"."id" =
"role_permissions"."role_id"
LEFT OUTER JOIN "member_roles"
ON "members"."id" =
"member_roles"."member_id"
LEFT OUTER JOIN
"roles" "assigned_roles"
ON "assigned_roles"."id" =
"permission_roles"."id"
AND "projects"."active" =
TRUE
AND ( "assigned_roles"."id" =
"member_roles"."role_id"
OR "projects"."public" =
TRUE
AND "assigned_roles"."builtin" =
1
AND "member_roles"."id"
IS NULL)
WHERE "assigned_roles"."id" IS NOT NULL)
OR "time_entries"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
LEFT OUTER JOIN "members"
ON "projects"."id" =
"members"."project_id"
AND "members"."user_id" =
574
AND "projects"."active" =
TRUE
INNER JOIN "enabled_modules"
ON "projects"."id" =
"enabled_modules"."project_id"
AND "enabled_modules"."name" IN
('costs')
AND "projects"."active" =
TRUE
INNER JOIN "role_permissions"
ON "role_permissions"."permission" IN
('view_own_time_entries')
INNER JOIN
"roles" "permission_roles"
ON "permission_roles"."id" =
"role_permissions"."role_id"
LEFT OUTER JOIN "member_roles"
ON "members"."id" =
"member_roles"."member_id"
LEFT OUTER JOIN
"roles" "assigned_roles"
ON "assigned_roles"."id" =
"permission_roles"."id"
AND "projects"."active" =
TRUE
AND ( "assigned_roles"."id" =
"member_roles"."role_id"
OR "projects"."public" =
TRUE
AND "assigned_roles"."builtin" =
1
AND "member_roles"."id"
IS NULL)
WHERE "assigned_roles"."id"
IS NOT NULL)
AND "time_entries"."user_id" = 574))
WHERE "work_packages"."id" = 47386
GROUP BY "work_packages"."id"
This sentence is internal and affects the performance of the app, how can we solve it?
Replies (3)
Optimizing a complex SQL query like the one you provided can involve several steps. Here's a general approach you can take to improve the performance of this query:
Run an EXPLAIN on the query to understand how the database is executing it. This will help you identify which parts of the query are causing performance issues and whether indexes are being used effectively.
Ensure that appropriate indexes are present on columns used in joins, filters, and group by operations. In particular, indexes on foreign keys and columns used in joins and where conditions can significantly speed up query execution.
Subqueries within the IN clauses and elsewhere can be performance-intensive. Consider using JOIN operations instead of subqueries where possible, as joins can often be optimized better by the database engine.
Review the conditions and eliminate any redundant or unnecessary ones. This can help the optimizer make better decisions about how to execute the query.
The query has multiple left outer joins and complex conditions. Simplify and restructure the query if possible to reduce the number of joins or simplify the conditions.
If some of the data is relatively static, consider implementing caching mechanisms to store the results of this query, reducing the need to execute it frequently.
Consider the table structure and whether normalization or denormalization could be beneficial. Denormalization might help if the data is read-heavy and involves complex joins.
Depending on the volume of data, consider partitioning tables to improve query performance. This can be particularly useful for large datasets.
If the data doesn't change rapidly, you could create materialized views that store the results of this query and refresh them periodically.
Sometimes, performance issues might not be solely due to the query. Review the application logic to ensure that you're not inadvertently triggering multiple executions of this query in a short span of time.
Regularly analyze and optimize the database, including rebuilding indexes and updating statistics.
If possible, consider whether some of the aggregation logic can be moved to the application layer rather than executing it in the database. This can reduce the load on the database.
I hope you like my reply.
Hi Gabriel,
first of all, please share what your aiming to do with that query, if you need more sophisticated help. The previous comment already shows, how you may optimize the query.
Second, instead of working with an SQL query at all, you may consider using the API instead. With the API you can set multiple filters reducing the complexity of your query substantially.
Okay, I think I figured it out. All the documentation I've read indicates that it shouldn't be required—in fact, it shouldn't really matter—but in the end, this worked for me.
I've included
SetEnv GEM_PATH /home/openproject/openproject/vendor/bundle/ruby/3.1.0/doodle cricket(Obviously, the file names and paths may differ for other users.)