HumanSignal / HumanSignal/label-studio
Project queue_total count creates an expensive duplicate-producing join
- Dominant language
- TypeScript
- Stars
- 28.3k
- Forks
- 3.7k
- Avg merge
- 14h
- Merged PRs (30d)
- 15
Description
**Describe the bug**
Serializing a project computes `queue_total` with a negated annotation relation, a positive annotation relation, and `distinct().count()`. Django compiles this to a duplicate-producing outer join plus a correlated anti-subquery and `DISTINCT`, even though the intended membership is simply: tasks that are unlabeled, or tasks annotated by the current user. On projects with substantial annotation history this query adds avoidable database load to project-list/API requests.
**To Reproduce**
1. Create a project with a mix of labeled and unlabeled tasks and annotations from multiple users.
2. Request the project serializer/API as a user.
3. Inspect the SQL for `queue_total`: it contains an annotation join, `DISTINCT`, and the negated relation subquery.
4. Compare it with `Q(is_labeled=False) | Exists(Annotation.objects.filter(task_id=OuterRef("pk"), completed_by_id=user_id))`.
**Expected behavior**
The count must preserve the exact task membership while using a correlated `EXISTS` predicate, avoiding the outer annotation join and `DISTINCT`. It should remain one count query and work on both SQLite and PostgreSQL.
**Performance evidence**
Read-only PostgreSQL A/B tests on three representative project/user pairs preserved exact counts and reduced server execution time from 268.2 ms to 16.3 ms, 31.5 ms to 9.5 ms, and 35.9 ms to 7.5 ms (3.3x-16.5x faster).
**Environment**
- Database: PostgreSQL
- Code: current `develop`
**Acceptance criteria**
- Exact task-membership parity with the current predicate
- One count query
- Correlated `EXISTS`; no outer annotation join or `DISTINCT`
- Regression coverage for unlabeled, current-user, other-user, and mixed-annotation tasks
- SQLite and PostgreSQL-compatible ORM query
Contributor guide
Research direction
Start at the project serializer/API path that computes queue_total and inspect the generated ORM query for its joins, DISTINCT, and count behavior. Add regression coverage for unlabeled, current-user, other-user, and mixed-annotation tasks, then verify exact counts, one count query, and SQLite/PostgreSQL compatibility.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- django, postgresql, python, sqlite
- Domain
- api, backend, database, performance, testing
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100