Why Postgres ignored our full-text search index

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.

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.