In OceanBase AI Database, you can use the Vector Index Hint to specify which vector index to use during a query and to set pre- and post-filter conditions.
Hint name |
Explanation |
|---|---|
VECTOR_INDEX |
Specify the vector index to use for the query, along with the preceding and following filter conditions. |
Syntax
/*+ VECTOR_INDEX (table_name [, index_name] [, {'pre_filter' | 'post_filter'}]) */
The parameters are described as follows:
table_name: The name of the primary table.index_name: The name of the vector index to use. This parameter is optional. If you do not specify it, the optimizer automatically selects an index.- Filter conditions that can be specified. If not specified, the optimizer will choose the filtering method:
pre_filter: used to filter data before a vector index query.post_filter: used to filter results after a vector index query.
Examples
Create a test table named
tand insert test data into it:CREATE TABLE t(c1 INT, c2 INT, c3 INT); INSERT INTO t VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);Create a vector index named
vec_idx1.CREATE VECTOR INDEX vec_idx1 ON t(c2) WITH (distance=l2, type=hnsw);Use the
VECTOR_INDEXhint to specify that the vector indexvec_idx1be used for the query and a post-filter condition be applied.EXPLAIN SELECT /*+VECTOR_INDEX(t vec_idx1 post_filter)*/ * FROM t WHERE c1 > 1 AND c2 > 2 ORDER BY l2_distance(cv, '[1,2,3]') APPROX LIMIT 1;The return result is as follows, where
VECTOR INDEX ADAPTIVE SCAN (POST-ITERATIVE-FILTER)indicates that the vector indexvec_idx1is used for the query, and a post-filter condition is applied:Query Plan ========================================================================================= |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ----------------------------------------------------------------------------------------- |0 |VECTOR INDEX ADAPTIVE SCAN (POST-ITERATIVE-FILTER)|t(vec_idx1)|2 |15 | ========================================================================================= Outputs & filters: ------------------------------------- 0 - output([t.c1], [t.c2], [t.cv]), filter([t.c1 > 1], [t.c2 > 2]), rowset=16 access([t.__pk_increment], [t.cv], [t.c1], [t.c2]), partitions(p0) is_index_back=true, is_global_index=false, filter_before_indexback[true,false], range_key([t.c1], [t.__pk_increment]), range(1,MAX ; MAX,MAX)
Usage with the INDEX hint
This section briefly describes the behavior rules when the vector index hint (VECTOR_INDEX) is used together with the normal index hint (INDEX) and provides guidance on optimizing the application of these hints:
- Specified with INDEX hint only (normal index): The pre-filter is preferred.
- Specify only the INDEX hint (vector index): Prefer post filtering and ensure the specified vector index is hit.
- Specifying only the VECTOR_INDEX hint: In this case, the optimizer selects the specific index and filtering method based on the hint configuration.
- When both the INDEX hint and VECTOR_INDEX hint are specified, the system prioritizes the VECTOR_INDEX hint. If multiple VECTOR_INDEX hints exist, one is randomly selected to take effect.
The following examples illustrate the behavior of the VECTOR_INDEX hint in combination with the INDEX hint.
The sample table structure is as follows:
CREATE TABLE t(c1 INT, c2 INT, cv VECTOR(3));
CREATE INDEX idx1 ON t(c1);
CREATE INDEX idx2 ON t(c2);
CREATE VECTOR INDEX vec_idx1 ON t(cv) WITH (type=hnsw, distance=l2);
CREATE VECTOR INDEX vec_idx2 ON t(cv) WITH (type=hnsw_bq, distance=l2);
CREATE VECTOR INDEX vec_idx3 ON t(cv) WITH (type=hnsw, distance=cosine);
The following table describes common use cases:
Specify only the INDEX hint (normal index)
SELECT /*+INDEX(t idx1)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In this example, pre-filtering is used first.
Specify only the INDEX hint (vector index)
SELECT /*+INDEX(t vec_idx2)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In this example, post filtering is prioritized, and the specified vector index
vec_idx2is matched.Specify only the VECTOR_INDEX hint (pre_filter)
SELECT /*+VECTOR_INDEX(t vec_idx1 pre_filter)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In the preceding example, the pre-filter using vector index
vec_idx1is specified. If no specific index for pre-filtering is specified, the optimizer automatically selects one from the available indexes on the relevant columns (for example, if there are indexes on bothc1andc2, either could be used for pre-filtering).Specify both the INDEX hint (for a normal index) and the VECTOR_INDEX hint (for pre_filter)
SELECT /*+INDEX(t idx1) VECTOR_INDEX(t vec_idx1 pre_filter)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In this example, vector index-based pre-filtering is prioritized. Specifically, the vector index
vec_idx1and the regular indexidx1are specified to handle the pre-filtering.Specify both the INDEX hint (for a normal index) and the VECTOR_INDEX hint (for post_filter).
SELECT /*+INDEX(t idx1) VECTOR_INDEX(t vec_idx1 post_filter)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In this example, the post-filtering of vector index
vec_idx1is prioritized. Note that in the current version, post-filtering does not support being used together with regular indexes. Therefore, even ifindex(t idx1)is specified, this hint will not take effect. Ultimately, only the post-filtering of vector indexvec_idx1specified by the VECTOR_INDEX hint will be applied.Specify both the INDEX hint (for vector indexes) and VECTOR_INDEX hint (for pre_filter)
SELECT /*+INDEX(t vec_idx2) VECTOR_INDEX(t vec_idx1 pre_filter)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In this example, the VECTOR_INDEX hint takes effect first, directing the query to perform a pre-filtering operation using
vec_idx1. The INDEX hint (index(t vec_idx2)) does not take effect.The vector index distance algorithm specified by the VECTOR_INDEX hint does not match the query algorithm.
SELECT /*+INDEX(t vec_idx2) VECTOR_INDEX(t vec_idx3 pre_filter)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;In this example, the VECTOR_INDEX hint is invalid. If there are other index hints, the selection follows the rules described above. From the sample table structure above, you can see that the distance algorithm for
vec_idx3is cosine, while the query specifies l2. Since this hint is invalid, the filtering rule afterindex(t vec_idx2)takes effect.Specify multiple VECTOR_INDEX hints with the same distance algorithm
SELECT /*+VECTOR_INDEX(t vec_idx1 pre_filter) VECTOR_INDEX(t vec_idx2 post_filter)*/ * FROM t ORDER BY l2_distance(cv, @vec) WHERE c1 < 1 AND c2 < 1 APPROX LIMIT 6;If the distance algorithms are all the same, the optimizer will choose one to take effect, but does not guarantee the order or which one will be specified.
Using these rules, you can flexibly combine VECTOR_INDEX and INDEX hints to achieve optimal synergy between vector indexes and regular indexes.