Why Postgres ignored our full-text search index

HOAi's AI agents at HOAi experienced full-text search queries taking 3.2 seconds or more because Postgres ignored the GIN index and materialized a CTE, scanning all pages for an association. The fix was a composite GIN index on (property_id, fts) and dropping the global index, reducing query time to 132 ms warm.

Our AI agents at HOAi search a community association’s documents to answer questions such as what’s the pet policy, when is the next board meeting, and where can I find the latest budget? Before a model judges which pages are relevant, Postgres runs a full-text search to retrieve candidates for that association. That retrieval should have been a quick index lookup. Instead, it sometimes took five or six seconds. The index existed, Postgres maintained it correctly, and the query returned the right results. The shape of the query made the planner ignore the index entirely. The query that hid the index The pages for every association live in one large multi-tenant table. Each page row stores the association key in a column named property id , along with a generated tsvector column. Postgres maintains the GIN index automatically. The original query built a common table expression CTE over the page table and referenced it twice. Postgres 12+ inlines CTEs by default — unless a query references a CTE more than once, which causes Postgres to materialize it. Here is the relevant shape, with the access-control details removed: WITH association pages AS SELECT FROM page WHERE property id = $1 , matches AS SELECT id FROM association pages WHERE fts @@ websearch to tsquery $2 SELECT association pages. FROM matches JOIN association pages USING id ; That detail changed the entire plan. Postgres first materialized every page belonging to the association, then applied the full-text predicate as a sequential scan over that result. The GIN index was never touched. On one production association, the numbers were stark. A few thousand pages belonged to that association, out of a table holding hundreds of millions of pages and hundreds of gigabytes. A few dozen pages matched the search phrase. Yet the materialized scan consumed essentially the entire query: about 3.2 seconds of a 3.2-second query, with colder traces stretching to five or six seconds. I keep relearning this lesson every few years: An index existing does not mean your query uses it. Confirm the real plan with EXPLAIN ANALYZE, BUFFERS on the actual slow shape. Why the obvious rewrite still failed Dropping the materialized CTE fixed the first problem. Then we hit another one: with a per-association index and a separate global full-text index available, the planner combined two bitmaps. For a common phrase, it first pulled hundreds of thousands of matches from the global posting list across every tenant. Only then did it intersect that list with the one association we cared about. That path took about 3.4 seconds. Forcing the association-key btree path instead ran in 132 ms with a warm cache, but it touched roughly 37,000 buffers. It looked good warm and still had a nasty cold tail. Filter and match in one index scan The fix was to make tenant filtering and term matching happen together : - Rewrite the database query as a single non-materialized SELECT , with all visibility, association, path, and full-text predicates applied before ranking. - Add a composite GIN index on so Postgres can resolve property id, fts property id = X AND fts @@ query in one index scan, returning only matching pages for that association. - Drop the standalone global full-text index so the planner cannot fall back to the expensive intersect-two-bitmaps path. Stripped of the access and path checks, the rewritten query looks like this: SELECT ... FROM page WHERE property id = $1 AND fts @@ websearch to tsquery $2 ; The new index finally matched the question we were asking: which pages in this association match these terms ? Validate the plan shape, not laptop timing I couldn’t reproduce production scale on a laptop, so I tested the plan shape on a synthetic corpus: one 9,000-page target association plus 500,000 noise pages. | Scenario | Time | Buffers | |---|---|---| | Original query, warm | 54 ms | 44,418 | | Original query, cold | 1,026 ms | 159,457 | | Query using the composite index | 8.8 ms | 746 | | Same query, standalone index removed | 2.1 ms | 742 | These aren’t production latencies, and I wouldn’t use them to claim an exact production speedup. What mattered was the drop in buffers: Postgres stopped scanning a global posting list and returned the matches for one tenant directly. The takeaway When a query is usually fast but occasionally terrible, a warm benchmark can hide the real problem. Inspect the production plan, count the buffers, and pay attention to how much irrelevant data the database touches. An index is useful only if the planner can use it for the shape of the question you are asking. In a multi-tenant search corpus, filter the tenant and match the terms in the same index scan.