match_thoughts_recency does a full table scan instead of using the HNSW index (even the canonical WHERE+ORDER BY form needs enable_seqscan hint)

[lloydsilvertwo]

Summary

match_thoughts_recency (schemas/recency-boosted-match-thoughts/schema.sql) does a full sequential scan over thoughts on every call instead of using the hnsw vector index, even though the index exists on the table. On a table with ~9,300 rows this cost ~5.9s per call (measured via EXPLAIN (ANALYZE, BUFFERS) — Seq Scan, ~30,000 shared buffer touches). The same query pattern rewritten to actually use the index takes ~180ms (~3,200 buffers) on the same data. This scales linearly with table size and is a real risk for anyone hitting this RPC under any concurrent load.

Root cause (two compounding issues)

1. The blended ORDER BY can't be pushed through an ANN index.

match_thoughts_recency's ORDER BY is a computed blend:

ORDER BY (
  (1 - (t.embedding <=> query_embedding)) * (1.0 - recency_weight)
  + exp(-age_days/half_life_days) * recency_weight
) DESC

Postgres can only use an ivfflat/hnsw index when the ORDER BY expression matches the indexed operator directly (embedding <=> query_embedding). A derived/blended expression forces a full scan + sort regardless of what indexes exist. This is already noted in passing in #417 ("its blended ORDER BY can't use the HNSW index, so it does an exact scan") but doesn't seem to have its own issue or fix attempt.

2. Even the plain match_thoughts shape doesn't reliably get an index scan on a table this size.

This part surprised me. My first fix attempt was the standard "indexed candidate retrieval + re-rank" pattern: pull candidates via the raw-distance ORDER BY (the indexable form), then blend/re-rank only that small set. Testing live against real data, this wasn't enough on its own — match_thoughts's own canonical shape:

WHERE 1 - (t.embedding <=> query_embedding) > match_threshold
ORDER BY t.embedding <=> query_embedding
LIMIT match_count

also produces Seq Scan + Sort, not Index Scan, on this table (~9,300 rows), by default. Only forcing SET LOCAL enable_seqscan = off for the call flips the plan to Index Scan using thoughts_embedding_idx (confirmed via EXPLAIN (ANALYZE, BUFFERS) — buffer touches drop ~13x for the identical query and results). A bare ORDER BY ... LIMIT with no WHERE filter at all does use the index without any hint — it's specifically the WHERE-filter + ORDER BY combination that the planner under-costs on this table.

I don't know if this is pgvector-version-specific (0.8.0 here), table-size-specific, or a planner-statistics quirk, but it means match_thoughts may also be doing full scans on filtered calls for some deployments, not just match_thoughts_recency — worth checking on your own instance with EXPLAIN (ANALYZE, BUFFERS) before assuming the base RPC is index-accelerated.

Fix

Two changes, applied together, in match_thoughts_recency:

1. Rewrite as a two-stage query: an indexed candidate-retrieval CTE (raw-distance ORDER BY + LIMIT candidate_count, with candidate_count generously larger than match_count — I used LEAST(GREATEST(match_count * 10, 150), 500)), then re-rank only that small candidate set by the recency-blended score in an outer query.
2. Inside the function, before running the query: force the planner onto the index and raise the ANN search width, both scoped to the current transaction only:

   PERFORM set_config('enable_seqscan', 'off', true);
   PERFORM set_config('hnsw.ef_search', candidate_count::text, true);

   The ef_search bump matters for the same reason match_thoughts silently loses recall on filtered queries (HNSW ef_search default) #417 flags: raising candidate_count past the default ef_search=40 would otherwise silently truncate the candidate set before it ever reaches the re-rank stage.

Verified on my own instance:

