HumanSignal / HumanSignal/label-studio

Project queue_total count creates an expensive duplicate-producing join

Open
#9,861 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**

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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.