[GH-ISSUE #24868] issue: Performance issue: /api/config triggers heavy SELECT count(*) on every call, consuming high CPU #123726

New Issue

GiteaMirror · 2026-05-21T03:12:01-05:00

GiteaMirror commented

2026-05-21 03:12:01 -05:00

Originally created by @MiXaiLL76 on GitHub (May 18, 2026).
Original GitHub issue: https://github.com/open-webui/open-webui/issues/24868

Originally assigned to: @Classic298 on GitHub.

Check Existing Issues

I have searched for any existing and/or related issues.
I have searched for any existing and/or related discussions.
I have also searched in the CLOSED issues AND CLOSED discussions and found no related items (your issue might already be addressed on the development branch!).
I am using the latest version of Open WebUI.

Installation Method

Docker

Open WebUI Version

v0.9.5

Operating System

Debian

Confirmation

I have read and followed all instructions in README.md.
I am using the latest version of both Open WebUI and Ollama.
I have included the browser console logs.
I have included the Docker container logs.
I have provided every relevant configuration, setting, and environment variable used in my setup.
I have clearly listed every relevant configuration, custom setting, environment variable, and command-line option that influences my setup (such as Docker Compose overrides, .env values, browser settings, authentication configurations, etc).
I have documented step-by-step reproduction instructions that are precise, sequential, and leave nothing to interpretation. My steps:
Start with the initial platform/version/OS and dependencies used,
Specify exact install/launch/configure commands,
List URLs visited, user input (incl. example values/emails/passwords if needed),
Describe all options and toggles enabled or changed,
Include any files or environmental changes,
Identify the expected and actual result at each stage,
Ensure any reasonably skilled user can follow and hit the same issue.

Expected Behavior

Every time a user authenticates or the frontend requests the /api/config endpoint, it triggers Users.get_num_users(). This method executes a full table count (SELECT count(*) FROM "user"), which causes massive DB load and slow response times on instances with a large number of users.

According to our query statistics, this single query accounts for 70% of total CPU usage:

    Calls: 335,018
    Total Time: ~578,806 seconds
    Mean Time per query: 1.72 seconds
    Max Time: 88.4 seconds
    CPU impact: 70.09%

Code Reference
The endpoint calls get_num_users() here:
https://github.com/open-webui/open-webui/blob/v0.9.5/backend/open_webui/main.py#L2344
The query implementation:
https://github.com/open-webui/open-webui/blob/v0.9.5/backend/open_webui/models/users.py

Python

async def get_num_users(self, db: Optional[AsyncSession] = None) -> Optional[int]:
    async with get_async_db_context(db) as db:
        result = await db.execute(select(func.count()).select_from(User))
        return result.scalar()

Generated SQL:

SELECT count(*) AS count_1 FROM "user";

I suggest changing the function to

async def get_num_users(self, db: Optional[AsyncSession] = None) -> Optional[int]:
    async with get_async_db_context(db) as db:
        # Передаем User.id внутрь func.count()
        result = await db.execute(select(func.count(User.id)))
        return result.scalar()

Actual Behavior

According to our query statistics, this single query accounts for 70% of total CPU usage:

    Calls: 335,018
    Total Time: ~578,806 seconds
    Mean Time per query: 1.72 seconds
    Max Time: 88.4 seconds
    CPU impact: 70.09%

Steps to Reproduce

Login open-web-ui

Logs & Screenshots

Originally created by @MiXaiLL76 on GitHub (May 18, 2026). Original GitHub issue: https://github.com/open-webui/open-webui/issues/24868 Originally assigned to: @Classic298 on GitHub. ### Check Existing Issues - [x] I have searched for any existing and/or related issues. - [x] I have searched for any existing and/or related discussions. - [x] I have also searched in the CLOSED issues AND CLOSED discussions and found no related items (your issue might already be addressed on the development branch!). - [x] I am using the latest version of Open WebUI. ### Installation Method Docker ### Open WebUI Version v0.9.5 ### Operating System Debian ### Confirmation - [x] I have read and followed all instructions in `README.md`. - [x] I am using the latest version of **both** Open WebUI and Ollama. - [x] I have included the browser console logs. - [x] I have included the Docker container logs. - [x] I have **provided every relevant configuration, setting, and environment variable used in my setup.** - [x] I have clearly **listed every relevant configuration, custom setting, environment variable, and command-line option that influences my setup** (such as Docker Compose overrides, .env values, browser settings, authentication configurations, etc). - [x] I have documented **step-by-step reproduction instructions that are precise, sequential, and leave nothing to interpretation**. My steps: - Start with the initial platform/version/OS and dependencies used, - Specify exact install/launch/configure commands, - List URLs visited, user input (incl. example values/emails/passwords if needed), - Describe all options and toggles enabled or changed, - Include any files or environmental changes, - Identify the expected and actual result at each stage, - Ensure any reasonably skilled user can follow and hit the same issue. ### Expected Behavior Every time a user authenticates or the frontend requests the /api/config endpoint, it triggers Users.get_num_users(). This method executes a full table count (SELECT count(*) FROM "user"), which causes massive DB load and slow response times on instances with a large number of users. According to our query statistics, this single query accounts for 70% of total CPU usage: ``` Calls: 335,018 Total Time: ~578,806 seconds Mean Time per query: 1.72 seconds Max Time: 88.4 seconds CPU impact: 70.09% ``` Code Reference The endpoint calls get_num_users() here: https://github.com/open-webui/open-webui/blob/v0.9.5/backend/open_webui/main.py#L2344 The query implementation: https://github.com/open-webui/open-webui/blob/v0.9.5/backend/open_webui/models/users.py Python ``` async def get_num_users(self, db: Optional[AsyncSession] = None) -> Optional[int]: async with get_async_db_context(db) as db: result = await db.execute(select(func.count()).select_from(User)) return result.scalar() ``` Generated SQL: ``` SELECT count(*) AS count_1 FROM "user"; ``` I suggest changing the function to ``` async def get_num_users(self, db: Optional[AsyncSession] = None) -> Optional[int]: async with get_async_db_context(db) as db: # Передаем User.id внутрь func.count() result = await db.execute(select(func.count(User.id))) return result.scalar() ``` ### Actual Behavior According to our query statistics, this single query accounts for 70% of total CPU usage: ``` Calls: 335,018 Total Time: ~578,806 seconds Mean Time per query: 1.72 seconds Max Time: 88.4 seconds CPU impact: 70.09% ``` ### Steps to Reproduce Login open-web-ui ### Logs & Screenshots <img width="950" height="939" alt="Image" src="https://github.com/user-attachments/assets/804518d0-3550-4e56-b051-6b6d7513dfcf" />

GiteaMirror added the bug label 2026-05-21 03:12:01 -05:00

GiteaMirror closed this issue

2026-05-21 03:12:04 -05:00

GiteaMirror commented

2026-05-21 03:12:06 -05:00

@owui-terminator[bot] commented on GitHub (May 18, 2026):

🔍 Related Issues Found

I found some existing issues that might be related. Please check if any of these are duplicates or contain helpful solutions:

🟣 #23939 issue: Loading and login issue after upgrading to 0.9.0 and 0.9.1
This report mentions /api/config being very slow during login and causing the OIDC login flow to hang. It is likely related because the new issue also centers on /api/config performance impacting authentication, though the specific bottleneck differs.
by Joly0 · bug
🟣 #13724 issue: Admin panel users slow to load for larger user bases
This issue is about user-related admin loading becoming slow as the user base grows, which points to database work scaling poorly with large numbers of users. It is related to the same general performance area and user-count-heavy code path.
by robbiekouwenberg · bug
🟣 #14945 issue: Performance degradation as active users increase due to "Active Users" count & user-list emitters
This is a broader performance regression tied to user activity and user-list emissions, showing that user-related backend events can create severe load. It is not the same query, but it is in the same subsystem and illustrates a similar scalability problem.
by taylorwilsdon · bug
🟣 #19509 issue: User overview page calls /api/v1/users multiple times
This issue reports repeated /api/v1/users requests from the admin UI, which is another case of excessive API/database churn around user data. It is related as a similar frontend/backend inefficiency affecting user-related endpoints.
by luke-wren · bug
🟣 #23793 feat: cache get_user_by_id — 25% of all DB queries with zero caching
This feature request analyzes heavy user lookup queries on authenticated requests and identifies the same general pattern of expensive user DB access on hot paths. While it focuses on get_user_by_id() rather than get_num_users(), it is directly relevant to authentication-path query load.
by ashm-dev

💡 If your issue is a duplicate, please close it and add any additional details to the existing issue instead.

This comment was generated automatically. React with 👍 if helpful, 👎 if not.

@owui-terminator[bot] commented on GitHub (May 18, 2026):  🔍 **Related Issues Found** I found some existing issues that might be related. Please check if any of these are duplicates or contain helpful solutions: 1. 🟣 [#23939](https://github.com/open-webui/open-webui/issues/23939) **issue: Loading and login issue after upgrading to 0.9.0 and 0.9.1** *This report mentions `/api/config` being very slow during login and causing the OIDC login flow to hang. It is likely related because the new issue also centers on `/api/config` performance impacting authentication, though the specific bottleneck differs.* *by Joly0 · `bug`* 2. 🟣 [#13724](https://github.com/open-webui/open-webui/issues/13724) **issue: Admin panel users slow to load for larger user bases** *This issue is about user-related admin loading becoming slow as the user base grows, which points to database work scaling poorly with large numbers of users. It is related to the same general performance area and user-count-heavy code path.* *by robbiekouwenberg · `bug`* 3. 🟣 [#14945](https://github.com/open-webui/open-webui/issues/14945) **issue: Performance degradation as active users increase due to "Active Users" count & `user-list` emitters** *This is a broader performance regression tied to user activity and user-list emissions, showing that user-related backend events can create severe load. It is not the same query, but it is in the same subsystem and illustrates a similar scalability problem.* *by taylorwilsdon · `bug`* 4. 🟣 [#19509](https://github.com/open-webui/open-webui/issues/19509) **issue: User overview page calls /api/v1/users multiple times** *This issue reports repeated `/api/v1/users` requests from the admin UI, which is another case of excessive API/database churn around user data. It is related as a similar frontend/backend inefficiency affecting user-related endpoints.* *by luke-wren · `bug`* 5. 🟣 [#23793](https://github.com/open-webui/open-webui/issues/23793) **feat: cache get_user_by_id — 25% of all DB queries with zero caching** *This feature request analyzes heavy user lookup queries on authenticated requests and identifies the same general pattern of expensive user DB access on hot paths. While it focuses on `get_user_by_id()` rather than `get_num_users()`, it is directly relevant to authentication-path query load.* *by ashm-dev* --- 💡 If your issue is a duplicate, please close it and add any additional details to the existing issue instead. *This comment was generated automatically.* React with 👍 if helpful, 👎 if not.

GiteaMirror commented

2026-05-21 03:12:06 -05:00

@Classic298 commented on GitHub (May 19, 2026):

investigating

@Classic298 commented on GitHub (May 19, 2026): investigating

GiteaMirror commented

2026-05-21 03:12:08 -05:00

@MiXaiLL76 commented on GitHub (May 19, 2026):

investigating

I think I figured out what exactly is complicating the query.

Since selecting the entire COUNT(*) table, rather than by ID, is the problem.

My users have their student information stored in info (json), which is heavy for the database.

If I apply the change above

async def get_num_users(self, db: Optional[AsyncSession] = None) -> Optional[int]:
  async with get_async_db_context(db) as db:
    # Pass User.id into func.count()
    result = await db.execute(select(func.count(User.id)))
    return result.scalar()

This will probably fix the problem, but I haven't tested it. I've just made get_num_users() -> return 1000 the default for now.

@MiXaiLL76 commented on GitHub (May 19, 2026): > investigating I think I figured out what exactly is complicating the query. Since selecting the entire COUNT(*) table, rather than by ID, is the problem. My users have their student information stored in info (json), which is heavy for the database. If I apply the change above ``` async def get_num_users(self, db: Optional[AsyncSession] = None) -> Optional[int]: async with get_async_db_context(db) as db: # Pass User.id into func.count() result = await db.execute(select(func.count(User.id))) return result.scalar() ``` This will probably fix the problem, but I haven't tested it. I've just made get_num_users() -> return 1000 the default for now.

GiteaMirror commented

2026-05-21 03:12:08 -05:00

@Classic298 commented on GitHub (May 19, 2026):

you mean stored in the biography in the profile?

@Classic298 commented on GitHub (May 19, 2026): you mean stored in the biography in the profile?

GiteaMirror commented

2026-05-21 03:12:10 -05:00

@Classic298 commented on GitHub (May 19, 2026):

@MiXaiLL76 1.7 seconds is too long for a single count operation.

Either your database is slow, or you are running the database over network, or you are running sqlite over slow disk or network attached disk, or you have very frequent user updates. So very often you have UPDATE "user" SET last_active_at=.

The first three are architectural faults of your deployment.
The fourth one is fixable through: https://docs.openwebui.com/reference/env-configuration#database_user_active_status_update_interval and setting it to for example 300.

I will make a PR to replace the user count in the unauthenticated branch with has_users() function which is a much cheaper operation and defer the actual user count from the authenticated branch, but at the end of the day, this will not fix your issue. Your issue is either setup related or caused by the missing env var here. A simple count operation should NEVER EVER take 1.7s on ANY database

@Classic298 commented on GitHub (May 19, 2026): @MiXaiLL76 1.7 seconds is too long for a single count operation. Either your database is slow, or you are running the database over network, or you are running sqlite over slow disk or network attached disk, or you have very frequent user updates. So very often you have `UPDATE "user" SET last_active_at=`. The first three are architectural faults of your deployment. The fourth one is fixable through: https://docs.openwebui.com/reference/env-configuration#database_user_active_status_update_interval and setting it to for example 300. I will make a PR to replace the user count in the unauthenticated branch with has_users() function which is a much cheaper operation and defer the actual user count from the authenticated branch, but at the end of the day, this will not fix your issue. Your issue is either setup related or caused by the missing env var here. A simple count operation should NEVER EVER take 1.7s on ANY database

GiteaMirror commented

2026-05-21 03:12:10 -05:00

@Classic298 commented on GitHub (May 19, 2026):

let us know what type of db you use and what your setup looks like because you didn't share that yet. I'd bet it's a deployment issue

@Classic298 commented on GitHub (May 19, 2026): let us know what type of db you use and what your setup looks like because you didn't share that yet. I'd bet it's a deployment issue

GiteaMirror commented

2026-05-21 03:12:11 -05:00

@MiXaiLL76 commented on GitHub (May 19, 2026):

you mean stored in the biography in the profile?

yep, user.info = json{"xxx":"yyy", "zzz" : {1:2} }....

postgresql + REDIS

# https://docs.openwebui.com/reference/env-configuration#database_url
        - name : DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: owu-secret
              key: DATABASE_URL
        - name: DATABASE_TYPE
          value: "postgresql"
        # https://docs.openwebui.com/reference/env-configuration#database_enable_session_sharing
        # Recommended True for PostgreSQL in multi-pod deployments
        - name: DATABASE_ENABLE_SESSION_SHARING
          value: "true"
        # Connection pool: size=20 steady + overflow=30 burst = 50 max per process.
        # With 2 replicas × 1 async worker = 100 max total PostgreSQL connections.
        - name: DATABASE_POOL_SIZE
          value: "20"
        - name: DATABASE_POOL_MAX_OVERFLOW
          value: "30"
        - name: DATABASE_POOL_TIMEOUT
          value: "30"
        - name: DATABASE_POOL_RECYCLE
          value: "1800"
        # Cache DB query results in memory (uses Valkey if REDIS_URL is set).
        # Reduces repeated SELECT load for frequently read data (models, users, configs).
        - name: ENABLE_QUERIES_CACHE
          value: "True"
        # Batch user last-active updates — write to DB every 60s instead of per-request.
        # With 5000 users this eliminates the biggest source of constant DB writes.
        - name: DATABASE_USER_ACTIVE_STATUS_UPDATE_INTERVAL
          value: "180"
        # Only admins see /api/usage (active user count) — prevents DB hit for every user.
        - name: ENABLE_PUBLIC_ACTIVE_USERS_COUNT
          value: "False"
        # https://docs.openwebui.com/reference/env-configuration#enable_db_migrations
        # Disabled to prevent migration race conditions in multi-pod deployments.
        # Migrations are handled by the init container below.
        - name: ENABLE_DB_MIGRATIONS
          value: "false"

I simply applied a "patch" to reduce such queries.

added

CREATE UNIQUE INDEX CONCURRENTLY idx_user_email ON "user" (email);

And everything started working 100 times faster with my users.

@MiXaiLL76 commented on GitHub (May 19, 2026): > you mean stored in the biography in the profile? yep, user.info = json{"xxx":"yyy", "zzz" : {1:2} }.... postgresql + REDIS ``` # https://docs.openwebui.com/reference/env-configuration#database_url - name : DATABASE_URL valueFrom: secretKeyRef: name: owu-secret key: DATABASE_URL - name: DATABASE_TYPE value: "postgresql" # https://docs.openwebui.com/reference/env-configuration#database_enable_session_sharing # Recommended True for PostgreSQL in multi-pod deployments - name: DATABASE_ENABLE_SESSION_SHARING value: "true" # Connection pool: size=20 steady + overflow=30 burst = 50 max per process. # With 2 replicas × 1 async worker = 100 max total PostgreSQL connections. - name: DATABASE_POOL_SIZE value: "20" - name: DATABASE_POOL_MAX_OVERFLOW value: "30" - name: DATABASE_POOL_TIMEOUT value: "30" - name: DATABASE_POOL_RECYCLE value: "1800" # Cache DB query results in memory (uses Valkey if REDIS_URL is set). # Reduces repeated SELECT load for frequently read data (models, users, configs). - name: ENABLE_QUERIES_CACHE value: "True" # Batch user last-active updates — write to DB every 60s instead of per-request. # With 5000 users this eliminates the biggest source of constant DB writes. - name: DATABASE_USER_ACTIVE_STATUS_UPDATE_INTERVAL value: "180" # Only admins see /api/usage (active user count) — prevents DB hit for every user. - name: ENABLE_PUBLIC_ACTIVE_USERS_COUNT value: "False" # https://docs.openwebui.com/reference/env-configuration#enable_db_migrations # Disabled to prevent migration race conditions in multi-pod deployments. # Migrations are handled by the init container below. - name: ENABLE_DB_MIGRATIONS value: "false" ``` <img width="493" height="161" alt="Image" src="https://github.com/user-attachments/assets/30b6109d-6d95-4797-8a7e-d3e15b2932d8" /> I simply applied a "patch" to reduce such queries. + added ``` CREATE UNIQUE INDEX CONCURRENTLY idx_user_email ON "user" (email); ``` And everything started working 100 times faster with my users.

GiteaMirror commented

2026-05-21 03:12:13 -05:00

@Classic298 commented on GitHub (May 19, 2026):

PostgreSQL should not take 1.7 seconds to run a count query. Please answer my question re: how is the connection to your PostgreSQL. is it on the same server? is it on a different network machine? What's the latency to that database?

also did you set the env var i recommended?

Finally you can test this PR if you want: https://github.com/open-webui/open-webui/pull/24904

@Classic298 commented on GitHub (May 19, 2026): PostgreSQL should not take 1.7 seconds to run a count query. Please answer my question re: how is the connection to your PostgreSQL. is it on the same server? is it on a different network machine? What's the latency to that database? also did you set the env var i recommended? Finally you can test this PR if you want: https://github.com/open-webui/open-webui/pull/24904

GiteaMirror commented

2026-05-21 03:12:14 -05:00

@Classic298 commented on GitHub (May 19, 2026):

you mean stored in the biography in the profile?
yep, user.info = json{"xxx":"yyy", "zzz" : {1:2} }....

Btw i checked and this isn't even being loaded so this is a red herring

the config endpoint runs a simple count query, so the user biography isn't even loaded, or any of the user data. It just counts how many users there are. One of the most simple and fastest queries a database can do.

@Classic298 commented on GitHub (May 19, 2026): > you mean stored in the biography in the profile? > yep, user.info = json{"xxx":"yyy", "zzz" : {1:2} }.... Btw i checked and this isn't even being loaded so this is a red herring the config endpoint runs a simple count query, so the user biography isn't even loaded, or any of the user data. It just counts how many users there are. One of the most simple and fastest queries a database can do.

GiteaMirror commented

2026-05-21 03:12:15 -05:00

@MiXaiLL76 commented on GitHub (May 19, 2026):

PostgreSQL should not take 1.7 seconds to run a count query. Please answer my question re: how is the connection to your PostgreSQL. is it on the same server? is it on a different network machine? What's the latency to that database?

also did you set the env var i recommended?

same k8s node

@MiXaiLL76 commented on GitHub (May 19, 2026): > PostgreSQL should not take 1.7 seconds to run a count query. Please answer my question re: how is the connection to your PostgreSQL. is it on the same server? is it on a different network machine? What's the latency to that database? > > also did you set the env var i recommended? same k8s node

GiteaMirror commented

2026-05-21 03:12:16 -05:00

@MiXaiLL76 commented on GitHub (May 19, 2026):

Overall, calculating using count(*) is a SQL anti-pattern, as I understand it.

I think it's worth considering using count(user.id) instead, as collecting data by index is a much simpler operation.

@MiXaiLL76 commented on GitHub (May 19, 2026): Overall, calculating using count(*) is a SQL anti-pattern, as I understand it. I think it's worth considering using count(user.id) instead, as collecting data by index is a much simpler operation.

GiteaMirror commented

2026-05-21 03:12:17 -05:00

@Classic298 commented on GitHub (May 19, 2026):

Following up to close the loop on the count(*) vs count(user.id) point, since it'll come up again otherwise.

@MiXaiLL76 count(*) is not an anti-pattern in PostgreSQL. That rule is MySQL/MyISAM folklore and doesn't transfer. In Postgres count(*) is the planner-optimized form; count(col) is, if anything, slower because it adds a per-row NOT-NULL check. The PG wiki and core devs are explicit that count(*) ≥ count(1) ≥ count(col).

Swapping to count(User.id) would not change anything. Postgres has no stored row count — MVCC means every transaction sees a different number, so any exact count must scan all visible tuples. count(*) and count(id) produce the same scan and the same plan family. It won't turn 1.7s into milliseconds. This is also why it was never the fix: note you hard-coded return 1000 and never actually benchmarked count(id).

Your own result actually disproves the theory. CREATE UNIQUE INDEX CONCURRENTLY idx_user_email making it "100× faster" is the tell: a fresh, compact index let the planner switch to an index-only scan for count(*) instead of scanning a bloated heap. That path is available to count(*) too — it has nothing to do with count(id).

Why 1.7s mean / 88s max on your userbase? Probably because you aren't scanning tidy rows. A count over a healthy user table of thousand of rows is single-digit milliseconds AT MOST. Those numbers mean the scan is hitting a pathological table — almost certainly dead-tuple + heap/TOAST bloat with autovacuum falling behind. Your own clues line up: frequent UPDATE "user" SET last_active_at=… on wide rows (large user.info JSON). Every UPDATE writes a new row version; with lagging autovacuum the heap balloons and a stale visibility map blocks the cheap index-only scan, forcing a seq-scan over a huge bloated heap. (count never reads info itself, so the JSON isn't loaded — but its width still inflates row size and bloat.)

Quick diagnostics to confirm on your instance:

SELECT n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables WHERE relname = 'user';

SELECT pg_size_pretty(pg_total_relation_size('"user"')) AS total,
       pg_size_pretty(pg_relation_size('"user"'))       AS heap;

If n_dead_tup is large relative to n_live_tup, or total/heap is huge for 4k users, that's your 1.7s.

Remediation for the deployment (independent of the code):

VACUUM (FULL, ANALYZE) "user"; once (or pg_repack to avoid the exclusive lock), then
much more aggressive autovacuum on this table, e.g. ALTER TABLE "user" SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_delay = 0);
keep DATABASE_USER_ACTIVE_STATUS_UPDATE_INTERVAL high (you have 180, good)
verify storage isn't a slow network-attached PVC.

