Applications and companies tables: an unconditional DISTINCT makes every count and page de-duplicate whole rows #552

New issue

Closed

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

tiagoagueda commented

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

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading the tables' querysets, capturing their SQL and timing them with and without DISTINCT.

Checked again on 2026-10-01 against main at 0a5cad587: still present.

ApplicationFilterMixin.filter_queryset and CompanyListView.get_queryset both end in .distinct() whatever was asked. The paginator's count then becomes COUNT(*) over a SELECT DISTINCT of every selected column and every correlated subquery, and the page query de-duplicates whole rows, the posting's description included, so neither can stop at the page's rows. On the applications table no filter can repeat a row, so the DISTINCT buys nothing; on the companies table only two filters need it.

Where:
- applications/views.py:113 (return queryset.distinct() in ApplicationFilterMixin.filter_queryset, shared by the table and the board)
- jobs/views.py:105 (return self.table.apply(queryset).distinct() in CompanyListView.get_queryset)
What happens: on applications, search and the column filters go through posting and posting__company (single foreign keys), status, priority, channel and dates are columns, the quiet and state filters use annotations, and tags__slug matches at most one tag per application because slugs are unique per owner (unique_tag_slug_per_owner). On companies, only q (through industries__name and identifiers__value) and the Industries column filter (jobs/tables.py:53-57) join a many-valued relation. With 1,500 applications and 800 companies on SQLite:
- applications count: 13 ms with DISTINCT, 3 ms without; page of 50: 57 ms against 29 ms
- companies count: 9 ms against under 1 ms
Why it matters: every view of either table, every filter keystroke and the board pay for de-duplicating rows that cannot repeat, and the cost grows with the record.
Fix: drop .distinct() from ApplicationFilterMixin.filter_queryset. On companies, express the search's industry and identifier matches as Exists(...), as core/search.py:251-252 already does, and apply .distinct() only when the Industries column filter is present (or give that column an Exists lookup too), so the plain table never needs it.

Related to #231 (closed), whose item 2 named "search joins and .distinct() on top" and replaced the aggregates with subqueries but left the unconditional DISTINCT.

Done when

A test in tests/test_tables.py captures the SQL of the applications table and the companies table with no filters and asserts neither contains DISTINCT; a second asserts a company in two matching industries still appears once when filtered by industry.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading the tables' querysets, capturing their SQL and timing them with and without `DISTINCT`. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. `ApplicationFilterMixin.filter_queryset` and `CompanyListView.get_queryset` both end in `.distinct()` whatever was asked. The paginator's count then becomes `COUNT(*)` over a `SELECT DISTINCT` of every selected column and every correlated subquery, and the page query de-duplicates whole rows, the posting's description included, so neither can stop at the page's rows. On the applications table no filter can repeat a row, so the `DISTINCT` buys nothing; on the companies table only two filters need it. - **Where:** - `applications/views.py:113` (`return queryset.distinct()` in `ApplicationFilterMixin.filter_queryset`, shared by the table and the board) - `jobs/views.py:105` (`return self.table.apply(queryset).distinct()` in `CompanyListView.get_queryset`) - **What happens:** on applications, search and the column filters go through `posting` and `posting__company` (single foreign keys), `status`, `priority`, `channel` and dates are columns, the quiet and state filters use annotations, and `tags__slug` matches at most one tag per application because slugs are unique per owner (`unique_tag_slug_per_owner`). On companies, only `q` (through `industries__name` and `identifiers__value`) and the *Industries* column filter (`jobs/tables.py:53-57`) join a many-valued relation. With 1,500 applications and 800 companies on SQLite: - applications count: 13 ms with `DISTINCT`, 3 ms without; page of 50: 57 ms against 29 ms - companies count: 9 ms against under 1 ms - **Why it matters:** every view of either table, every filter keystroke and the board pay for de-duplicating rows that cannot repeat, and the cost grows with the record. - **Fix:** drop `.distinct()` from `ApplicationFilterMixin.filter_queryset`. On companies, express the search's industry and identifier matches as `Exists(...)`, as `core/search.py:251-252` already does, and apply `.distinct()` only when the *Industries* column filter is present (or give that column an `Exists` lookup too), so the plain table never needs it. Related to #231 (closed), whose item 2 named "search joins and `.distinct()` on top" and replaced the aggregates with subqueries but left the unconditional `DISTINCT`. ## Done when - A test in `tests/test_tables.py` captures the SQL of the applications table and the companies table with no filters and asserts neither contains `DISTINCT`; a second asserts a company in two matching industries still appears once when filtered by industry.

tiagoagueda added this to the 0.6.0 milestone

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

tiagoagueda added the

tier

enhancement

labels

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

tiagoagueda referenced this issue from a commit

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

Stop de-duplicating the applications and companies tables unconditionally

tiagoagueda commented

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