Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

get_paginate_workflow_app_logs experiencing slow query #14752

Open
5 tasks done
horochx opened this issue Mar 3, 2025 · 1 comment · May be fixed by #14710
Open
5 tasks done

get_paginate_workflow_app_logs experiencing slow query #14752

horochx opened this issue Mar 3, 2025 · 1 comment · May be fixed by #14710

Comments

@horochx
Copy link
Contributor

horochx commented Mar 3, 2025

Self Checks

  • I have searched for existing issues search for existing issues, including closed ones.
  • I confirm that I am using English to submit this report (我已阅读并同意 Language Policy).
  • [FOR CHINESE USERS] 请务必使用英文提交 Issue，否则会被关闭。谢谢！:）
  • Please do not modify this template :) and fill in all the required fields.

1. Is this request related to a challenge you're experiencing? Tell me about your story.

Currently, the primary slow queries in our database are originating from the get_paginate_workflow_app_logs method of the WorkflowAppService. I've noticed that there might be room for optimization in the index of the WorkflowAppLog table.

SQL：

SELECT count(*) AS count_1 
FROM (SELECT workflow_app_logs.id AS id, workflow_app_logs.tenant_id AS tenant_id, workflow_app_logs.app_id AS app_id, workflow_app_logs.workflow_id AS workflow_id, workflow_app_logs.workflow_run_id AS workflow_run_id, workflow_app_logs.created_from AS created_from, workflow_app_logs.created_by_role AS created_by_role, workflow_app_logs.created_by AS created_by, workflow_app_logs.created_at AS created_at 
FROM workflow_app_logs JOIN workflow_runs ON workflow_runs.id = workflow_app_logs.workflow_run_id LEFT OUTER JOIN end_users ON workflow_runs.created_by = end_users.id AND workflow_runs.created_by_role = 'end_user'

2. Additional context or comments

No response

3. Can you help us with this feature?

  • I am interested in contributing to this feature.
@dosubot DosuBot
Copy link

dosubot bot commented Mar 3, 2025

Hey @horochx! 👋 I'm here to help you with bug fixes, answer your questions, and guide you as a potential contributor. Let me know how I can assist you while you wait for a human maintainer.

To address the slow queries in the get_paginate_workflow_app_logs method, you can optimize the indexing strategy on the WorkflowAppLog table. Here are some suggestions:

  1. Composite Index on tenant_id and app_id: This index already exists and helps with filtering by these columns [1].

  2. Index on workflow_run_id: Ensure there is an index on workflow_run_id to optimize the join with the WorkflowRun table [2].

  3. Index on created_at: Adding an index on created_at can improve the performance of ordering operations [2].

  4. Indexes on inputs and outputs: If these columns are frequently used in ilike conditions, consider creating indexes to speed up text searches [2].

  5. Index on created_by and created_by_role: Consider indexing these columns to optimize the join with the EndUser table [2].

Implementing these indexes should help reduce the time taken to filter, join, and order the data, thus improving the performance of the get_paginate_workflow_app_logs method.

To continue talking to Dosu, mention @dosu.

Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other

@horochx horochx linked a pull request Mar 3, 2025 that will close this issue
Open
5 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

perf: optimizing db WorkflowAppLog index horochx/dify
1 participant
@horochx