Vector search with BM25 reranking (RRF – Reciprocal Rank Fusion), filters, and facets can become complex very quickly:
* You’re combining multiple rankings (e.g., BM25 + vector similarity).
* You’re applying post-filtering or pre-filtering (categories, availability, dates, etc.).
* You’re computing facets/aggregations on top of already-ranked results.
* And sometimes you’re adding re-sorting (e.g., by date or popularity after relevance).
That’s where PostgreSQL really shines.
With `EXPLAIN (ANALYZE, BUFFERS)` you can:
* See whether your vector index (IVFFlat / HNSW) is actually used.
* Check if filters are applied before or after similarity search.
* Detect expensive bitmap heap scans or unnecessary sequential scans.
* Measure the real cost of CTEs vs. subqueries.
* Understand how RRF is materialized (temporary sort, window function, hash aggregation).
* Inspect memory usage and buffer hits to optimize `work_mem`.
For example, you can verify:
* Whether `ROW_NUMBER()` for RRF is forcing a full sort.
* If facets are computed on the full dataset instead of the top-K.
* If your `WHERE embedding <-> query_embedding < X` cutoff is selective enough.
* Whether parallel workers are being used.
Instead of guessing, you see exactly:
* How many rows are scanned
* How many are filtered out
* Where time is spent
* Which indexes are (or aren’t) used
That transparency is rare in vector databases.
With PostgreSQL, hybrid search isn’t a black box — it’s fully inspectable and tunable. And once you start reading execution plans comfortably, optimizing vector + BM25 + facets becomes a systematic engineering task rather than trial and error.
That’s the real joy.