langgenius/dify
1
0
Fork 0
mirror of https://github.com/langgenius/dify.git synced 2026-07-23 13:05:55 -04:00
Code Issues 775 Packages Projects Releases 10 Wiki Activity

Performance Degradation: Slow SQL Queries on Conversations Table with Large Dataset #21891

New Issue
Open
opened 2026-02-21 20:14:45 -05:00 by yindo · 1 comment
yindo commented 2026-02-21 20:14:45 -05:00
Owner
Copy Link
Copy Source

Originally created by @scdeng on GitHub (Jan 25, 2026).

Originally assigned to: @snakevash, @scdeng, @laipz8200 on GitHub.

Self Checks

  • I have read the Contributing Guide and Language Policy.
  • This is only for bug report, if you would like to ask a question, please head to Discussions.
  • I have searched for existing issues search for existing issues, including closed ones.
  • I confirm that I am using English to submit this report, otherwise it will be closed.
  • 【中文用户 & Non English User】请使用英语提交，否则会被关闭 ：）
  • Please do not modify this template :) and fill in all the required fields.

Dify version

1.11.2

Cloud or Self Hosted

Self Hosted (Source)

Steps to reproduce

  1. Deploy Dify with PostgreSQL containing 3M+ conversations, 3M+ messages, 39k+ end_users
  2. Navigate to any app's logs page: /app/[appId]/logs
  3. Observe the page load time: 5-30 seconds ⚠️
  4. Attempt to search or filter conversations: 10-60 seconds ⚠️
  5. Attempt pagination: Additional 5-30 second delays

Environment

  • Dify Version: 1.11.2
  • Database: PostgreSQL 17+
  • Data Volume:
    • conversations table: 3,686,787 rows
    • messages table: 3,791,337 rows
    • end_users table: 39,590 rows
  • Deployment: Production with real-world data

✔️ Expected Behavior

  • Logs page should load within < 1 second
  • Search/filter results should display within < 500ms
  • Pagination should work smoothly
  • System should remain responsive with high query volume

❌ Actual Behavior

  • Logs page takes 5-30 seconds to load initial data
  • Search queries timeout or return delayed results after 10-60 seconds
  • Pagination causes additional 5-30 second delays
  • System becomes unresponsive with concurrent users viewing logs
  • Database connections get exhausted with large result sets

Slow Queries Identified

Query 1: List conversations with full-text search (Most Critical)

SELECT conversations.id, conversations.app_id, conversations.app_model_config_id, 
       conversations.model_provider, conversations.override_model_configs, 
       conversations.model_id, conversations.mode, conversations.name, 
       conversations.summary, conversations.inputs, conversations.introduction, 
       conversations.system_instruction, conversations.system_instruction_tokens, 
       conversations.status, conversations.invoke_from, conversations.from_source, 
       conversations.from_end_user_id, conversations.from_account_id, conversations.read_at, 
       conversations.read_account_id, conversations.dialogue_count, conversations.created_at, 
       conversations.updated_at, conversations.is_deleted 
FROM conversations 
JOIN messages ON messages.conversation_id=conversations.id 
JOIN (
  SELECT conversations.id AS conversation_id, 
         end_users.session_id AS from_end_user_session_id 
  FROM conversations 
  LEFT OUTER JOIN end_users ON conversations.from_end_user_id=end_users.id
) AS anon_1 ON anon_1.conversation_id=conversations.id 
WHERE conversations.app_id=?::UUID 
  AND conversations.is_deleted IS false 
  AND (messages.query ILIKE ? 
       OR messages.answer ILIKE ? 
       OR conversations.name ILIKE ? 
       OR conversations.introduction ILIKE ? 
       OR anon_1.from_end_user_session_id ILIKE ?) 
  AND conversations.created_at>=?::timestamptz 
  AND conversations.created_at<=?::timestamptz 
  AND conversations.invoke_from!=? 
GROUP BY conversations.id 
ORDER BY conversations.created_at DESC 
LIMIT ? OFFSET ?;

Performance: 5-30 seconds | Execution Plan: Full table scan on messages (3.7M rows)

Query 2: Count conversations with full-text search (For pagination)

