Hybrid search ============= Hybrid search combines semantic (vector/embedding) search on knowledge base embeddings with one or more additional filter mechanisms: - **Relational predicate filtering** — filter results by any column in the source table, for example ``WHERE category = 'vegetarian'`` or ``WHERE published_date > '2024-01-01'`` . - **BM25 full-text search** — rank results by keyword relevance using a BM25 index, ensuring that specific words or phrases appear in the results. These two approaches can be used independently or together alongside semantic search. AI pipelines handle knowledge base construction — including embedding generation — as normal. Hybrid search is applied at query time by constructing custom SQL that combines the vector table with your source table. Prerequisites ------------- pgvector iterative index scans ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Requires pgvector 0.8.0 or later (EDB Postgres AI defaults to 0.8.1). Iterative index scans are the key enabler for combining vector ``ORDER BY`` with ``WHERE`` filters efficiently. Without them, the vector index pre-fetches a fixed number of candidates before filtering, which means a highly selective filter may return fewer results than your ``LIMIT`` requests. With iterative scans enabled, the index keeps producing candidates until your ``LIMIT`` is satisfied, even after filtering. Enable iterative scans for HNSW indexes: .. code:: sql SET hnsw.iterative_scan = relaxed_order; For IVFFlat indexes: .. code:: sql SET ivfflat.iterative_scan = on; For very selective filters (few rows match), set a safety limit to prevent the scan from traversing the entire index: .. code:: sql SET hnsw.iterative_scan_max_tuples = 20000; .. Important:: The vector `ORDER BY` and the `WHERE` filter must be in the same query level — not separated by a CTE boundary — for iterative scans to take effect. The query patterns below are structured accordingly. BM25 (optional) ^^^^^^^^^^^^^^^ BM25 full-text filtering requires the ``vchord_bm25`` and :ref:`pg_tokenizer ` extensions. These are user-installed and managed. See :ref:`VectorChord-BM25 ` for setup details. Helper functions and metadata ----------------------------- aidb.kb_query_encode() ^^^^^^^^^^^^^^^^^^^^^^ Encodes a text query using the embedding model configured for a specific knowledge base, without needing to look up the model name: .. code:: sql SELECT aidb.kb_query_encode(public.my_kb, test query); Use this in place of ``aidb.encode_text('', '')`` when constructing custom vector queries. ``aidb.knowledge_bases`` view ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The ``aidb.knowledge_bases`` view (also accessible as ``aidb.kbs`` ) exposes all the metadata needed to construct hybrid queries: .. csv-table:: :header: Column,Type,Description :widths: 12,15,25 :align: left :class: longtable `id`,integer,Internal identifier `name`,text,Knowledge base name `vector_schema`,text,Schema of the embeddings table `vector_table`,text,Embeddings storage table `model_name`,text,Embedding model used by this KB `distance_operator`,aidb.distanceoperator,Distance metric enum value `distance_operator_sql`,text,The distance operator in SQL-operator syntax `vector_data_column`,text,Column storing the embeddings `vector_key_column`,text,Column linking embeddings to source rows `vector_index`,jsonb,Vector index configuration `pipeline_ids`,integer[],IDs of all pipelines attached to this knowledge base `pipeline_names`,text[],Names of all pipelines attached to this knowledge base .. code:: sql SELECT name, vector_schema, vector_table, distance_operator_sql FROM aidb.knowledge_bases; aidb.rerank_text() ^^^^^^^^^^^^^^^^^^ **Optional:** After retrieving candidates, pass them to a reranking model for a final relevance-ordered result: .. code:: sql SELECT aidb.rerank_text(my_reranking_model, query text, ARRAY[result1, result2, ...]); Reranking models such as ``nim_reranking`` and ``hf_tei_reranking`` are supported as a separate post-processing step after retrieval. -------------- Query patterns -------------- Vector + relational filter ^^^^^^^^^^^^^^^^^^^^^^^^^^ Filter semantic search results by any column in the source table. The filter is applied at the same query level as the vector ``ORDER BY`` , allowing iterative index scans to produce exactly ``LIMIT`` results. .. code:: sql SET hnsw.iterative_scan = relaxed_order; SELECT s.id, s.description, kb.value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) AS distance FROM my_kb_vector kb JOIN my_source_table s ON s.id::TEXT = kb.source_id WHERE s.category_id = 2 -- relational filter on source table ORDER BY kb.value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) LIMIT 10; Vector + BM25 as a filter ^^^^^^^^^^^^^^^^^^^^^^^^^ Use a BM25 score threshold as a ``WHERE`` condition, then order results by vector distance. This returns the semantically nearest results that also satisfy the keyword match. .. code:: sql SET hnsw.iterative_scan = relaxed_order; SELECT s.id, s.description, kb.value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) AS distance FROM my_kb_vector kb JOIN my_source_table s ON s.id::TEXT = kb.source_id WHERE s.bm25_col <&> to_bm25query(my_bm25_idx, tokenize(shoes, bert)) < -1.0 -- BM25 filter ORDER BY kb.value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) LIMIT 10; Vector + BM25 via Reciprocal Rank Fusion ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Reciprocal Rank Fusion (RRF) runs semantic and keyword searches independently, then merges and scores the result sets. This avoids over-filtering and surfaces results that rank well in either dimension. .. code:: sql WITH semantic AS ( SELECT source_id AS id, ROW_NUMBER() OVER (ORDER BY value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) AS rank FROM my_kb_vector ORDER BY value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) LIMIT 50 ), fulltext AS ( SELECT id::TEXT AS id, ROW_NUMBER() OVER (ORDER BY bm25_col <&> to_bm25query(my_bm25_idx, tokenize(shoes, bert))) AS rank FROM my_source_table ORDER BY bm25_col <&> to_bm25query(my_bm25_idx, tokenize(shoes, bert)) LIMIT 50 ) SELECT COALESCE(s.id, f.id) AS id, COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + f.rank), 0) AS rrf_score FROM semantic s FULL OUTER JOIN fulltext f ON s.id = f.id ORDER BY rrf_score DESC LIMIT 10; The constant ``60`` in the RRF formula is the standard rank smoothing factor. Adjust it to control the relative weight of high-rank vs lower-rank results. Vector + relational + BM25 (all three) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Combine all three filtering mechanisms in a single query: .. code:: sql SET hnsw.iterative_scan = relaxed_order; SELECT s.id, s.description, kb.value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) AS distance, s.bm25_col <&> to_bm25query(my_bm25_idx, tokenize(shoes, bert)) AS bm25_score FROM my_kb_vector kb JOIN my_source_table s ON s.id::TEXT = kb.source_id WHERE s.category_id = 2 -- relational filter AND s.bm25_col <&> to_bm25query(my_bm25_idx, tokenize(shoes, bert)) < -1.0 -- BM25 filter ORDER BY kb.value <=> aidb.kb_query_encode(public.my_kb_vector, summer fashion) LIMIT 10; -------------- Worked examples --------------- Example 1: Relational predicate filtering ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ This example shows how to combine semantic vector search with a relational ``WHERE`` filter to narrow results to a specific category. A pipeline embeds product names into a knowledge base, and queries then retrieve semantically similar results filtered by category. **Set up the source table and pipeline** Create the source table: .. code:: sql CREATE TABLE products ( id SERIAL PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL ); Populate it with sample data: .. code:: sql INSERT INTO products (product_name, category) VALUES (Hamburger, meat), (Cheeseburger, meat), (Fish n Chips, fish), (Fries, vegetarian), (Burrito, meat), (Pizza, meat), (Sandwich, meat), (Veggie Burger, vegetarian), (Kebab, meat); Create a pipeline to embed ``product_name`` into a knowledge base: .. code:: sql SELECT aidb.create_pipeline( name => food_products, source => products, source_key_column => id, source_data_column => product_name, step_1 => KnowledgeBase, step_1_options => aidb.knowledge_base_config( model => bert, data_format => Text ) ); Run the pipeline to generate embeddings: .. code:: sql SELECT aidb.run_pipeline(food_products); **Standard semantic query (no filter)** .. code:: sql SELECT * FROM aidb.retrieve_text(public.pipeline_food_products, fast food, topk => 4); __OUTPUT__ key | value | distance | part_ids | intermediate_steps - ----+---------------+--------------------+----------+-------------------- 8 | Veggie Burger | 0.9955745765192575 | {0} | [] 6 | Pizza | 1.0280680840689795 | {0} | [] 4 | Fries | 1.0630385212203115 | {0} | [] 7 | Sandwich | 1.0903594734917037 | {0} | [] **Custom query with relational filter** .. code:: sql SET hnsw.iterative_scan = relaxed_order; SELECT vector.source_id, source.product_name, source.category FROM public.pipeline_food_products AS vector JOIN products AS source ON source.id::TEXT = vector.source_id WHERE source.category = vegetarian -- relational filter ORDER BY vector.value <=> aidb.kb_query_encode(public.pipeline_food_products, fast food) LIMIT 10; __OUTPUT__ source_id | product_name | category - ----------+---------------+------------ 8 | Veggie Burger | vegetarian 4 | Fries | vegetarian Only vegetarian items are returned, ordered by semantic relevance to “fast food”. -------------- Example 2: BM25 full-text search with RRF ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ This example shows how to combine semantic vector search with BM25 keyword search using Reciprocal Rank Fusion (RRF) to merge the two result sets. RRF re-ranks results by combining their positions in the semantic and keyword rankings, surfacing items that score well in both. This example builds on the same ``products`` table and ``food_products`` pipeline from Example 1. **Add a BM25 index to the source table** Enable the ``pg_tokenizer`` and ``vchord_bm25`` extensions: .. code:: sql CREATE EXTENSION IF NOT EXISTS pg_tokenizer; CREATE EXTENSION IF NOT EXISTS vchord_bm25; Create a tokenizer using the BERT model: .. code:: sql SELECT tokenizer_catalog.create_tokenizer(bert, $$ model = "bert_base_uncased" $$); Add a BM25 vector column to the source table and populate it with tokenized values: .. code:: sql ALTER TABLE products ADD COLUMN embedding_bm25 bm25_catalog.bm25vector; UPDATE products SET embedding_bm25 = tokenizer_catalog.tokenize(product_name, bert); Create a BM25 index on the new column: .. code:: sql CREATE INDEX products_bm25_idx ON products USING bm25 (embedding_bm25 bm25_catalog.bm25_ops); **Hybrid query with RRF** .. code:: sql SET hnsw.iterative_scan = relaxed_order; SET search_path TO public, tokenizer_catalog, bm25_catalog; WITH semantic AS ( SELECT source.id, source.product_name, source.category, ROW_NUMBER() OVER (ORDER BY vector.value <=> aidb.kb_query_encode(public.pipeline_food_products, fast food) AS rank FROM public.pipeline_food_products AS vector JOIN products AS source ON source.id::TEXT = vector.source_id ORDER BY vector.value <=> aidb.kb_query_encode(public.pipeline_food_products, fast food) LIMIT 20 ), keyword AS ( SELECT id, product_name, ROW_NUMBER() OVER (ORDER BY embedding_bm25 <&> to_bm25query(products_bm25_idx, tokenize(fast food, bert))) AS rank FROM products ORDER BY embedding_bm25 <&> to_bm25query(products_bm25_idx, tokenize(fast food, bert)) LIMIT 20 ) SELECT COALESCE(s.id, k.id) AS id, COALESCE(s.product_name, k.product_name) AS product_name, s.category, s.rank AS semantic_rank, k.rank AS keyword_rank, COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score FROM semantic s FULL OUTER JOIN keyword k ON s.id = k.id ORDER BY rrf_score DESC LIMIT 10; __OUPUT__ id | product_name | category | semantic_rank | keyword_rank | rrf_score - ---+---------------+------------+---------------+--------------+------------------------ 1 | Hamburger | meat | 6 | 1 | 0.03154495777446597119 4 | Fries | vegetarian | 3 | 4 | 0.03149801587301587302 6 | Pizza | meat | 2 | 6 | 0.03128054740957966765 8 | Veggie Burger | vegetarian | 1 | 8 | 0.03109932497589199614 3 | Fish n Chips | fish | 7 | 3 | 0.03079838900734423123 5 | Burrito | meat | 5 | 5 | 0.03076923076923076924 2 | Cheeseburger | meat | 9 | 2 | 0.03062178588125292193 7 | Sandwich | meat | 4 | 7 | 0.03055037313432835821 9 | Kebab | meat | 8 | 9 | 0.02919863597612958227 The RRF score combines both rankings: items that rank highly in either semantic or keyword search surface at the top, with items that rank well in both receiving the highest scores. -------------- Setting up user-managed BM25 ---------------------------- BM25 is not managed by AI pipelines. You set it up independently on your source table, then reference it in your hybrid queries. **1. Install extensions** .. code:: sql CREATE EXTENSION IF NOT EXISTS pg_tokenizer; CREATE EXTENSION IF NOT EXISTS vchord_bm25; **2. Create a tokenizer** .. code:: sql SELECT create_tokenizer(bert, $$ model = "bert_base_uncased" $$); **3. Add a ``bm25vector`` column and index to your source table** .. code:: sql ALTER TABLE my_source ADD COLUMN bm25_tokens bm25vector; UPDATE my_source SET bm25_tokens = tokenize(description, bert); CREATE INDEX my_source_bm25 ON my_source USING bm25 (bm25_tokens bm25_ops); **4. Keep the index current** New rows must be tokenized before they will appear in BM25 results. Use a trigger or update the column as part of your data ingestion process: .. code:: sql UPDATE my_source SET bm25_tokens = tokenize(description, bert) WHERE bm25_tokens IS NULL; For full tokenizer configuration options, see :ref:`VectorChord-BM25 ` and :ref:`VectorChord-BM25 in pg_extensions ` . -------------- Volume source limitation ------------------------ Hybrid search with relational predicate filtering or BM25 is **not supported for volume-sourced knowledge bases**. Volume sources have no backing Postgres table to join against, so there is no source table to filter or index. Volume-sourced knowledge bases continue to support standard semantic search via ``aidb.retrieve_text()`` and ``aidb.retrieve_key()`` .