Postgres search reads whole tables per query on large projects (admission count, FTS predicate placement, count) #1791

Production search on large projects reads whole tables per query. On a 1 CU Neon compute those reads mostly miss the cache, so a hybrid search running alone has a p50 of 12.8 s, and under MCP fan-out searches time out at 30 s. Full evidence (EXPLAIN plans, Logfire spans, costs): https://github.com/basicmachines-co/basic-memory-cloud/issues/2610#issuecomment-6283405950

Measured on a tenant with 480k vector chunks and 95k FTS chunks, on core 91d83d6 (includes #1746). Four other large tenants show the same shape.

Defects, in priority order

• Admission count (pgvector_index.PgVectorIndex.search, if admission is not None): SELECT count(*) FROM (... JOIN ... LIMIT :exact_cap) plans as a parallel hash join over both full tables. LIMIT cannot short-circuit the hash build. Measured on a 480k-chunk tenant: 8.2 s cold, 4.2 s warm, 318k pages. Fix ideas: decide from per-project manifest counts (or counts cached per scope and type), or count from search_vector_chunks alone with an index-only scan on (project_id, source_type, embedding_status). Acceptance: under 50 ms and under 1k pages at 480k chunks.
• FTS predicate placement (compile_fts_filter / document_fts_vector_sql): the document-vector predicate and its per-operand EXISTS subqueries run on every row in scope, before the candidate join, because of the planner's 1-row estimate. Fix ideas: a MATERIALIZED candidate CTE joined by primary key, with operand presence computed in one grouped pass over search_index_fts_chunks restricted to candidates (bool_or(tsv @@ op_i) per search_index_id). Acceptance: pages touched scale with candidate count, not scope size.
• Chunk candidate leg uses a seq scan. The GIN index on search_index_fts_chunks.textsearchable_index_col loses to a seq scan because TOAST is not costed. Each scan detoasts about 375 MB. Make the GIN path explicit.
• Count: PostgresFts.count repeats the full FTS cost. Replace it with a limit+1 has-more probe, or a capped count.
• Manifest join: the HNSW candidate path fetches fat search_vector_chunks heap rows only to check status columns. Add a covering index or move the status columns.

Test

Include a reproduction harness: 300k+ chunk fixture, cold cache (restart, or pg_prewarm off), and report EXPLAIN (ANALYZE, BUFFERS) page counts, not only timings. Warm-cache benchmarks hide all of this. The #1746 numbers were warm.

Cloud is adding a per-tenant search concurrency guard separately. Raising compute is a stopgap only: until the admission count is fixed, its 2.5 GB sweep flushes most of a larger cache.