SELECT count(*) AS count_1 
FROM (
  SELECT conversations.id, conversations.app_id, ... [23 fields] 
  FROM conversations 
  JOIN messages ON messages.conversation_id=conversations.id 
  JOIN (
    SELECT conversations.id AS conversation_id, 
           end_users.session_id AS from_end_user_session_id 
    FROM conversations 
    LEFT OUTER JOIN end_users ON conversations.from_end_user_id=end_users.id
  ) AS anon_1 ON anon_1.conversation_id=conversations.id 
  WHERE conversations.app_id=?::UUID 
    AND conversations.is_deleted IS false 
    AND (messages.query ILIKE ? 
         OR messages.answer ILIKE ? 
         OR conversations.name ILIKE ? 
         OR conversations.introduction ILIKE ? 
         OR anon_1.from_end_user_session_id ILIKE ?) 
    AND conversations.created_at>=?::timestamptz 
    AND conversations.created_at<=?::timestamptz 
    AND conversations.invoke_from!=? 
  GROUP BY conversations.id
) AS anon_2;

Performance: 10-60 seconds | Issues: Selects 23 unnecessary fields, then counts

Query 3: List conversations (basic, no search)

SELECT conversations.id, conversations.app_id, conversations.app_model_config_id, ...
FROM conversations 
WHERE conversations.app_id=?::UUID 
  AND conversations.is_deleted IS false 
  AND conversations.created_at>=?::timestamptz 
  AND conversations.created_at<=?::timestamptz 
  AND conversations.invoke_from!=? 
ORDER BY conversations.created_at DESC 
LIMIT ? OFFSET ?;

Performance: 2-10 seconds | Execution Plan: Full table scan on conversations (3.6M rows)

Query 4: Count conversations (basic, no search)

SELECT count(*) AS count_1 
FROM (
  SELECT conversations.id, conversations.app_id, ... [23 fields]
  FROM conversations 
  WHERE conversations.app_id=?::UUID 
    AND conversations.is_deleted IS false 
    AND conversations.created_at>=?::timestamptz 
    AND conversations.created_at<=?::timestamptz 
    AND conversations.invoke_from!=?
) AS anon_1;

Performance: 1-5 seconds | Issues: Unnecessary subquery and field selection

