Listings → Applied and Everything: one extra query per applied row, which the query budget test cannot see #558

New issue

Closed

opened 2026-10-01 10:47:38 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-01 10:47:38 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by counting the queries of each Listings tab as the data grows.

Checked again on 2026-10-01 against main at 0a5cad587: still present. The code is now at jobs/models.py:766-767 (with_application_count) and jobs/models.py:821-835 (undecided and in_state); the template, ListingListView.get_queryset and the budget test have not moved.

An applied row draws its Application button with {% with application=listing.applications.all|first %}, and ListingListView.get_queryset prefetches nothing, so each applied row on the page costs one more query, up to the page size. The query budget test added by #231 opens only the default tab, where no row is applied, so it cannot catch this.

Where: templates/jobs/partials/listing_row.html:115, jobs/listing_views.py:88-102 ListingListView.get_queryset, jobs/models.py:763-764 with_application_count (used by undecided and in_state, jobs/models.py:818-832), tests/test_query_budget.py:93-101 (PAGES has ("the listings page", "listings:list", {}) only).
What happens: measured on the snapshot: with 3 companies the tabs cost 10 (to decide), 13 (applied) and 13 (everything) queries; with 18 companies 10, 28 and 28. At 50 rows a page the Applied tab makes about 60 queries instead of 10. The rows are also counted with Count("applications", distinct=True), a GROUP BY over every posting column, description included, which is the one-join aggregate #231 replaced with Exists and correlated subqueries elsewhere (tab_counts, CompanyQuerySet.with_table_data).
Why it matters: the tab somebody opens to see where they applied is the one that grows with how much they applied; the budget test gives false comfort.
Fix: annotate the application to link to instead of fetching it per row, first_application_id=Subquery(Application.objects.filter(posting=OuterRef("pk")).order_by("created_at", "pk").values("pk")[:1]), and link with {% url 'applications:detail' listing.first_application_id %} (or prefetch_related("applications") if the row needs more). Count applications with Coalesce(Subquery(...), 0) as with_table_data does for companies. Add the applied and all tabs to PAGES.

Related to #231 (closed), whose budget test opens only the default tab.

Done when

tests/test_query_budget.py has ("the listings page, applied", "listings:list", {"state": "applied"}) and ("the listings page, everything", "listings:list", {"state": "all"}) in PAGES, with fixtures that include applied listings; both fail today.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by counting the queries of each Listings tab as the data grows. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The code is now at `jobs/models.py:766-767` (`with_application_count`) and `jobs/models.py:821-835` (`undecided` and `in_state`); the template, `ListingListView.get_queryset` and the budget test have not moved. An applied row draws its *Application* button with `{% with application=listing.applications.all|first %}`, and `ListingListView.get_queryset` prefetches nothing, so each applied row on the page costs one more query, up to the page size. The query budget test added by #231 opens only the default tab, where no row is applied, so it cannot catch this. - **Where:** `templates/jobs/partials/listing_row.html:115`, `jobs/listing_views.py:88-102` `ListingListView.get_queryset`, `jobs/models.py:763-764` `with_application_count` (used by `undecided` and `in_state`, `jobs/models.py:818-832`), `tests/test_query_budget.py:93-101` (`PAGES` has `("the listings page", "listings:list", {})` only). - **What happens:** measured on the snapshot: with 3 companies the tabs cost 10 (to decide), 13 (applied) and 13 (everything) queries; with 18 companies 10, 28 and 28. At 50 rows a page the Applied tab makes about 60 queries instead of 10. The rows are also counted with `Count("applications", distinct=True)`, a `GROUP BY` over every posting column, description included, which is the one-join aggregate #231 replaced with `Exists` and correlated subqueries elsewhere (`tab_counts`, `CompanyQuerySet.with_table_data`). - **Why it matters:** the tab somebody opens to see where they applied is the one that grows with how much they applied; the budget test gives false comfort. - **Fix:** annotate the application to link to instead of fetching it per row, `first_application_id=Subquery(Application.objects.filter(posting=OuterRef("pk")).order_by("created_at", "pk").values("pk")[:1])`, and link with `{% url 'applications:detail' listing.first_application_id %}` (or `prefetch_related("applications")` if the row needs more). Count applications with `Coalesce(Subquery(...), 0)` as `with_table_data` does for companies. Add the `applied` and `all` tabs to `PAGES`. Related to #231 (closed), whose budget test opens only the default tab. ## Done when - `tests/test_query_budget.py` has `("the listings page, applied", "listings:list", {"state": "applied"})` and `("the listings page, everything", "listings:list", {"state": "all"})` in `PAGES`, with fixtures that include applied listings; both fail today.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:47:38 +00:00

tiagoagueda added the

tier

enhancement

labels

2026-10-01 10:47:38 +00:00

tiagoagueda referenced this issue from a commit

2026-10-05 23:54:30 +00:00

Count a listing's applications with subqueries and link the first without fetching it

tiagoagueda commented

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