Vector search

Approximate nearest neighbor search

Apache Doris has officially supported ANN search since version 4.0. The system does not introduce a new data type for vectors; they are stored as fixed-length arrays. To accelerate vector distance retrieval, Doris implements a new ANN index type based on Faiss. The following example uses the common SIFT dataset to demonstrate how to create a table with an ANN index:

CREATE TABLE sift_1M (
  id int NOT NULL,
  embedding array<float>  NOT NULL  COMMENT "",
  INDEX ann_index (embedding) USING ANN PROPERTIES(
      "index_type"="hnsw",
      "metric_type"="l2_distance",
      "dim"="128",
      "quantizer"="flat"
  )
) ENGINE=OLAP
DUPLICATE KEY(id) COMMENT "OLAP"
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
  "replication_num" = "1"
);

• index_type: hnsw specifies the use of the Hierarchical Navigable Small World algorithm.

• metric_type: l2_distance specifies L2 distance as the distance function.

• dim: 128 indicates that the vector dimension is 128.

• quantizer: flat means that each dimension is stored in its original float32 format.

Import data using S3 TVF:

INSERT INTO sift_1M
SELECT *
FROM S3(
  "uri" = "https://selectdb-customers-tools-bj.oss-cn-beijing.aliyuncs.com/sift_database.tsv",
  "format" = "csv");

select count(*) from sift_1M
--------------

+----------+
| count(*) |
+----------+
|  1000000 |
+----------+

Using l2_distance_approximate or inner_product_approximate triggers the ANN index path. The function name must exactly match the index's metric_type (for example, metric_type='l2_distance' requires using l2_distance_approximate; metric_type='inner_product' requires using inner_product_approximate). For sorting, use ascending order for L2 distance (smaller is closer) and descending order for inner product (larger is closer).

SELECT id,
       l2_distance_approximate(
        embedding,
        [0,11,77,24,3,0,0,0,28,70,125,8,0,0,0,0,44,35,50,45,9,0,0,0,4,0,4,56,18,0,3,9,16,17,59,10,10,8,57,57,100,105,125,41,1,0,6,92,8,14,73,125,29,7,0,5,0,0,8,124,66,6,3,1,63,5,0,1,49,32,17,35,125,21,0,3,2,12,6,109,21,0,0,35,74,125,14,23,0,0,6,50,25,70,64,7,59,18,7,16,22,5,0,1,125,23,1,0,7,30,14,32,4,0,2,2,59,125,19,4,0,0,2,1,6,53,33,2]
       ) AS distance
FROM sift_1M
ORDER BY distance
LIMIT 10;
--------------

+--------+----------+
| id     | distance |
+--------+----------+
| 178811 | 210.1595 |
| 177646 | 217.0161 |
| 181997 | 218.5406 |
| 181605 | 219.2989 |
| 821938 | 221.7228 |
| 807785 | 226.7135 |
| 716433 | 227.3148 |
| 358802 | 230.7314 |
| 803100 | 230.9112 |
| 866737 | 231.6441 |
+--------+----------+
10 rows in set (0.02 sec)

To compare with the exact results from a brute-force calculation, use l2_distance or inner_product (without the _approximate suffix). In this example, the exact search takes about 290 ms:

10 rows in set (0.29 sec)

Using the ANN index reduces query latency from about 290 ms to about 20 ms.

The ANN index is built at the segment level. In a distributed table, each segment returns its local top-N results. The top-N operator then merges these results across tablets and segments to produce the global top-N.

Sorting rules:

• For metric_type = l2_distance, a smaller distance means the vectors are closer. Use ORDER BY dist ASC.

• For metric_type = inner_product, a larger value means the vectors are closer. Use ORDER BY dist DESC to get the top-N results using the index.

Approximate range search

Unlike top-N search, a range search returns all data points whose distance from the target vector meets a specified condition (greater or less than a threshold), rather than a fixed number of results. This is useful when you need candidates that are "similar enough" or "dissimilar enough"—for example, retrieving diverse content in recommendation systems or locating outliers in anomaly detection.