• recency_weight=0 output is byte-identical (same ids, same order, same similarity values to full precision) to plain match_thoughts — the documented backward-compat guarantee holds.
• EXPLAIN ANALYZE on the rewritten function: 184ms, ~3,200 buffers (down from ~5,868ms / ~30,000 buffers).
• Recency-weighted output (recency_weight=0.2) still reorders sensibly — a same-day thought gets promoted ahead of slightly-higher-raw-similarity older ones, as intended.

Full patch (drop-in replacement — same signature and return columns, no caller changes needed):

CREATE OR REPLACE FUNCTION match_thoughts_recency(
  query_embedding  vector(1536),
  match_threshold  float   DEFAULT 0.7,
  match_count      int     DEFAULT 10,
  filter           jsonb   DEFAULT '{}'::jsonb,
  recency_weight   float   DEFAULT 0.0,
  half_life_days   float   DEFAULT 90.0
)
RETURNS TABLE (
  id          uuid,
  content     text,
  metadata    jsonb,
  similarity  float,
  created_at  timestamptz
)
LANGUAGE plpgsql
STABLE
PARALLEL SAFE
AS $$
DECLARE
  candidate_count int;
BEGIN
  IF recency_weight < 0.0 THEN recency_weight := 0.0; END IF;
  IF recency_weight > 1.0 THEN recency_weight := 1.0; END IF;
  IF half_life_days <= 0.0 THEN half_life_days := 90.0; END IF;

  candidate_count := LEAST(GREATEST(match_count * 10, 150), 500);

  -- Force the planner onto the HNSW index for the candidate stage below,
  -- and raise ef_search so the LIMIT doesn't get silently starved (see #417).
  -- Both scoped to this transaction only (is_local=true).
  PERFORM set_config('enable_seqscan', 'off', true);
  PERFORM set_config('hnsw.ef_search', candidate_count::text, true);

  RETURN QUERY
  WITH candidates AS (
    SELECT
      t.id,
      t.content,
      t.metadata,
      t.created_at,
      (1 - (t.embedding <=> query_embedding))::float AS raw_similarity
    FROM public.thoughts t
    WHERE
      (t.embedding <=> query_embedding) <= (1 - match_threshold)
      AND (filter = '{}'::jsonb OR t.metadata @> filter)
    ORDER BY t.embedding <=> query_embedding
    LIMIT candidate_count
  )
  SELECT
    c.id,
    c.content,
    c.metadata,
    (
      c.raw_similarity * (1.0 - recency_weight)
      +
      exp(
        -GREATEST(
          extract(epoch FROM (now() - c.created_at)) / 86400.0,
          0.0
        ) / half_life_days
      ) * recency_weight
    )::float AS similarity,
    c.created_at
  FROM candidates c
  ORDER BY
    (
      c.raw_similarity * (1.0 - recency_weight)
      +
      exp(
        -GREATEST(
          extract(epoch FROM (now() - c.created_at)) / 86400.0,
          0.0
        ) / half_life_days
      ) * recency_weight
    ) DESC
  LIMIT match_count;
END;
$$;

Environment

• pgvector 0.8.0
• Index: CREATE INDEX thoughts_embedding_idx ON public.thoughts USING hnsw (embedding vector_cosine_ops)
• Table size at time of testing: ~9,300 rows
• hnsw.ef_search default: 40 (min 1, max 1000)
• hnsw.iterative_scan default: off — tried relaxed_order as an alternative to enable_seqscan=off for the filtered case; it did not change the plan on this table, enable_seqscan=off was what actually worked

Happy to open a PR with this if it's useful — flagging the diagnosis first in case there's context I'm missing, e.g. whether forcing enable_seqscan=off inside a function body is something the project wants to avoid for other reasons (it's scoped to the transaction only, but it is a blunt instrument).

[github-actions]

Hey @lloydsilvertwo — welcome to Open Brain Source! 👋

Thanks for opening your first issue. Someone from the community will take a look soon.

If you're interested in contributing (code or not), check out CONTRIBUTING.md — we have paths for developers and non-developers alike.