Image
Originally created by @scdeng on GitHub (Jan 25, 2026). Originally assigned to: @snakevash, @scdeng, @laipz8200 on GitHub. ### Self Checks - [x] I have read the [Contributing Guide](https://github.com/langgenius/dify/blob/main/CONTRIBUTING.md) and [Language Policy](https://github.com/langgenius/dify/issues/1542). - [x] This is only for bug report, if you would like to ask a question, please head to [Discussions](https://github.com/langgenius/dify/discussions/categories/general). - [x] I have searched for existing issues [search for existing issues](https://github.com/langgenius/dify/issues), including closed ones. - [x] I confirm that I am using English to submit this report, otherwise it will be closed. - [x] 【中文用户 & Non English User】请使用英语提交，否则会被关闭 ：） - [x] Please do not modify this template :) and fill in all the required fields. ### Dify version 1.11.2 ### Cloud or Self Hosted Self Hosted (Source) ### Steps to reproduce 1. Deploy Dify with PostgreSQL containing 3M+ conversations, 3M+ messages, 39k+ end_users 2. Navigate to any app's logs page: `/app/[appId]/logs` 3. Observe the page load time: **5-30 seconds** ⚠️ 4. Attempt to search or filter conversations: **10-60 seconds** ⚠️ 5. Attempt pagination: **Additional 5-30 second delays** ## Environment - **Dify Version**: 1.11.2 - **Database**: PostgreSQL 17+ - **Data Volume**: - `conversations` table: 3,686,787 rows - `messages` table: 3,791,337 rows - `end_users` table: 39,590 rows - **Deployment**: Production with real-world data ### ✔️ Expected Behavior - Logs page should load within **< 1 second** - Search/filter results should display within **< 500ms** - Pagination should work smoothly - System should remain responsive with high query volume ### ❌ Actual Behavior - Logs page takes **5-30 seconds** to load initial data - Search queries timeout or return delayed results after **10-60 seconds** - Pagination causes additional 5-30 second delays - System becomes unresponsive with concurrent users viewing logs - Database connections get exhausted with large result sets #### Slow Queries Identified **Query 1: List conversations with full-text search** (Most Critical) ```sql SELECT conversations.id, conversations.app_id, conversations.app_model_config_id, conversations.model_provider, conversations.override_model_configs, conversations.model_id, conversations.mode, conversations.name, conversations.summary, conversations.inputs, conversations.introduction, conversations.system_instruction, conversations.system_instruction_tokens, conversations.status, conversations.invoke_from, conversations.from_source, conversations.from_end_user_id, conversations.from_account_id, conversations.read_at, conversations.read_account_id, conversations.dialogue_count, conversations.created_at, conversations.updated_at, conversations.is_deleted FROM conversations JOIN messages ON messages.conversation_id=conversations.id JOIN ( SELECT conversations.id AS conversation_id, end_users.session_id AS from_end_user_session_id FROM conversations LEFT OUTER JOIN end_users ON conversations.from_end_user_id=end_users.id ) AS anon_1 ON anon_1.conversation_id=conversations.id WHERE conversations.app_id=?::UUID AND conversations.is_deleted IS false AND (messages.query ILIKE ? OR messages.answer ILIKE ? OR conversations.name ILIKE ? OR conversations.introduction ILIKE ? OR anon_1.from_end_user_session_id ILIKE ?) AND conversations.created_at>=?::timestamptz AND conversations.created_at<=?::timestamptz AND conversations.invoke_from!=? GROUP BY conversations.id ORDER BY conversations.created_at DESC LIMIT ? OFFSET ?; ``` **Performance**: **5-30 seconds** | **Execution Plan**: Full table scan on messages (3.7M rows) --- **Query 2: Count conversations with full-text search** (For pagination) ```sql SELECT count(*) AS count_1 FROM ( SELECT conversations.id, conversations.app_id, ... [23 fields] FROM conversations JOIN messages ON messages.conversation_id=conversations.id JOIN ( SELECT conversations.id AS conversation_id, end_users.session_id AS from_end_user_session_id FROM conversations LEFT OUTER JOIN end_users ON conversations.from_end_user_id=end_users.id ) AS anon_1 ON anon_1.conversation_id=conversations.id WHERE conversations.app_id=?::UUID AND conversations.is_deleted IS false AND (messages.query ILIKE ? OR messages.answer ILIKE ? OR conversations.name ILIKE ? OR conversations.introduction ILIKE ? OR anon_1.from_end_user_session_id ILIKE ?) AND conversations.created_at>=?::timestamptz AND conversations.created_at<=?::timestamptz AND conversations.invoke_from!=? GROUP BY conversations.id ) AS anon_2; ``` **Performance**: **10-60 seconds** | **Issues**: Selects 23 unnecessary fields, then counts --- **Query 3: List conversations (basic, no search)** ```sql SELECT conversations.id, conversations.app_id, conversations.app_model_config_id, ... FROM conversations WHERE conversations.app_id=?::UUID AND conversations.is_deleted IS false AND conversations.created_at>=?::timestamptz AND conversations.created_at<=?::timestamptz AND conversations.invoke_from!=? ORDER BY conversations.created_at DESC LIMIT ? OFFSET ?; ``` **Performance**: **2-10 seconds** | **Execution Plan**: Full table scan on conversations (3.6M rows) --- **Query 4: Count conversations (basic, no search)** ```sql SELECT count(*) AS count_1 FROM ( SELECT conversations.id, conversations.app_id, ... [23 fields] FROM conversations WHERE conversations.app_id=?::UUID AND conversations.is_deleted IS false AND conversations.created_at>=?::timestamptz AND conversations.created_at<=?::timestamptz AND conversations.invoke_from!=? ) AS anon_1; ``` **Performance**: **1-5 seconds** | **Issues**: Unnecessary subquery and field selection --- <img width="2189" height="736" alt="Image" src="https://github.com/user-attachments/assets/b5b7b7f7-5c96-4556-aac6-fce9dde934ee" />
yindo added the 🐞 bug label 2026-02-21 20:14:45 -05:00
yindo commented 2026-02-21 20:14:46 -05:00
Author
Owner
Copy Link
Copy Source

@dosubot[bot] commented on GitHub (Jan 25, 2026):

The main reason for the slow queries is that Dify currently relies on ILIKE for full-text search on large tables (messages and conversations), which leads to full table scans since there are no GIN/GIST or tsvector indexes on the relevant text columns (messages.query, messages.answer, conversations.name, conversations.introduction). This is a known limitation in the community edition and has been discussed in issues and discussions, but there are no built-in optimizations for this in the current schema or migrations [source].

To improve performance, consider these steps:

  • Add GIN indexes on the columns used in full-text search (e.g., messages.query, messages.answer, conversations.name, conversations.introduction). For example:

    CREATE INDEX messages_query_gin_idx ON messages USING GIN (to_tsvector('simple', query));
