Why search took two seconds
Every food search scanned two million rows and took about the same two seconds, whatever you typed. The fix was to stop scanning for an answer that almost never lives there — with a test proving the results don't change.
Food search was taking around two seconds. Not occasionally — reliably, and for everything.
That consistency was the tell. A search that’s slow because a term is popular gets faster when you type something obscure. Ours didn’t care what you typed, which meant the cost wasn’t proportional to the answer. It was proportional to the catalog, and the catalog is about 1.9 million rows.
What was actually happening
A multi-word search has to match each word against the food’s name, its brand, or one of its aliases. Written the obvious way, that’s an OR across two ILIKE comparisons and an EXISTS subquery for the aliases.
Postgres can combine several index lookups into one bitmap scan — but not when the OR contains a subquery. So the trigram index on food names went unused, the planner fell back to an index on is_public, which is true for essentially every row, and every keystroke paid to walk the entire catalog. The fixed cost is exactly why every query took the same two seconds.
The fix is about ranking, not indexes
The instinct is to fight the planner. The better move was to notice that we already know where the answer comes from.
Our ranking puts curated generic staples first — the hand-checked whole foods, “Chicken Breast (Roasted)”, “Oatmeal (Cooked)” — ahead of the shouty branded rows. And above even that sits a personal-frequency boost: foods you have logged before outrank everything.
Which means for any search, the winners can only come from two small sets: the 13,224 curated rows, and the handful of foods you personally log. Both are cheap to reach. So we query that pool first, and if it fills the page, we stop. The 1.9 million-row long tail is only touched when a genuinely brand-specific search — “tyson chicken breast” — can’t be answered without it, and then only once.
Searches now come back in roughly 150 milliseconds.
The part that mattered more than the speed
Speed that quietly reorders your results is not an improvement. It’s the “brown rice → 16 PIECES” failure mode wearing a stopwatch: the app gets faster and the answers get worse, and nobody notices for a month.
So the tests assert the tiering is invisible. They run the old, slow query as an oracle and require the new one to return the same rows in the same order, both when the fast pool fills the page and when it has to fall through to the long tail.
Writing those tests found a real bug that had nothing to do with speed. Our bulk import had written thousands of rows inside one transaction, so their timestamps tie to the millisecond — and pairs like “Apple, dried” and “Apple, baked” matched on every single ranking key. With nothing left to break the tie, their order was whatever the query planner happened to emit, which meant results could silently reshuffle when the planner changed its mind. Adding a final tiebreak made the ordering total, and the reshuffling stopped.
That’s the kind of bug you only find when you make a machine check what you’d otherwise eyeball.
Where this shows up for you
Search is faster on every surface — the app, the web app, and the Claude connector, which shares the same ranking code so the answer you get from an assistant matches the one in the app. That sharing was its own piece of work: the two had drifted apart, and for months Claude would answer “chicken breast” with a 40-calorie slice of lunch meat while the app gave the right row. One definition, two callers, so a fix like this one reaches both at once.