A typical SQL query looks like this:

SELECT count(*)
FROM   sift_1M
WHERE  l2_distance_approximate(
        embedding,
        [0,11,77,24,3,0,0,0,28,70,125,8,0,0,0,0,44,35,50,45,9,0,0,0,4,0,4,56,18,0,3,9,16,17,59,10,10,8,57,57,100,105,125,41,1,0,6,92,8,14,73,125,29,7,0,5,0,0,8,124,66,6,3,1,63,5,0,1,49,32,17,35,125,21,0,3,2,12,6,109,21,0,0,35,74,125,14,23,0,0,6,50,25,70,64,7,59,18,7,16,22,5,0,1,125,23,1,0,7,30,14,32,4,0,2,2,59,125,19,4,0,0,2,1,6,53,33,2])
        > 300 
--------------

+----------+
| count(*) |
+----------+
|   999271 |
+----------+
1 row in set (0.19 sec)

Range-based vector search in Doris is also accelerated by the ANN index. The index quickly filters candidate vectors before calculating approximate distances, reducing computational overhead. The supported range conditions include >, >=, <, <=.

Compound search

A compound search performs an ANN top-N search and a range condition filter in the same SQL query, returning the top-N results that also satisfy the range constraint.

SELECT id,
       l2_distance_approximate(
        embedding, [0,11,77,24,3,0,0,0,28,70,125,8,0,0,0,0,44,35,50,45,9,0,0,0,4,0,4,56,18,0,3,9,16,17,59,10,10,8,57,57,100,105,125,41,1,0,6,92,8,14,73,125,29,7,0,5,0,0,8,124,66,6,3,1,63,5,0,1,49,32,17,35,125,21,0,3,2,12,6,109,21,0,0,35,74,125,14,23,0,0,6,50,25,70,64,7,59,18,7,16,22,5,0,1,125,23,1,0,7,30,14,32,4,0,2,2,59,125,19,4,0,0,2,1,6,53,33,2]) as dist
FROM sift_1M
WHERE l2_distance_approximate(
        embedding, [0,11,77,24,3,0,0,0,28,70,125,8,0,0,0,0,44,35,50,45,9,0,0,0,4,0,4,56,18,0,3,9,16,17,59,10,10,8,57,57,100,105,125,41,1,0,6,92,8,14,73,125,29,7,0,5,0,0,8,124,66,6,3,1,63,5,0,1,49,32,17,35,125,21,0,3,2,12,6,109,21,0,0,35,74,125,14,23,0,0,6,50,25,70,64,7,59,18,7,16,22,5,0,1,125,23,1,0,7,30,14,32,4,0,2,2,59,125,19,4,0,0,2,1,6,53,33,2])
        > 300
ORDER BY dist limit 10
--------------

+--------+----------+
| id     | dist     |
+--------+----------+
| 243590 |  300.005 |
| 549298 | 300.0317 |
| 429685 | 300.0533 |
| 690172 | 300.0916 |
| 123410 | 300.1333 |
| 232540 | 300.1649 |
| 547696 | 300.2066 |
| 855437 | 300.2782 |
| 589017 | 300.3048 |
| 930696 | 300.3381 |
+--------+----------+
10 rows in set (0.12 sec)

A key factor in compound search is the execution order of the predicate filter and the top-N search. Pre-filtering applies the filter first and then finds the top-N results; post-filtering does the reverse. Post-filtering is typically faster but can reduce recall, so Doris adopts pre-filtering. The index can accelerate both stages, but using it for both may lower recall when the initial range filter is highly selective. Doris adaptively decides whether to use the index for both stages based on the predicate filtering rate and the index type.

Filtered ANN search

A filtered ANN search applies other filter predicates before performing the ANN top-N search, returning the top-N results that satisfy the specified conditions. The following example with 8-dimensional vectors illustrates this process.

