Hybrid Search - Reranking Full-Text and Vector Search Results

Example 1 - Hybrid Search of Wikipedia Articles

This example demonstrates indexed hybrid search over a dataset of Wikipedia articles using full-text search and vector search.

SingleStore has created a dataset of 160M vectors and associated paragraphs to simulate vector-based semantic search over all 6.7 million articles in Wikipedia. The video game subset used in this example has real data, with approximately 41,000 vectors from 1,800 articles. The remaining data is sample data. The full dataset, including how to load and generate the vectors, is described in the blog post on ANN search. The dataset is available under the Creative Commons Attribution-ShareAlike License 4.0.

Create a table to store the vectors and paragraphs.

CREATE TABLE vecs(
id BIGINT(20),
     url TEXT DEFAULT NULL,
     paragraph TEXT DEFAULT NULL,
     v VECTOR(1536) NOT NULL,
     SHARD KEY(id), KEY(id) USING HASH
);

Load data into this table using the following pipeline.

-- since the bucket is open, you can leave the credentials clause as-is
CREATE OR REPLACE PIPELINE wiki_pipeline AS
load data S3
's3://singlestore-docs-example-datasets/wikipedia-video-game-data/video-game-embeddings.csv'
config '{"region":"us-east-1"}'
credentials '{"aws_access_key_id": "", "aws_secret_access_key": ""}'
SKIP DUPLICATE KEY ERRORS
INTO TABLE vecs
FORMAT csv
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

START PIPELINE wiki_pipeline FOREGROUND;

Add a full-text index and a vector index on the table. The vector index is optional – vector similarity queries work with or without indexes. Creating the indexes after loading data is more efficient  than creating them before loading data.

ALTER TABLE vecs ADD FULLTEXT ft_para(paragraph);

ALTER TABLE vecs ADD VECTOR INDEX ivf_v(v)
    INDEX_OPTIONS '{"index_type":"IVF_FLAT"}';

Run OPTIMIZE TABLE to ensure the values are indexed.

OPTIMIZE TABLE vecs FLUSH;

The following query searches for rows with information about Super Mario Kart using full-text and vector search:

• Full-text search finds paragraphs that match the string "super mario kart".

• Vector search finds paragraphs semantically similar to the introduction of the Super Mario Kart Wikipedia page by comparing embedding vectors.

The variable are set prior to defining the query.

• @v_mario_kart stores the vector embedding for the Super Mario Kart Wikipedia page.

At a high-level, the query is structured as follows:

• Full-text search and vector search retrieve candidate results separately using their respective indexes.

• Each result set is ranked independently.

• The rankings are combined using the RRF formula, and the top five results are returned.

The query uses CTEs to create intermediate result sets for each stage.

SET @v_mario_kart = (SELECT v FROM vecs
          WHERE URL = "https://en.wikipedia.org/wiki/Super_Mario_Kart"
          ORDER BY id LIMIT 1);

WITH fts AS ( /* Find top full-text matches. */
  SELECT
    id,
    paragraph,
    MATCH(paragraph) AGAINST ("super mario kart") AS SCORE
  FROM vecs
    WHERE MATCH(paragraph) AGAINST ("super mario kart")
 ORDER BY SCORE desc
  LIMIT 200
),
vs AS ( /* Find top vector search matches. <*> is dot product. */
  SELECT
    id,
    paragraph,
    v <*> @v_mario_kart AS SCORE
  FROM vecs
  ORDER BY score DESC
  LIMIT 200
),
fts_ranked as (
  SELECT
    *,
    RANK() OVER (ORDER BY SCORE DESC) AS fts_rank
  FROM fts
),
vs_ranked as (
  SELECT
    *,
    RANK() OVER (ORDER BY SCORE DESC) as vs_rank
  FROM vs
)
SELECT
  *,
  0.7 * (1.0 / (NVL(fts_rank, 1000.0) + 60)) + 0.3 * (1.0 / (NVL(vs_rank, 1000.0) + 60)) as combined_score
FROM fts_ranked f FULL OUTER JOIN vs_ranked v ON f.id = v.id
ORDER BY combined_score DESC
LIMIT 5;

In more detail, this query works as follows:

• The first two CTEs in the query (fts and vs) create ordered lists of 200 rows that best match the full-text and vector search criteria.

  • The full-text search block (fts) uses inverted index search based on Java Lucene.

  • The vector search block (vs) uses approximate nearest neighbor (ANN) indexing.

• The next two CTEs use the RANK window function to create ranked lists (fts_ranked and vs_ranked) of these 200 rows.

  • The ranked lists are created in separate CTEs to allow the initial CTEs to use indexed search.

  • If the RANK calculation were performed directly inside the initial fts and vs, it would prevent those CTEs from using index search.

• The final query block combines the ranked result sets:

  • A FULL OUTER JOIN includes all items appearing in either ranked list. Note that a standard INNER JOIN would discard items that do not appear in both lists.

  • The SELECT clause calculates the final combined_score using RRF and returns the five highest-scoring results.

