AnalyticDB:Dual-source recall with vector search and full-text search

Category 1: brute-force query

The optimizer fetches all rows matching the structured conditions, sorts them by vector distance, and returns the closest 100. This guarantees 100% recall but is slow when the structured conditions match a large number of rows.

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: the filter condition for the price column.
                         Filter: the filter condition for the time column.

 Optimizer: Postgres query optimizer

Category 2: pure vector search and structured filtering

The optimizer queries the vector index for the N most similar rows, then filters by the structured conditions. This is fast, but when structured conditions are highly selective, the final result may contain fewer rows than requested.

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: the filter condition for the price and time columns.

 Optimizer: Postgres query optimizer

Category 3: hybrid search (vector + structured indexes)

This plan combines both approaches. When structured condition columns are indexed and their index type supports bitmaps, the optimizer generates a bitmap from the structured index and pushes it into the vector index scan. This accelerates filtering while preserving recall.

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: the filter condition for the price column.
                     ->  Ann Index Scan with filter using products_feature_idx on products
                         ORDER BY: (feature <-> $0)
                         Filter: the filter condition for the time column.

 Optimizer: Postgres query optimizer

Two execution plan nodes indicate that hybrid search is in use:

• Ann Index Scan with filter: pushes filter conditions down to the vector index, combining filtering with the index scan.

• Fusion Ann Scan: used when structured condition columns have a supported index. It generates a bitmap from the structured index (left subtree) and pushes the bitmap into the vector index scan (right subtree). The right subtree is either Ann Index Scan (bitmap only) or Ann Index Scan with filter (bitmap plus additional filter conditions).

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for PostgreSQL V6.0 or V7.0 instance

• The permission to create tables and indexes

Set up semi-structured field filtering

1. Create the table.

   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);

2. Set the storage mode of the vector column to PLAIN.

   ALTER TABLE stock_analysis_chunks ALTER COLUMN feature SET STORAGE PLAIN;

3. Create a vector index.

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

4. Create indexes for the structured and semi-structured columns.

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

5. Collect statistics.

   ANALYZE stock_analysis_chunks;

6. Run a hybrid search query. The following query returns stock analysis articles published between July 18 and August 18, 2023, that mention all four specified stocks, ranked by semantic similarity to a query vector:

   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;

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for PostgreSQL V6.0 or V7.0 instance

• The permission to create tables, indexes, and functions

Set up two-way retrieval

1. Create the table. The to_tsvector column stores precomputed full-text search tokens.

   Populate the to_tsvector column when inserting rows using PostgreSQL's to_tsvector() function, for example: to_tsvector('english', text). Keep this column in sync with the text column when updating rows.

   -- vector: the vector column. to_tsvector: the full-text tokenizer column.
   CREATE TABLE IF NOT EXISTS documents(
                   id TEXT,
                   docname TEXT,
                   title TEXT,
                   vector real[],
                   text TEXT,
                   to_tsvector TSVECTOR);

2. Set the storage mode of the vector column to PLAIN.

   ALTER TABLE documents ALTER COLUMN vector SET STORAGE PLAIN;

3. Create indexes.

   • Create a vector index for the vector column.

     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 tokenizer column.

     CREATE INDEX ON documents USING gin(to_tsvector);

4. Create a helper function to convert query text into a tsquery.

   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$

5. Run a two-way retrieval query. The query uses a common table expression (CTE) to run both searches and merge results. The first branch retrieves the top 10 rows by vector similarity; the second retrieves the top 10 rows by full-text relevance. UNION ALL combines both sets, and the outer SELECT deduplicates by id, taking the higher similarity score for any row found by both methods.

   Result columns

   Column	Description
   similarity	The higher score from either the vector or full-text search for this row
   source	A bitmask indicating which method(s) found the row: 1 = vector only, 2 = full-text only, 3 = both

   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 query returns at most 20 rows (10 from vector search + 10 from full-text search, deduplicated by id).

   Tuning the balance between vector and full-text search

   By default, the query gives equal weight to vector similarity scores and full-text relevance scores. To favor one method over the other, multiply the similarity value in either branch before the UNION ALL. For example, to double the weight of full-text results, replace ts_rank(...) AS similarity with ts_rank(...) * 2 AS similarity in the second branch.