Two-way retrieval based on vector search and full-text search-AnalyticDB(AnalyticDB)-阿里云帮助中心

When you need to search for structured and unstructured data at the same time, you can use the hybrid query feature of the AnalyticDB for PostgreSQL vector database. Hybrid query supports structured field filtering, semi-structured field filtering, and two-way retrieval together with full-text search on text fields.

This topic applies to two-way retrieval in AnalyticDB for PostgreSQL V6.0 and V7.0. However, for V7.0, we recommend that you view and use Two-way retrieval based on vector search and full-text search.

Introduction to hybrid search

Approximate nearest neighbor search (ANNS) vector indexes can handle only unstructured data retrieval. However, in actual production environments, you often need to search for structured and unstructured data at the same time. For example, you may need to query whether specific persons appeared in a specific area within a specified time range. The time range and area range are structured conditions, and human faces are unstructured conditions. Therefore, AnalyticDB for PostgreSQL provides the hybrid query capability for structured and unstructured data.

Most hybrid query solutions in the industry use two separate systems: structured data is stored in databases and unstructured data is stored in vector search systems. The results from the two systems are then intersected and aggregated to obtain the final result. Typically, this method first uses the vector search system to retrieve an amplified number of results (amplification factor × top K), and then filters them using structured indexes to obtain the final result. However, because structured index filtering may eliminate a large number of results, this approach can cause significant recall loss and fail to deliver the required top K results. The following section describes how hybrid query works by using an example with structured and unstructured fields.

How hybrid search works

AnalyticDB for PostgreSQL integrates the FastANN vector search engine into the database as an index plugin, allowing you to use structured and semi-structured indexes while performing vector search. Specifically, the optimizer uses ranked-biased overlap (RBO) cost estimation rules to generate different execution plans for searching structured and unstructured data at the same time.

The following example illustrates the principle of hybrid search.

Assume that you need to search for products by image with conditions: find the top 100 products that have the highest similarity to the input image, are priced between 100 and 200 CNY, and were listed within the last month.

The table and index structures are as follows:

CREATE TABLE products (
    id serial primary key,
    name varchar(256),
    price real,
    inTime timestamp,
    url varchar(256),
    feature real[]
);

-- Set the vector column to PLAIN storage mode.
ALTER TABLE products ALTER COLUMN feature SET STORAGE PLAIN;

-- Create a BTREE index for structured fields.
CREATE INDEX ON products(price, intime);

-- Create a vector index for the vector field.
CREATE INDEX ON products USING ann(feature) WITH (dim=512);

-- Collect statistics for generating the optimal hybrid query execution plan.
ANALYZE products;

The SQL statement for hybrid query is as follows:

SELECT id, price FROM products WHERE
    price > 100 AND price <=200
    AND inTime > '2019-03-01 00:00:00' AND inTime <= '2019-03-31 00:00:00'
ORDER BY
    feature <-> array[10,2.0,…, 512.0]
LIMIT 100;

To execute this SQL statement, based on whether indexes exist on the structured condition columns (price and inTime) and the selectivity of the structured conditions, AnalyticDB for PostgreSQL may generate the following three types of execution plans and select the optimal one to meet your performance and recall requirements:

Type 1: Brute-force query

The system retrieves all rows that match the structured conditions, sorts them by vector distance, and then outputs the 100 rows with the smallest distance. This execution plan achieves a 100% recall rate but has the slowest query speed. Query performance is poor when the dataset is large or when a large number of rows match the structured conditions. The execution plan is as follows (some output is simplified):

                                       QUERY PLAN
-----------------------------------------------------------------------------------
 LIMIT
     ->  Gather Motion 3:1  (slice1; segments: 3)
         Merge Key: (l2_squared_distance($0, feature))
             ->  LIMIT
                 ->  Sort
                     Sort Key: (l2_squared_distance($0, feature))
                     ->  Index Scan using products_price_idx on products
                         Index Cond: Price filter condition
                         Filter: Time filter condition

 Optimizer: Postgres query optimizer