This query uses a smoothing factor of 60 and weights full-text results at 0.7 and vector search results at 0.3. Any NULL ranks, which can arise from the outer join, are given an artificially high rank of 1000 so they don't contribute meaningfully to the score.

RRF allows strong performance in one search method to offset weaker performance in the other. For example, a result with rank pair (2, 1) receives a strong combined score because it ranks highly in both lists. With the weights used in this query, a result with rank pair (3, 11) still ranks higher overall than one with (5, 8) because the full-text score is weighted more heavily (0.7 versus 0.3).

Example 2 - Hybrid Search of Comments

This example combines hybrid search with a SQL filter. The query retrieves candidate results using full-text search and vector search, restricts both searches to comments in the "Food" category, and then combines the rankings using RRF.

Consider a table of comments that contains:

• id – an INT id

• comment – a TEXT field that stores the text of the comment

• comment_embedding – a VECTOR that stores an embedding capturing the meaning of the comment. as described in Working with Vector Data

• category – a VARCHAR that stores a category assigned to the comment

Create the comments table and insert insert sample data.

CREATE TABLE comments(id INT,
   comment TEXT,
   comment_embedding VECTOR(4) NOT NULL,
   category VARCHAR(256));

INSERT INTO comments VALUES
(1, "The cafeteria in building 35 has a great salad bar", 
    '[0.45, 0.55, 0.495, 0.5]',
    "Food"),
(2, "I love the taco bar in the B16 cafeteria.",
    '[0.01111, 0.01111, 0.1, 0.999]',
    "Food"),
(3, "The B24 restaurant salad bar is quite good.",
    '[0.1, 0.8, 0.2, 0.555]',
    "Food");

Add a full-text index to the table and run OPTIMIZE TABLE to ensure the values are indexed.

ALTER TABLE comments ADD FULLTEXT ft_comment(comment);

OPTIMIZE TABLE comments FLUSH;

The following SQL query searches for comments that match the word "restaurant" and are similar to a query vector ('[0.44, 0.554, 0.34, 0.62]') which is intended to represent an embedding of the user's request. In addition, search is restricted to comments in the category "Food".

The following variable are set prior to defining the query.

• @query_vec stores the vector embedding from the user's request.

Similar to Example 1, the query is structured as follows:

• Full-text search and vector search retrieve candidate comments separately using their respective indexes.

• The search is restricted to comments in the "Food" category.

• Each result set is ranked independently.

• The rankings are combined using the RRF formula, and the top three results are returned.

The @query_vec variable is cast to VECTOR to ensure that @query_vec is a valid VECTOR and to improve performance.

SET @query_vec = ('[0.44, 0.554, 0.34, 0.62]'):>VECTOR(4);

WITH fts AS ( /* Find top full-text matches. */
  SELECT
    id,
    comment,
    MATCH(comment) AGAINST ("restaurant") AS ft_score
  FROM comments
  WHERE MATCH(comment) AGAINST ("restaurant")
    AND category = "Food"
  ORDER BY ft_score desc
  LIMIT 200
),
vs AS ( /* Find top vector search matches. <*> is dot product. */
  SELECT
    id,
    comment,
    @query_vec <*> comment_embedding AS vec_score
  FROM comments
  WHERE category = "Food"
  ORDER BY vec_score desc
  LIMIT 200),
fts_ranked as (
  SELECT
    *,
    RANK() OVER (ORDER BY ft_score DESC) AS fts_rank
  FROM fts
),
vs_ranked as (
  SELECT
    *,
    RANK() OVER (ORDER BY vec_score DESC) as vs_rank
  FROM vs
)
SELECT
  *,
  0.7 * (1.0 / (NVL(fts_rank, 1000.0) + 60)) + 0.3 * (1.0 / (NVL(vs_rank, 1000.0) + 60)) AS combined_score
FROM fts_ranked f FULL OUTER JOIN vs_ranked v ON f.id = v.id
ORDER BY combined_score DESC
LIMIT 3;

*** 1. row ***
            id: 3
       comment: The B24 restaurant salad bar is quite good.
      ft_score: 0.46706151962280273
      fts_rank: 1
            id: 3
       comment: The B24 restaurant salad bar is quite good.
     vec_score: 0.8993000388145447
       vs_rank: 2
combined_score: 0.016314
*** 2. row ***
            id: NULL
       comment: NULL
      ft_score: NULL
      fts_rank: NULL
            id: 1
       comment: The cafeteria in building 35 has a great salad bar
     vec_score: 0.9810000061988831
       vs_rank: 1
combined_score: 0.005578
*** 3. row ***
            id: NULL
       comment: NULL
      ft_score: NULL
      fts_rank: NULL
            id: 2
       comment: I love the taco bar in the B16 cafeteria.
     vec_score: 0.6644233465194702
       vs_rank: 3
combined_score: 0.005422

In the results, the row with id = 3 has the word "restaurant", so it receives a strong full-text ranking. Because full-text search is weighted more heavily than vector search in this example (0.7 vs. 0.3), this higher full-text ranking contributes more to the final RRF score. If only vector search were used, the comment with id = 1 would rank higher since it has the highest vector similarity score.