CREATE TABLE ann_with_fulltext (
  id int NOT NULL,
  embedding array<float> NOT NULL,
  comment String NOT NULL,
  value int NULL,
  INDEX idx_comment(`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment',
  INDEX ann_embedding(`embedding`) USING ANN PROPERTIES("index_type"="hnsw","metric_type"="l2_distance","dim"="8")
) DUPLICATE KEY (`id`) 
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES("replication_num"="1");

INSERT INTO ann_with_fulltext VALUES
(1, [0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8], 'this is about music', 10),
(2, [0.2,0.1,0.5,0.3,0.9,0.4,0.7,0.1], 'sports news today',   20),
(3, [0.9,0.8,0.7,0.6,0.5,0.4,0.3,0.2], 'latest music trend',  30),
(4, [0.05,0.06,0.07,0.08,0.09,0.1,0.2,0.3], 'politics update',40)

Suppose a user provides the query vector [0.1,0.1,0.2,0.2,0.3,0.3,0.4,0.4] and wants to retrieve the top 2 most similar documents only from those whose comment field contains "music":

SELECT id, comment,
       l2_distance_approximate(embedding, [0.1,0.1,0.2,0.2,0.3,0.3,0.4,0.4]) AS dist
FROM ann_with_fulltext
WHERE comment MATCH_ANY 'music'       -- First, filter using the inverted index
ORDER BY dist ASC                     -- Then, perform an ANN TopN search on the filtered result set
LIMIT 2;

+------+---------------------+----------+
| id   | comment             | dist     |
+------+---------------------+----------+
|    1 | this is about music | 0.663325 |
|    3 | latest music trend  | 1.280625 |
+------+---------------------+----------+
2 rows in set (0.04 sec)

To accelerate a filtered ANN search with a vector index, ensure that the columns used in the filter predicates have a secondary index, such as an inverted index.

Query parameters

You can also adjust HNSW index behavior at query time through session variables.

• hnsw_ef_search: The ef_search parameter for the HNSW index. It controls the maximum length of the candidate queue during the search phase. A larger ef_search value improves search accuracy at the cost of higher latency. The default value is 32.

• hnsw_check_relative_distance: Whether to enable the relative distance check mechanism to improve HNSW search accuracy. The default is true.

• hnsw_bounded_queue: Whether to use a bounded priority queue to optimize HNSW search performance. The default is true.

Vector quantization

With FLAT encoding, the HNSW index (original vectors plus graph structure) can consume a large amount of memory. HNSW must be fully loaded into memory to function, which can become a bottleneck on very large datasets. Scalar quantization (SQ) reduces memory by compressing FLOAT32 values, while product quantization (PQ) decomposes high-dimensional vectors into sub-vectors and quantizes them separately. Doris supports two scalar quantization levels: INT8 and INT4 (SQ8 and SQ4). Here is an example using SQ8:

CREATE TABLE sift_1M (
  id int NOT NULL,
  embedding array<float>  NOT NULL  COMMENT "",
  INDEX ann_index (embedding) USING ANN PROPERTIES(
      "index_type"="hnsw",
      "metric_type"="l2_distance",
      "dim"="128",
      "quantizer"="sq8"    -- Use INT8 for quantization
  )
) ENGINE=OLAP
DUPLICATE KEY(id) COMMENT "OLAP"
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
  "replication_num" = "1"
);

In tests with the 768-dimensional Cohere-MEDIUM-1M and Cohere-LARGE-10M datasets, SQ8 can compress the index size to about 1/3 of the size of a FLAT index.

Quantization adds extra overhead to the build process because the build phase requires many distance calculations, each of which must decode the quantized values. For a 128-dimensional vector, the build time increases with the number of rows. SQ can introduce approximately 10 times the build cost compared to FLAT.

Doris also supports product quantization, which requires additional parameters:

• pq_m: Specifies the number of sub-vectors to split the original high-dimensional vector into. The vector dimension (dim) must be divisible by pq_m.

• pq_nbits: Specifies the number of bits to quantize each sub-vector. This determines the size of the codebook for each subspace (k = 2 ^ pq_nbits). In Faiss, the value of pq_nbits should generally be no more than 24.

Product quantization requires a minimum amount of training data. The number of training points must be greater than or equal to the number of cluster centers (n >= 2 ^ pq_nbits).

CREATE TABLE sift_1M (
  id int NOT NULL,
  embedding array<float>  NOT NULL  COMMENT "",
  INDEX ann_index (embedding) USING ANN PROPERTIES(
      "index_type"="hnsw",
      "metric_type"="l2_distance",
      "dim"="128",
      "quantizer"="pq",    -- Use PQ for quantization
      "pq_m"="2",          -- Required for PQ, splits the high-dim vector into pq_m low-dim sub-vectors
      "pq_nbits"="2"       -- Required for PQ, specifies the number of bits for each subspace codebook
  )
) ENGINE=OLAP
DUPLICATE KEY(id) COMMENT "OLAP"
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
  "replication_num" = "1"
);

Performance tuning

Vector search is a typical point query scenario that uses a secondary index. If your application requires high QPS and low latency, follow these recommendations. After tuning, on a machine with a 32-core 64 GB FE and a 32-core 64 GB BE, Doris can achieve over 3,000 QPS on the Cohere-MEDIUM-1M dataset.

1. Enable server-side prepared statements in the JDBC URL.
url = jdbc:mysql://127.0.0.1:9030/demo?useServerPrepStmts=true
2. Use a prepared statement.
// Use '?' as a placeholder. The readStatement should be reused.

PreparedStatement readStatement = conn.prepareStatement("SELECT id, l2_distance_approximate(embedding, cast (? as ARRAY<FLOAT>)) AS distance
  FROM l2_distance_approximate
  ORDER BY distance
  LIMIT 10");
  
...

readStatement.setString("[0,11,77,24,3,0,0,0,28,70,125,8,0,0,0,0,44,35,50,45,9,0,0,0,4,0,4,56,18,0,3,9,16,17,59,10,10,8,57,57,100,105,125,41,1,0,6,92,8,14,73,125,29,7,0,5,0,0,8,124,66,6,3,1,63,5,0,1,49,32,17,35,125,21,0,3,2,12,6,109,21,0,0,35,74,125,14,23,0,0,6,50,25,70,64,7,59,18,7,16,22,5,0,1,125,23,1,0,7,30,14,32,4,0,2,2,59,125,19,4,0,0,2,1,6,53,33,2]");

ResultSet resultSet = readStatement.executeQuery();

Python SDK

Community members have contributed a Python SDK that makes it easy to use Doris vector search capabilities in Python.

• https://github.com/uchenily/doris_vector_search: This SDK is optimized for vector distance retrieval and is currently the best-performing Python SDK for Doris vector search available.

Limitations

1. Doris requires the column corresponding to the ANN index to be a NOT NULLABLE column of type Array<Float>. During data import, you must ensure that the length of every vector in this column equals the dimension (dim) specified in the index properties; otherwise, Doris reports an error.

2. The ANN index can only be used on tables with the DuplicateKey model.

3. Doris uses pre-filtering semantics, evaluating predicates before the ANN top-N calculation. To ensure correct results, if a predicate in the SQL query involves a column that does not have a secondary index, Doris falls back to brute-force calculation. For example:

SELECT id, l2_distance_approximate(embedding, [xxx]) AS distance
    FROM sift_1M
    WHERE round(id) > 100
    ORDER BY distance limit 10;

Although id is a primary key, this query will fall back to a brute-force calculation. This is because the round(id) > 100 predicate applies to a column that lacks a secondary index (like an inverted index) capable of precisely locating rows. To maintain the pre-filtering semantics for the ANN top-N search, the system defaults to a full scan.

4. If the distance function specified in the SQL query does not match the metric_type of the index in the DDL, Doris cannot use the ANN index to accelerate the top-N calculation, even if you use l2_distance_approximate or inner_product_approximate.

5. If the metric_type is inner_product, only a query with ORDER BY inner_product_approximate() DESC LIMIT N (the DESC keyword is mandatory) can be accelerated by the ANN index.

6. The xxx_approximate() function triggers an index scan only when its first argument is a column array and its second argument is a CAST or an array literal. If you swap their positions, the query falls back to a brute-force search.