max / makenotwork
| 1 | -- Drop idx_posts_body_trgm (migration 018). It never had a reader. |
| 2 | -- |
| 3 | -- 018 added two things at once: trigram GIN indexes "for ILIKE/similarity |
| 4 | -- queries" and tsvector columns "for full-text search ranking". Post matching |
| 5 | -- went to the tsvector half and stayed there; the trigram half on post bodies |
| 6 | -- was speculative and nothing was ever written against it. |
| 7 | -- |
| 8 | -- Nothing in the tree can use a trigram index on posts.body_markdown: |
| 9 | -- |
| 10 | -- * search_threads (queries/search.rs) is the only reader of post text as a |
| 11 | -- predicate, and post_matches filters on `p.search_tsv @@ q.tsq` alone. The |
| 12 | -- trigram `%` branch in that query is `t.title % $2` — threads, not posts. |
| 13 | -- * Every other read of body_markdown is a projection: the `LEFT(..., 200)` |
| 14 | -- snippet in the same query, get_post_body_markdown_in_community, and the |
| 15 | -- edit-form fetch in queries/post.rs. A projection uses no index. |
| 16 | -- * search_users (queries/admin.rs) is the tree's only other ILIKE, and it is |
| 17 | -- on users.username. |
| 18 | -- |
| 19 | -- So it is pure write amplification. Post bodies are the largest text in the |
| 20 | -- schema and a trigram GIN tokenizes every one into three-character grams, so |
| 21 | -- this is the most expensive index here to maintain, on the hottest write path |
| 22 | -- (every post insert and every edit), read by nothing. |
| 23 | -- |
| 24 | -- Worth recording for whoever reaches for this again: even with a reader, a |
| 25 | -- trigram index over long-form prose is the wrong tool. Trigram selectivity |
| 26 | -- collapses as documents grow — common grams appear in nearly every post, so |
| 27 | -- the bitmap scan degrades toward a seq scan plus a recheck on every row. It |
| 28 | -- earns its keep on short strings (titles, usernames, slugs), which is exactly |
| 29 | -- where 018's other two trigram indexes sit. Post bodies belong to tsvector. |
| 30 | -- |
| 31 | -- idx_threads_title_trgm stays: it is live, backing the `t.title % $2` fuzzy |
| 32 | -- branch that gives search its typo tolerance. |
| 33 | -- |
| 34 | -- IF EXISTS so a partial re-run cannot hard-fail, matching 035. |
| 35 | |
| 36 | IF EXISTS idx_posts_body_trgm; |
| 37 |