Scheduler: the closing and quiet announcers loop once per row, not per owner, because DISTINCT takes in the default ordering #400

New issue

Closed

opened 2026-10-01 10:46:25 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-01 10:46:25 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the announcers in send_due_reminders and printing their owner querysets' SQL.

Checked again on 2026-10-01 against main at 0a5cad587: still present. JobPosting.Meta.ordering is now at jobs/models.py:959; jobs/closing.py, applications/quiet.py, applications/models.py:427 and docker/compose.yml:63 have not moved.

Both announcers start by asking which owners have anything to announce, with .values_list("owner", flat=True).distinct(). The models carry a default ordering, and Django adds ordering columns to a SELECT DISTINCT, so the question returns one row per listing or application rather than one per owner. Each row then runs the whole per-owner pass again. The results stay correct, because the stamps leave nothing fresh after the first pass for an owner, but the scheduler repeats the work every five minutes, and for the quiet announcer the cost grows with the square of a person's applications.

Where: jobs/closing.py:77-88 (announce_closing_postings), over JobPosting.Meta.ordering = ("-noted_at", "-created_at") at jobs/models.py:956; applications/quiet.py:70-81 (announce_quiet_applications), over Application.Meta.ordering = ("-created_at",) at applications/models.py:427.
What happens: the printed SQL is SELECT DISTINCT "jobs_jobposting"."owner_id" AS "owner", "jobs_jobposting"."noted_at", "jobs_jobposting"."created_at" ... ORDER BY "jobs_jobposting"."noted_at" DESC, ... and SELECT DISTINCT "applications_application"."owner_id" AS "owner", "applications_application"."created_at" ... ORDER BY "applications_application"."created_at" DESC. So the closing announcer iterates once per listing with a closing date today or later, each iteration loading the user (_owner_of) and running closing_for; the quiet announcer iterates once per application of every active account, whatever its status, each iteration loading the user and running the annotated quiet() query over all that person's applications. A person with 300 applications costs 600 queries, 300 of them scanning 300 applications with activity subqueries, on every pass (docker/compose.yml:63 runs a pass every 300 seconds).
Why it matters: the comment at jobs/closing.py:77-78 promises "three questions rather than a thousand", and the code asks one per row instead. On SQLite the passes hold the database for the whole loop, in step with the web workers, for work that finds nothing new.
Fix: clear the ordering in both querysets: .values_list("owner", flat=True).order_by().distinct().

Done when

tests/test_deadlines.py: with two listings closing within the notice for one owner, announce_closing_postings runs a fixed number of queries (django_assert_num_queries) that does not grow with a third listing.
tests/test_quiet.py: the same for announce_quiet_applications with several applications for one owner.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the announcers in `send_due_reminders` and printing their owner querysets' SQL. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. `JobPosting.Meta.ordering` is now at `jobs/models.py:959`; `jobs/closing.py`, `applications/quiet.py`, `applications/models.py:427` and `docker/compose.yml:63` have not moved. Both announcers start by asking which owners have anything to announce, with `.values_list("owner", flat=True).distinct()`. The models carry a default ordering, and Django adds ordering columns to a `SELECT DISTINCT`, so the question returns one row per listing or application rather than one per owner. Each row then runs the whole per-owner pass again. The results stay correct, because the stamps leave nothing fresh after the first pass for an owner, but the scheduler repeats the work every five minutes, and for the quiet announcer the cost grows with the square of a person's applications. - **Where:** `jobs/closing.py:77-88` (`announce_closing_postings`), over `JobPosting.Meta.ordering = ("-noted_at", "-created_at")` at `jobs/models.py:956`; `applications/quiet.py:70-81` (`announce_quiet_applications`), over `Application.Meta.ordering = ("-created_at",)` at `applications/models.py:427`. - **What happens:** the printed SQL is `SELECT DISTINCT "jobs_jobposting"."owner_id" AS "owner", "jobs_jobposting"."noted_at", "jobs_jobposting"."created_at" ... ORDER BY "jobs_jobposting"."noted_at" DESC, ...` and `SELECT DISTINCT "applications_application"."owner_id" AS "owner", "applications_application"."created_at" ... ORDER BY "applications_application"."created_at" DESC`. So the closing announcer iterates once per listing with a closing date today or later, each iteration loading the user (`_owner_of`) and running `closing_for`; the quiet announcer iterates once per application of every active account, whatever its status, each iteration loading the user and running the annotated `quiet()` query over all that person's applications. A person with 300 applications costs 600 queries, 300 of them scanning 300 applications with activity subqueries, on every pass (`docker/compose.yml:63` runs a pass every 300 seconds). - **Why it matters:** the comment at `jobs/closing.py:77-78` promises "three questions rather than a thousand", and the code asks one per row instead. On SQLite the passes hold the database for the whole loop, in step with the web workers, for work that finds nothing new. - **Fix:** clear the ordering in both querysets: `.values_list("owner", flat=True).order_by().distinct()`. ## Done when - `tests/test_deadlines.py`: with two listings closing within the notice for one owner, `announce_closing_postings` runs a fixed number of queries (`django_assert_num_queries`) that does not grow with a third listing. - `tests/test_quiet.py`: the same for `announce_quiet_applications` with several applications for one owner.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:46:25 +00:00

tiagoagueda added the

tier

enhancement

labels

2026-10-01 10:46:25 +00:00

tiagoagueda referenced this issue from a commit

2026-10-06 03:03:42 +00:00

Ask the closing and quiet announcers for one row per owner

tiagoagueda commented

2026-10-06 09:32:08 +00:00