Search: each group counts its matches and then fetches them, two full scans per group with SQLite's Python LIKE #1116

New issue

Closed

opened 2026-10-31 03:04:07 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-31 03:04:07 +00:00

Owner

What exists today

src/postulo/core/search.py:149-155 take() returns Found(total=rows.count(), hits=[build(row) for row in rows[:limit]]): one COUNT and one ordered slice per group. search() (:658) calls ten group functions (GROUPS, :640-651); search_career() (:550) loops over several career models, each with its own take(). The rows are filtered with icontains over several fields and ordered by ranked() (:132), a CASE WHEN title icontains. On SQLite, core/sqlite_like.py:50-51 replaces like with a Python function (#505, so that case folding works beyond ASCII), so every row of every filtered table is passed through Python once for the count and again for the ordered slice.

Measured with CaptureQueriesContext on the second GET of core:search?q=aperture with 300 companies, under the test settings (probe audit/m_scripts/test_probe_queries.py): 51 queries, 75 ms, two per table.

What changed from the candidate: search does not run "per keystroke". The search-everything button in templates/cotton/site_search.html is a plain submit to core:search; only the table filters search live, and they do not use take(). So the cost is per search, not per letter.

What would change

One query per group: annotate the total with a window function over the filtered rows and take the first limit (rows.annotate(total=Window(Count("pk")))[:limit]), reading the total off the first row, and 0 when there is none. Window functions run on SQLite 3.25+ and PostgreSQL, both supported. The "and N more" line keeps its exact number. On SQLite the Python like still runs once per row, but once rather than twice.

What it saves or protects

small: about half the scans of a search (51 queries for 10 groups and the career sections, measured 75 ms with 300 companies), more on SQLite where every LIKE is a Python call

Accessibility

none

Decisions

Window count, or fetch limit + 1 and say "more" without a number? Recommended: the window count, so nothing a person sees changes.

Found by the efficiency and reliability audit of 2026-10-30 (finding Q5; index postulo/postulo#1108). Checked against main at daee30b15 (2026-10-30).

## What exists today `src/postulo/core/search.py:149-155` `take()` returns `Found(total=rows.count(), hits=[build(row) for row in rows[:limit]])`: one `COUNT` and one ordered slice per group. `search()` (`:658`) calls ten group functions (`GROUPS`, `:640-651`); `search_career()` (`:550`) loops over several career models, each with its own `take()`. The rows are filtered with `icontains` over several fields and ordered by `ranked()` (`:132`), a `CASE WHEN title icontains`. On SQLite, `core/sqlite_like.py:50-51` replaces `like` with a Python function (#505, so that case folding works beyond ASCII), so every row of every filtered table is passed through Python once for the count and again for the ordered slice. Measured with `CaptureQueriesContext` on the second GET of `core:search?q=aperture` with 300 companies, under the test settings (probe `audit/m_scripts/test_probe_queries.py`): 51 queries, 75 ms, two per table. What changed from the candidate: search does not run "per keystroke". The search-everything button in `templates/cotton/site_search.html` is a plain submit to `core:search`; only the table filters search live, and they do not use `take()`. So the cost is per search, not per letter. ## What would change One query per group: annotate the total with a window function over the filtered rows and take the first `limit` (`rows.annotate(total=Window(Count("pk")))[:limit]`), reading the total off the first row, and 0 when there is none. Window functions run on SQLite 3.25+ and PostgreSQL, both supported. The "and N more" line keeps its exact number. On SQLite the Python `like` still runs once per row, but once rather than twice. ## What it saves or protects small: about half the scans of a search (51 queries for 10 groups and the career sections, measured 75 ms with 300 companies), more on SQLite where every LIKE is a Python call ## Accessibility none ## Decisions - **Window count, or fetch `limit + 1` and say "more" without a number?** Recommended: the window count, so nothing a person sees changes. --- Found by the efficiency and reliability audit of 2026-10-30 (finding Q5; index postulo/postulo#1108). Checked against `main` at `daee30b15` (2026-10-30).

tiagoagueda added this to the 0.6.0 milestone

2026-10-31 03:04:07 +00:00

tiagoagueda added the

performance

enhancement

labels

2026-10-31 03:04:07 +00:00

tiagoagueda referenced this issue

2026-10-31 03:04:40 +00:00

Efficiency and reliability audit of 2026-10-30: index #1108

tiagoagueda referenced this issue from a pull request that will close it,

2026-11-11 13:40:10 +00:00