Type 2: Pure vector search + structured filtering
To accelerate the query, the system first uses the vector index to retrieve the N rows most similar to the input image, and then filters the results based on the structured conditions. This execution plan provides the fastest query speed. However, if the selectivity of the structured query is too low (i.e., the probability of a row passing the filter is too small), the final output may contain fewer rows than requested. The execution plan is as follows (some output is simplified):
```
                                       QUERY PLAN
-----------------------------------------------------------------------------------
 LIMIT
    ->  Gather Motion 3:1  (slice1; segments: 3)
        Merge Key: ((feature <-> $0))
            ->  LIMIT
                ->  Ann Index Scan using products_feature_idx on products
                    ORDER BY: (feature <-> $0)
                    Filter: Price and time column filter conditions

 Optimizer: Postgres query optimizer
```
Type 3: Hybrid vector-structured query
The hybrid vector-structured query combines the advantages of Type 1 and Type 2 execution plans. It can use indexes and also addresses the issue of reduced result counts in Type 2. Additionally, if an index exists on the structured condition column and the index type supports Bitmap generation (left subtree), the hybrid query can leverage other indexes to generate Bitmaps, further accelerating the hybrid query. The execution plan is as follows (some output is simplified):
```
                                       QUERY PLAN
-----------------------------------------------------------------------------------
 LIMIT
     ->  Gather Motion 3:1  (slice1; segments: 3)
         Merge Key: ((feature <-> $0))
         ->  LIMIT
             ->  Fusion Ann Scan
                 ->  Bitmap Index Scan on products_price_idx
                     Index Cond: Price filter condition
                     ->  Ann Index Scan with filter using products_feature_idx on products
                         ORDER BY: (feature <-> $0)
                         Filter: Time filter condition

 Optimizer: Postgres query optimizer
```
In the preceding execution plans, any plan that contains a Fusion Ann Scan node or an Ann Index Scan with filter node uses the hybrid query capability. The two execution plans are described as follows:
- Ann Index Scan with filter: This node pushes filter conditions down into the vector index, so that filter conditions are considered during index execution.
- Fusion Ann Scan: This node may appear when specific structured condition columns have indexes. It generates a Bitmap based on structured conditions (left subtree) and pushes the Bitmap down into the vector index (right subtree) to accelerate structured condition evaluation. When a Fusion Ann Scan node appears, its right subtree may be either Ann Index Scan or Ann Index Scan with filter. If the right subtree is Ann Index Scan, it means no structured conditions other than the Bitmap are pushed down. Otherwise, Ann Index Scan with filter appears.

Hybrid search usage

The hybrid query feature of the AnalyticDB for PostgreSQL vector database supports structured field filtering, semi-structured field filtering, and two-way retrieval together with full-text search on text fields. Hybrid query can be divided into the following three types:

Hybrid query combining vector search and structured field filtering. Currently supported structured field types include: bigint, boolean, bytea, char, varchar, integer, float, double, date, smallint, timestamp, serial, and all other structured field types. These structured fields can be accelerated in hybrid queries by creating default BTREE indexes. For detailed information about structured field types, see Data types.
Hybrid query combining vector search and semi-structured field filtering. Currently supported semi-structured types include: JSON, JSONB, Array, and all other semi-structured field types. These semi-structured fields can be accelerated in hybrid queries by creating GIN indexes. For detailed information about semi-structured types, see JSON & JSONB data types and ARRAY type.
Two-way retrieval combining vector search and full-text search.For more information about full-text search, see How to use AnalyticDB for PostgreSQL for one-stop full-text search.

In How hybrid search works, the acceleration and optimization methods for structured field types in hybrid queries are described. The following examples demonstrate how to use semi-structured fields in hybrid search and how to perform two-way retrieval based on vector search and full-text search.

Use semi-structured fields in hybrid search

Assume that a securities company has a text database named stock_analysis_chunks for stock analysis articles. This database mainly includes the following fields:

Field	Type	Description
id	serial	The serial number.
chunk	varchar(1024)	The text chunk after splitting the stock analysis article.
release_time	timestamp	The publication time of the stock analysis article.
stock_id_list	char(10)[]	The list of company stock IDs involved in the stock analysis article.
url	varchar(1024)	The URL of the article to which the text chunk belongs.
feature	real[]	The embedding vector of the text chunk.

Create the text database table stock_analysis_chunks.

CREATE TABLE stock_analysis_chunks (
    id serial primary key,
    chunk varchar(1024),
    release_time timestamp,
    stock_id_list integer[],
    url varchar(1024),
    feature real[]
) distributed by (id);

Set the vector column to PLAIN storage mode.