CREATE INDEX messages_answer_gin_idx ON messages USING GIN (to_tsvector('simple', answer));
CREATE INDEX conversations_name_gin_idx ON conversations USING GIN (to_tsvector('simple', name));
CREATE INDEX conversations_intro_gin_idx ON conversations USING GIN (to_tsvector('simple', introduction));

    Then refactor your queries to use to_tsvector/to_tsquery instead of ILIKE for these fields.

  • Rewrite count queries to select only the necessary fields (ideally just id) and avoid unnecessary subqueries and field selection, which currently add overhead.

  • Reduce the number of columns selected in list queries to only those needed for the UI.

  • For long-term scalability, consider partitioning large tables (messages, conversations) by time (e.g., created_at) to limit the amount of data scanned per query and enable instant cleanup [source].

  • If you need to keep expensive counts fast, consider caching counts in Redis or using materialized views, as repeated COUNT(*) on large tables is inherently slow [source].

There are no migrations or PRs in the community edition that add full-text search indexes or advanced optimizations for these tables yet. These changes require schema migrations and some ORM query refactoring, but they are the most effective way to address the performance bottleneck you’re seeing.

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other

Chat with Dosu Open in Cursor Join Discord Share on X

@dosubot[bot] commented on GitHub (Jan 25, 2026): <!-- Answer --> The main reason for the slow queries is that Dify currently relies on ILIKE for full-text search on large tables (messages and conversations), which leads to full table scans since there are no GIN/GIST or tsvector indexes on the relevant text columns (messages.query, messages.answer, conversations.name, conversations.introduction). This is a known limitation in the community edition and has been discussed in issues and discussions, but there are no built-in optimizations for this in the current schema or migrations [[source](https://github.com/langgenius/dify/blob/b9f1d65d4fb601bf5b80eb9bb7df9e11b38fcc95/api/models/model.py#L703-L1660)]. To improve performance, consider these steps: - Add GIN indexes on the columns used in full-text search (e.g., messages.query, messages.answer, conversations.name, conversations.introduction). For example: ```sql CREATE INDEX messages_query_gin_idx ON messages USING GIN (to_tsvector('simple', query)); CREATE INDEX messages_answer_gin_idx ON messages USING GIN (to_tsvector('simple', answer)); CREATE INDEX conversations_name_gin_idx ON conversations USING GIN (to_tsvector('simple', name)); CREATE INDEX conversations_intro_gin_idx ON conversations USING GIN (to_tsvector('simple', introduction)); ``` Then refactor your queries to use `to_tsvector`/`to_tsquery` instead of ILIKE for these fields. - Rewrite count queries to select only the necessary fields (ideally just `id`) and avoid unnecessary subqueries and field selection, which currently add overhead. - Reduce the number of columns selected in list queries to only those needed for the UI. - For long-term scalability, consider partitioning large tables (messages, conversations) by time (e.g., created_at) to limit the amount of data scanned per query and enable instant cleanup [[source](https://github.com/langgenius/dify/issues/29776)]. - If you need to keep expensive counts fast, consider caching counts in Redis or using materialized views, as repeated COUNT(*) on large tables is inherently slow [[source](https://github.com/langgenius/dify/issues/27764#issuecomment-3479154550)]. There are no migrations or PRs in the community edition that add full-text search indexes or advanced optimizations for these tables yet. These changes require schema migrations and some ORM query refactoring, but they are the most effective way to address the performance bottleneck you’re seeing. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/afef6c2a-ab93-4912-9e43-43f093bf1b58?feedback_type=other)</sup> [![Chat with Dosu](https://dosu.dev/dosu-chat-badge.svg)](https://app.dosu.dev/6088a4f4-073a-43f3-b3d4-4f06fb0c05a0/ask?utm_source=github)&nbsp;[![Open in Cursor](https://dosu.dev/dosu-in-cursor.svg)](https://cursor.com/link/prompt?text=The%20main%20reason%20for%20the%20slow%20queries%20is%20that%20Dify%20currently%20relies%20on%20ILIKE%20for%20full-text%20search%20on%20large%20tables%20%28messages%20and%20conversations%29%2C%20which%20leads%20to%20full%20table%20scans%20since%20there%20are%20no%20GIN/GIST%20or%20tsvector%20indexes%20on%20the%20relevant%20text%20columns%20%28messages.query%2C%20messages.answer%2C%20conversations.name%2C%20conversations.introduction%29.%20This%20is%20a%20known%20limitation%20in%20the%20community%20edition%20and%20has%20been%20discussed%20in%20issues%20and%20discussions%2C%20but%20there%20are%20no%20built-in%20optimizations%20for%20this%20in%20the%20current%20schema%20or%20migrations%20%5B%5Bsource%5D%28https%3A//github.com/langgenius/dify/blob/b9f1d65d4fb601bf5b80eb9bb7df9e11b38fcc95/api/models/model.py%23L703-L1660%29%5D.%0A%0ATo%20improve%20performance%2C%20consider%20these%20steps%3A%0A%0A-%20Add%20GIN%20indexes%20on%20the%20columns%20used%20in%20full-text%20search%20%28e.g.%2C%20messages.query%2C%20messages.answer%2C%20conversations.name%2C%20conversations.introduction%29.%20For%20example%3A%0A%20%20%60%60%60sql%0A%20%20CREATE%20INDEX%20messages_query_gin_idx%20ON%20messages%20USING%20GIN%20%28to_tsvector%28%27simple%27%2C%20query%29%29%3B%0A%20%20CREATE%20INDEX%20messages_answer_gin_idx%20ON%20messages%20USING%20GIN%20%28to_tsvector%28%27simple%27%2C%20answer%29%29%3B%0A%20%20CREATE%20INDEX%20conversations_name_gin_idx%20ON%20conversations%20USING%20GIN%20%28to_tsvector%28%27simple%27%2C%20name%29%29%3B%0A%20%20CREATE%20INDEX%20conversations_intro_gin_idx%20ON%20conversations%20USING%20GIN%20%28to_tsvector%28%27simple%27%2C%20introduction%29%29%3B%0A%20%20%60%60%60%0A%20%20Then%20refactor%20your%20queries%20to%20use%20%60to_tsvector%60/%60to_tsquery%60%20instead%20of%20ILIKE%20for%20these%20fields.%0A%0A-%20Rewrite%20count%20queries%20to%20select%20only%20the%20necessary%20fields%20%28ideally%20just%20%60id%60%29%20and%20avoid%20unnecessary%20subqueries%20and%20field%20selection%2C%20which%20currently%20add%20overhead.%0A%0A-%20Reduce%20the%20number%20of%20columns%20selected%20in%20list%20queries%20to%20only%20those%20needed%20for%20the%20UI.%0A%0A-%20For%20long-term%20scalability%2C%20consider%20partitioning%20large%20tables%20%28messages%2C%20conversations%29%20by%20time%20%28e.g.%2C%20created_at%29%20to%20limit%20the%20amount%20of%20data%20scanned%20per%20query%20and%20enable%20instant%20cleanup%20%5B%5Bsource%5D%28https%3A//github.com/langgenius/dify/issues/29776%29%5D.%0A%0A-%20If%20you%20need%20to%20keep%20expensive%20counts%20fast%2C%20consider%20caching%20counts%20in%20Redis%20or%20using%20materialized%20views%2C%20as%20repeated%20COUNT%28%2A%29%20on%20large%20tables%20is%20inherently%20slow%20%5B%5Bsource%5D%28https%3A//github.com/langgenius/dify/issues/27764%23issuecomment-3479154550%29%5D.%0A%0AThere%20are%20no%20migrations%20or%20PRs%20in%20the%20community%20edition%20that%20add%20full-text%20search%20indexes%20or%20advanced%20optimizations%20for%20these%20tables%20yet.%20These%20changes%20require%20schema%20migrations%20and%20some%20ORM%20query%20refactoring%2C%20but%20they%20are%20the%20most%20effective%20way%20to%20address%20the%20performance%20bottleneck%20you%E2%80%99re%20seeing.)&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/langgenius/dify/issues/31513)
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Knowledge Pipeline
Proposal
WIP
cloud
dependencies
feat:webapp
good first issue
outdated
performance
pull-request
Mirrored from GitHub Pull Request
 python:uv
refactor
size:L
stale
status: accepting prs
status: blocked
status: in discussion
stt & tts
vulnerablity
web
wont-fix
🌊 feat:workflow
🌚 invalid
🐞 bug
👻 feat:rag
💪 enhancement
📚 documentation
🔨 feat:tools
🙋‍♂️ question
🙏 help wanted
🤔 cant-reproduce
🤖 feat:agent
No Label 🐞 bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
yindo
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: langgenius/dify#21891
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?