[PR #21157] [CLOSED] feat: get all group query optimization #64809

New Issue

GiteaMirror · 2026-05-06T10:30:12-05:00

GiteaMirror commented

2026-05-06 10:30:12 -05:00

📋 Pull Request Information

Original PR: https://github.com/open-webui/open-webui/pull/21157
Author: @ylwy0178-ctrl
Created: 2/4/2026
Status: ❌ Closed

Base: dev ← Head: bugfix/get_all_group_query_optimization

📝 Commits (1)

cd6cdfc Update groups.py

📊 Changes

1 file changed (+30 additions, -17 deletions)

View changed files

📝 backend/open_webui/models/groups.py (+30 -17)

📄 Description

Optimize database queries: Replace N+1 queries with single aggregated queries in get_groups() and search_groups()

Convert get_groups() to use LEFT OUTER JOIN with GroupMember and COUNT aggregation instead of making individual get_group_member_count_by_id() calls per group
Convert search_groups() to use same optimization with aggregated member counts while maintaining pagination (offset/limit) and all filtering logic
Use func.count(GroupMember.id) with group_by(Group.id) to collapse joined rows and retrieve member counts in single query execution
Eliminates N database round-trips (where N = group count) and reduces to 1 query

Performance improvement: O(N+1) → O(1) database queries for group retrieval operations

Pull Request Checklist

Note to first-time contributors: Please open a discussion post in Discussions to discuss your idea/fix with the community before creating a pull request, and describe your changes before submitting a pull request.

This is to ensure large feature PRs are discussed with the community first, before starting work on it. If the community does not want this feature or it is not relevant for Open WebUI as a project, it can be identified in the discussion before working on the feature and submitting the PR.

Before submitting, make sure you've checked the following:

Target branch: Verify that the pull request targets the dev branch. Not targeting the dev branch will lead to immediate closure of the PR.
Description: Provide a concise description of the changes made in this pull request down below.
Changelog: Ensure a changelog entry following the format of Keep a Changelog is added at the bottom of the PR description.
Documentation: If necessary, update relevant documentation Open WebUI Docs like environment variables, the tutorials, or other documentation sources.
Dependencies: Are there any new dependencies? Have you updated the dependency versions in the documentation?
Testing: Perform manual tests to verify the implemented fix/feature works as intended AND does not break any other functionality. Take this as an opportunity to make screenshots of the feature/fix and include it in the PR description.
Agentic AI Code: Confirm this Pull Request is not written by any AI Agent or has at least gone through additional human review AND manual testing. If any AI Agent is the co-author of this PR, it may lead to immediate closure of the PR.
Code review: Have you performed a self-review of your code, addressing any coding standard issues and ensuring adherence to the project's coding standards?
Title Prefix: To clearly categorize this pull request, prefix the pull request title using one of the following:
- BREAKING CHANGE: Significant changes that may affect compatibility
- build: Changes that affect the build system or external dependencies
- ci: Changes to our continuous integration processes or workflows
- chore: Refactor, cleanup, or other non-functional code changes
- docs: Documentation update or addition
- feat: Introduces a new feature or enhancement to the codebase
- fix: Bug fix or error correction
- i18n: Internationalization or localization changes
- perf: Performance improvement
- refactor: Code restructuring for better maintainability, readability, or scalability
- style: Changes that do not affect the meaning of the code (white space, formatting, missing semi-colons, etc.)
- test: Adding missing tests or correcting existing tests
- WIP: Work in progress, a temporary label for incomplete or ongoing work

Changelog Entry

Description

Optimized group retrieval to eliminate N+1 queries. Replaced per-group member-count lookups with single aggregated queries using LEFT OUTER JOIN + COUNT and GROUP BY. Preserves existing filters (query, share, member_id) and pagination behavior.

Changed

get_groups(): now returns member_count from a single aggregated query (outer join + func.count + group_by) instead of calling get_group_member_count_by_id() for each group.
search_groups(): same optimization applied; preserves skip/limit and filtering logic.
Updated mapping to produce GroupResponse including member_count from the aggregated query results.

Deprecated

[List any deprecated functionality or features that have been removed]

Removed

N+1 pattern of per-group member-count database calls.

Fixed

Eliminated N+1 query bug causing one DB query per group when listing/searching groups.

Contributor License Agreement

By submitting this pull request, I confirm that I have read and fully agree to the Contributor License Agreement (CLA), and I am providing my contributions under its terms.

Note

Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/open-webui/open-webui/pull/21157 **Author:** [@ylwy0178-ctrl](https://github.com/ylwy0178-ctrl) **Created:** 2/4/2026 **Status:** ❌ Closed **Base:** `dev` ← **Head:** `bugfix/get_all_group_query_optimization` --- ### 📝 Commits (1) - [`cd6cdfc`](https://github.com/open-webui/open-webui/commit/cd6cdfc01dc49bdb8891928fc874def34c2c7877) Update groups.py ### 📊 Changes **1 file changed** (+30 additions, -17 deletions) <details> <summary>View changed files</summary> 📝 `backend/open_webui/models/groups.py` (+30 -17) </details> ### 📄 Description Optimize database queries: Replace N+1 queries with single aggregated queries in get_groups() and search_groups() - Convert get_groups() to use LEFT OUTER JOIN with GroupMember and COUNT aggregation instead of making individual get_group_member_count_by_id() calls per group - Convert search_groups() to use same optimization with aggregated member counts while maintaining pagination (offset/limit) and all filtering logic - Use func.count(GroupMember.id) with group_by(Group.id) to collapse joined rows and retrieve member counts in single query execution - Eliminates N database round-trips (where N = group count) and reduces to 1 query Performance improvement: O(N+1) → O(1) database queries for group retrieval operations # Pull Request Checklist ### Note to first-time contributors: Please open a discussion post in [Discussions](https://github.com/open-webui/open-webui/discussions) to discuss your idea/fix with the community before creating a pull request, and describe your changes before submitting a pull request. This is to ensure large feature PRs are discussed with the community first, before starting work on it. If the community does not want this feature or it is not relevant for Open WebUI as a project, it can be identified in the discussion before working on the feature and submitting the PR. **Before submitting, make sure you've checked the following:** - [ ] **Target branch:** Verify that the pull request targets the `dev` branch. **Not targeting the `dev` branch will lead to immediate closure of the PR.** - [ ] **Description:** Provide a concise description of the changes made in this pull request down below. - [ ] **Changelog:** Ensure a changelog entry following the format of [Keep a Changelog](https://keepachangelog.com/) is added at the bottom of the PR description. - [ ] **Documentation:** If necessary, update relevant documentation [Open WebUI Docs](https://github.com/open-webui/docs) like environment variables, the tutorials, or other documentation sources. - [ ] **Dependencies:** Are there any new dependencies? Have you updated the dependency versions in the documentation? - [ ] **Testing:** Perform manual tests to **verify the implemented fix/feature works as intended AND does not break any other functionality**. Take this as an opportunity to **make screenshots of the feature/fix and include it in the PR description**. - [ ] **Agentic AI Code:** Confirm this Pull Request is **not written by any AI Agent** or has at least **gone through additional human review AND manual testing**. If any AI Agent is the co-author of this PR, it may lead to immediate closure of the PR. - [ ] **Code review:** Have you performed a self-review of your code, addressing any coding standard issues and ensuring adherence to the project's coding standards? - [ ] **Title Prefix:** To clearly categorize this pull request, prefix the pull request title using one of the following: - **BREAKING CHANGE**: Significant changes that may affect compatibility - **build**: Changes that affect the build system or external dependencies - **ci**: Changes to our continuous integration processes or workflows - **chore**: Refactor, cleanup, or other non-functional code changes - **docs**: Documentation update or addition - **feat**: Introduces a new feature or enhancement to the codebase - **fix**: Bug fix or error correction - **i18n**: Internationalization or localization changes - **perf**: Performance improvement - **refactor**: Code restructuring for better maintainability, readability, or scalability - **style**: Changes that do not affect the meaning of the code (white space, formatting, missing semi-colons, etc.) - **test**: Adding missing tests or correcting existing tests - **WIP**: Work in progress, a temporary label for incomplete or ongoing work # Changelog Entry ### Description Optimized group retrieval to eliminate N+1 queries. Replaced per-group member-count lookups with single aggregated queries using LEFT OUTER JOIN + COUNT and GROUP BY. Preserves existing filters (query, share, member_id) and pagination behavior. ### Changed - get_groups(): now returns member_count from a single aggregated query (outer join + func.count + group_by) instead of calling get_group_member_count_by_id() for each group. search_groups(): same optimization applied; preserves skip/limit and filtering logic. Updated mapping to produce GroupResponse including member_count from the aggregated query results. ### Deprecated - [List any deprecated functionality or features that have been removed] ### Removed - N+1 pattern of per-group member-count database calls. ### Fixed - Eliminated N+1 query bug causing one DB query per group when listing/searching groups. --- ### Contributor License Agreement By submitting this pull request, I confirm that I have read and fully agree to the [Contributor License Agreement (CLA)](https://github.com/open-webui/open-webui/blob/main/CONTRIBUTOR_LICENSE_AGREEMENT), and I am providing my contributions under its terms. > [!NOTE] > Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

GiteaMirror added the pull-request label 2026-05-06 10:30:12 -05:00

GiteaMirror closed this issue

2026-05-06 10:30:14 -05:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/open-webui#64809