HumanSignal / HumanSignal/label-studio

N+1 query on GET /api/users list endpoint

Open
#9,761 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
28.3k
Forks
3.7k
Avg merge
14h
Merged PRs (30d)
15

Description

**Describe the bug**

The `GET /api/users` list endpoint issues N+1 database queries when serializing users. For an organization with N users, the endpoint executes approximately `1 + 2N` queries instead of a constant number (4).

Three separate N+1 sources in `BaseUserSerializer`:

1. **`instance.active_organization`** (`users/serializers.py:25`) — accessed by `get_active_organization_meta()`. This is a ForeignKey on User (`users/models.py:145-146`) that is not eagerly loaded, so Django issues one `SELECT` per user.

2. **`organization.created_by`** (`users/serializers.py:32`) — a second-hop FK access in the same method. `Organization.created_by` is a OneToOneField (`organizations/models.py:88-94`), causing another `SELECT` per user.

3. **`instance.om_through.all()`** (`users/serializers.py:49`) — accessed by `_is_deleted()` via `get_initials()`. This is a reverse FK from `OrganizationMember` to `User` (`organizations/models.py:21-22`). Without `prefetch_related`, each call hits the database.

The current queryset at `users/api.py:181-182`:

```python
def get_queryset(self):
return User.objects.filter(organizations=self.request.user.active_organization)
```

This is the same class of issue already fixed on the organization members endpoint at `organizations/api.py:202-207`, which uses `prefetch_related('user__om_through')`. The `/api/users` endpoint was missed during that fix.

**To Reproduce**

The following script can be run via `label-studio shell` or `python manage.py shell` to demonstrate the issue:

```python
import time
from django.db import connection, reset_queries
from django.test.utils import override_settings
from organizations.models import Organization, OrganizationMember
from users.models import User
from users.serializers import BaseUserSerializer

NUM_TEST_USERS = 50

# --- Setup: create an org with test users ---
owner = User.objects.first()
if owner is None:
raise RuntimeError('Need at least one existing user in the database')

org = owner.active_organization
if org is None:
org = Organization.create_organization(created_by=owner, title='N+1 Test Org')
owner.active_organization = org
owner.save(update_fields=['active_organization'])

created_users = []
for i in range(NUM_TEST_USERS):
u = User.objects.create(
email=f'n1test{i}@example.com',
username=f'n1test{i}',
first_name='Test',
last_name=f'User{i}',
)
u.active_organization = org
u.save(update_fields=['active_organization'])
OrganizationMember.objects.create(user=u, organization=org)
created_users.append(u)

print(f'Created {len(created_users)} test users in org "{org.title}" (id={org.pk})')

# --- Helper ---
def measure(label, qs):
with override_settings(DEBUG=True):
reset_queries()
connection.queries_log.clear()
t0 = time.perf_counter()
data = BaseUserSerializer(
qs, many=True, context={'user': owner}
).data
elapsed = time.perf_counter() - t0
n_queries = len(connection.queries)
print(f'[{label}] users={len(data)} queries={n_queries} time={elapsed:.3f}s')

try:
# --- Before: no prefetch ---
qs_before = User.objects.filter(organizations=org)
measure('BEFORE (no prefetch)', qs_before)

# --- After: with select_related + prefetch_related ---
qs_after = (
User.objects.filter(organizations=org)
.select_related('active_organization', 'active_organization__created_by')
.prefetch_related('om_through')
)
measure('AFTER (with prefetch)', qs_after)
finally:
# --- Cleanup ---
User.objects.filter(email__startswith='n1test').delete()
print('Cleanup complete.')
```

Expected output (approximate):

```
Created 50 test users in org "..." (id=1)
[BEFORE (no prefetch)] users=51 queries=103 time=0.150s
[AFTER (with prefetch)] users=51 queries=2 time=0.020s
Cleanup complete.
```

**Expected behavior**

The `/api/users` list endpoint should execute a constant number of queries (≤4, constant in N: one for users with JOINs for `active_organization` and `created_by`, plus one prefetch for `om_through`), regardless of how many users are in the organization.

**Proposed fix**

One-line change in `users/api.py:181-182`:

```python
def get_queryset(self):
return (
User.objects.filter(organizations=self.request.user.active_organization)
.select_related('active_organization', 'active_organization__created_by')
.prefetch_related('om_through')
)
```

This follows the same pattern as PR #7461, which fixed the same N+1 on the organization members endpoint (`organizations/api.py:202-207`).

**Impact on other `UserAPI` methods**

All methods on `UserAPI` go through `get_queryset()`. The added `select_related`/`prefetch_related` are safe for all of them:

| Method | Effect | Notes |
|---|---|---|
| `list` (`api.py:211`) | **Primary beneficiary.** Eliminates N+1 across all users. | |
| `retrieve` (`api.py:221`) | Minor benefit — saves 2-3 queries on a single user. | |
| `update` (`api.py:208`) | Neutral. Response serialization uses the same serializer, so the JOINs help. The queryset is used for `.get()` lookup. | |
| `partial_update` (`api.py:224`) | Neutral. Same as update. The extra `User.objects.get()` at line 235 bypasses the queryset. | |
| `destroy` (`api.py:244`) | No effect. Returns 204 with no serialized body. The extra JOINs are harmless on a single-row DELETE lookup. | |
| `create` (`api.py:214`) | No effect. `perform_create` (`api.py:217`) writes via `serializer.save()`, not via the queryset. | |

No filters, pagination classes, or filter backends are configured on `UserAPI`, so the queryset change has no interaction with middleware.

**Environment**

- Label Studio version: `1.24.0.dev0` (develop branch)
- Database: affects both SQLite and PostgreSQL (DB-agnostic N+1)

**Additional context**

This is the same pattern fixed by PR #7461 for the organization members list endpoint. That fix added `prefetch_related('user__om_through')` to `OrganizationMemberListAPI.get_queryset()` (see `organizations/api.py:202-207`). The `/api/users` endpoint was not included in that fix.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.