The actual fix for the CPU regression is #24904: it stops calling the count on the hot/unauthenticated path entirely (has_users() → SELECT EXISTS, the 335k calls go to ~0) and defers the real count to authenticated admin/user only when a seat-limited license needs it. That resolves the 70% CPU regardless of DB health — but the bloat above is still worth fixing on your side, because it will keep biting other queries.

And as I said, try out this env var also, it will help protect your auto vacuum from falling behind on frequent updates: docs.openwebui.com/reference/env-configuration#database_user_active_status_update_interval

@Classic298 commented on GitHub (May 19, 2026): Following up to close the loop on the count(*) vs count(user.id) point, since it'll come up again otherwise. @MiXaiLL76 `count(*)` is not an anti-pattern in PostgreSQL. That rule is MySQL/MyISAM folklore and doesn't transfer. In Postgres `count(*)` is the planner-optimized form; **`count(col)` is, if anything, slower** because it adds a per-row NOT-NULL check. The PG wiki and core devs are explicit that `count(*)` ≥ count(1) ≥ count(col). Swapping to count(User.id) would not change anything. Postgres has no stored row count — MVCC means every transaction sees a different number, so any exact count must scan all visible tuples. `count(*)` and count(id) produce the same scan and the same plan family. It won't turn 1.7s into milliseconds. This is also why it was never the fix: note you hard-coded return 1000 and never actually benchmarked count(id). Your own result actually disproves the theory. CREATE UNIQUE INDEX CONCURRENTLY idx_user_email making it "100× faster" is the tell: a fresh, compact index let the planner switch to an index-only scan for `count(*)` instead of scanning a bloated heap. That path is available to `count(*)` too — it has nothing to do with count(id). Why 1.7s mean / 88s max on your userbase? Probably because you aren't scanning tidy rows. A count over a healthy user table of thousand of rows is single-digit milliseconds AT MOST. Those numbers mean the scan is hitting a pathological table — almost certainly dead-tuple + heap/TOAST bloat with autovacuum falling behind. Your own clues line up: frequent UPDATE "user" SET last_active_at=… on wide rows (large user.info JSON). Every UPDATE writes a new row version; with lagging autovacuum the heap balloons and a stale visibility map blocks the cheap index-only scan, forcing a seq-scan over a huge bloated heap. (count never reads info itself, so the JSON isn't loaded — but its width still inflates row size and bloat.) Quick diagnostics to confirm on your instance: ``` SELECT n_live_tup, n_dead_tup, last_autovacuum, last_vacuum FROM pg_stat_user_tables WHERE relname = 'user'; SELECT pg_size_pretty(pg_total_relation_size('"user"')) AS total, pg_size_pretty(pg_relation_size('"user"')) AS heap; ``` If n_dead_tup is large relative to n_live_tup, or total/heap is huge for 4k users, that's your 1.7s. Remediation for the deployment (independent of the code): VACUUM (FULL, ANALYZE) "user"; once (or pg_repack to avoid the exclusive lock), then much more aggressive autovacuum on this table, e.g. ALTER TABLE "user" SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_delay = 0); keep DATABASE_USER_ACTIVE_STATUS_UPDATE_INTERVAL high (you have 180, good) verify storage isn't a slow network-attached PVC. The actual fix for the CPU regression is [#24904](https://github.com/open-webui/open-webui/pull/24904): it stops calling the count on the hot/unauthenticated path entirely (has_users() → SELECT EXISTS, the 335k calls go to ~0) and defers the real count to authenticated admin/user only when a seat-limited license needs it. That resolves the 70% CPU regardless of DB health — but the bloat above is still worth fixing on your side, because it will keep biting other queries. And as I said, try out this env var also, it will help protect your auto vacuum from falling behind on frequent updates: [docs.openwebui.com/reference/env-configuration#database_user_active_status_update_interval](https://docs.openwebui.com/reference/env-configuration#database_user_active_status_update_interval)

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#123726