timescale.info#162 · Find search matches through a text index
- timeline-app
- pull request
Follows up the 1M fork benchmark, where search on a fork measured 1.60× its source's time. ## What I found The fork slowdown didn't reproduce. Re-running `bench/fork-reads.mjs` at 1,000,000 moments, fork search was 1.06×, 1.22× and 1.15× its source's time across the three stages. At 200,000 it was 0.97–1.14×. The real problem was search itself. `oc_entity_search` had no full-text index, so every search read all of a timeline's search rows, and the planner often read the whole table. The cost grew with the timeline whatever was searched: 0.6–0.9 s at 1M moments even for one moment's number. ## Changes - **GIN index on the search vectors** (`oc_entity_search_document`). The schema already copies a live table's indexes onto its `_retired` twin, so `oc_entity_search_retired` gets the same index. Forks need that one: without it, a fork's search read every retired version of its source, which took 345 ms to find one row in the benchmark database. - **The index is on the text alone**, and each match's timeline is checked on its row. Putting the timeline first in the index would need the `btree_gin` extension. RPM and FreeBSD ship that in a separate contrib package, so it would add a dependency. - **The search query ranks and counts matches on narrow rows** (kind, ID, start, rank). It then reads only the page's 20–25 results whole, through the reader's `lookup`. Before, every match's title, 300-character excerpt and formatted times were computed and held for the count, which spilled to temporary files on broad searches. ## Measurements Searches through the store, median of 7, on the benchmark timelines: | Timeline | "harbor treaty" (7% match) | "777" (1,222 matches) | "123456" (1 match) | | --- | ---: | ---: | ---: | | 1M source, before | 936 ms | 601 ms | — | | 1M fork, before | 1,165 ms | 734 ms | — | | 1M source, now | 666 ms | 7 ms | 4 ms | | 1M fork, now | 875 ms | 12 ms | 7 ms | The benchmark's text uses only 12 words, so "harbor treaty" matches about 7% of everything. A search that broad still reads each match, because it has to rank and count them all. Costs of the index: - **Creating a timeline:** about 15% slower (100k moments: 67–69 s without it, 78–79 s with). - **Saves:** nothing measurable (2,500-moment saves at 1M: 18–19 s with, 18–22 s without). - **Size:** 72 MB for 1.33M search rows, about a tenth of the table. - **Migration:** building the indexes on the 1M benchmark database took 15 s. ## Tests - `test/search-postgres.mjs` checks that both text indexes exist. It doesn't check that searches use them: on a test timeline of 34 rows, the planner correctly reads by primary key instead. - `test/fork-read-through-postgres.mjs` already compares a fork's search results with a whole copy's through edits and deletions, and passes with the new query. - All 35 database tests, the 258 unit tests and the typecheck pass locally. ## Docs `docs/search.md` describes the index, what searches cost and the trade-offs.
Sources
Open #162 · Find search matches through a text index in the timeline