ALTER TABLE stock_analysis_chunks ALTER COLUMN feature SET STORAGE PLAIN;

Create a vector index on the vector column.

CREATE INDEX ON stock_analysis_chunks USING ann(feature) WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1, hnsw_ef_construction=128);

Create indexes on the structured and semi-structured columns related to hybrid query.

-- Create a BTREE index for the structured field.
CREATE INDEX ON stock_analysis_chunks(release_time);
-- Create a GIN index for the semi-structured field.
CREATE INDEX ON stock_analysis_chunks USING gin(stock_id_list);

Collect statistics.
```
ANALYZE stock_analysis_chunks;
```

Perform hybrid query.

For example, to find stock analysis articles published within the last month that contain specific text content and involve specific companies, you can use the following SQL statement for hybrid query:

SELECT id, url, cosine_similarity(feature, array[1.0, 2.0, ..., 1536.0]::real[]) as score
FROM stock_analysis_chunks
WHERE release_time >= '2023-07-18 00:00:00' AND release_time <= '2023-08-18 00:00:00' AND stock_id_list @> ARRAY['BABA', 'AAPL', 'MSFT', 'AMZN']
ORDER BY feature <=> array[1.0, 2.0, ..., 1536.0]::real[]
LIMIT 10;

Two-way retrieval based on vector search and full-text search

Two-way retrieval refers to a strategy that uses both vector search and full-text search to retrieve data simultaneously. In most scenarios, using vector search alone can achieve a high recall rate for similarity retrieval. However, in certain cases, such as when the embedding model performs poorly or when complex queries cause the generated vectors to be far from the target data, vector similarity retrieval alone cannot achieve the desired results. In such cases, you can use two-way retrieval to enrich the retrieval strategy and improve the recall rate.

The two-way retrieval feature of AnalyticDB for PostgreSQL retrieves partial data through vector search and full-text search separately, and then performs precision ranking, distillation, and post-processing to achieve better retrieval results.

Procedure

Create a sample table.

-- The vector column is for vector data. to_tsvector is for full-text tokenization.
CREATE TABLE IF NOT EXISTS documents(
                id TEXT,
                docname TEXT,
                title TEXT,
                vector real[],
                text TEXT,
                to_tsvector TSVECTOR);

Set the vector column to PLAIN storage mode.

ALTER TABLE documents ALTER COLUMN vector SET STORAGE PLAIN;

Create indexes.

Create a vector index for the vector field.

CREATE INDEX ON documents USING ann(vector) WITH (dim=1024, distancemeasure=cosine, hnsw_m=64, pq_enable=1, hnsw_ef_construction=128);

Create a GIN index for the full-text tokenization field.
```
CREATE INDEX ON documents USING gin(to_tsvector);
```

Create a dependency function.

CREATE OR REPLACE FUNCTION public.to_tsquery_from_text(txt text, lang regconfig DEFAULT 'english'::regconfig)
 RETURNS tsquery
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$ SELECT to_tsquery(lang, COALESCE(string_agg(split_part(word, ':', 1), ' | '), '')) FROM (SELECT UNNEST(STRING_TO_ARRAY(TO_TSVECTOR(lang, txt)::text, ' ')) AS TEXT) AS words_only;$function$

Perform two-way retrieval query.

 WITH combined AS (
   (
     SELECT
        id,
        docname,
        text,
        cosine_similarity(densevec,ARRAY{embedding}::real[]) AS similarity,
        1 AS source
     FROM
        documents
     ORDER BY vector <=> ARRAY[10,2.0,…, 1024.0]
     LIMIT 10
   )
   UNION ALL
   (
     SELECT
        id,
        docname,
        text,
        ts_rank(to_tsvector, to_tsquery_from_text('{query.query}', 'zh_cn'), 32) AS similarity,
        2 AS source
     FROM
        documents
     WHERE to_tsvector@@to_tsquery_from_text('{query.query}', 'zh_cn')
     ORDER BY similarity DESC
     LIMIT 10
   )
)
SELECT id, docname, title, text, MAX(similarity) AS similarity,
                BIT_OR(source) AS source
                FROM combined
                GROUP BY id
                ORDER BY similarity DESC;

The first part of the UNION retrieves 10 results through vector search, and the second part retrieves 10 results through full-text search. The results are then deduplicated by id. The preceding two-way retrieval SQL statement returns a result set of up to 20 rows.