IVF Vector Query with Rank Options¶
Overview¶
MatrixOne provides the BY RANK WITH OPTION clause to select a vector ranking path. The modes differ in whether they use an IVF index; inspect EXPLAIN for the actual plan.
Syntax¶
SELECT column_list
FROM table_name
ORDER BY distance_function(vector_column, query_vector) ASC
LIMIT k
BY RANK WITH OPTION 'mode = <mode>';
Parameters¶
Parameter |
Description |
|---|---|
|
Selects the ranking path. Values include |
Mode Options¶
mode = pre (Pre-ranking Mode)¶
In pre-ranking mode, the IVF index is used to filter candidate vectors before applying the rank function. This mode:
Behavior: First uses IVF index to select candidate centroids, then applies ranking within those candidates
Performance: Fastest execution, as ranking is applied only to a subset of data
Accuracy: May miss some relevant results if they are in centroids not selected by the probe limit
Use Case: High-throughput scenarios where query speed is prioritized over perfect recall.
-- Example: Pre-ranking mode
SELECT id, content, l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') AS distance
FROM documents
ORDER BY l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') ASC
LIMIT 10
BY RANK WITH OPTION 'mode = pre';
mode = force (Force Mode)¶
In the verified v4.2.5 behavior, force uses a scalar table scan and distance sort, not an IVF index scan. EXPLAIN shows Table Scan + Sort(l2_distance). This mode:
Behavior: Ranks all rows passing the
WHEREclause by exact distancePerformance: Can be slower than indexed retrieval because it scans and sorts qualifying rows
Recall: Avoids IVF candidate pruning, which can help filtered or multi-tenant searches
Use Case: When the result set must not be limited to IVF candidates; compare plans and latency for your workload.
-- Example: Force mode
SELECT id, content, l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') AS distance
FROM documents
ORDER BY l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') ASC
LIMIT 10
BY RANK WITH OPTION 'mode = force';
mode = post (Post-ranking Mode)¶
In post-ranking mode, the rank function is applied after the IVF index has retrieved an expanded set of candidates. This mode:
Behavior: Retrieves more candidates from the IVF index, then re-ranks them using the exact distance computation
Performance: Slower than pre-ranking, but faster than full table scan
Accuracy: Higher accuracy, as the final ranking is based on exact distance calculations
Use Case: Scenarios requiring high recall where exact ranking is important.
-- Example: Post-ranking mode
SELECT id, content, l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') AS distance
FROM documents
ORDER BY l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') ASC
LIMIT 10
BY RANK WITH OPTION 'mode = post';
Comparison of Modes¶
Mode |
Index Usage |
Ranking Strategy |
Speed |
Accuracy |
Best For |
|---|---|---|---|---|---|
|
Uses IVF to filter first |
Rank within IVF candidates |
⚡ Fastest |
Lower |
High-throughput, latency-sensitive |
|
Does not use IVF in v4.2.5 |
Table scan and exact distance sort |
Slower on large scans |
No IVF candidate pruning |
Filtered or multi-tenant searches needing full recall |
|
Uses IVF for candidates |
Re-ranks with exact distance |
🐢 Slower |
⭐ Highest |
High-recall requirements |
|
Planner-selected IVF or scalar path |
Depends on the plan |
Depends on the plan |
Depends on the plan |
Let the planner choose; inspect |
NULL vector values¶
For a nullable vector column, a scalar ORDER BY l2_distance(vector_column, query) ASC LIMIT k includes rows whose vector is NULL; their distance is NULL, which sorts first in ascending order. In the verified v4.2.5 IVF path, a bare indexed ORDER BY and pre or post exclude NULL vectors; the reported auto query also selected this path. force uses the scalar scan and can therefore return NULL rows first. auto may also choose a scalar scan for a small or highly filtered result set, so its NULL behavior follows the chosen plan. Add WHERE vector_column IS NOT NULL when you want to exclude them, or declare the column NOT NULL to reject them on insert. Use EXPLAIN to confirm which path the query takes.
Complete Example¶
Setup¶
-- Enable IVF index feature
SET GLOBAL experimental_ivf_index = 1;
-- Create table with vector column
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(200),
embedding VECF32(4)
);
-- Insert sample data
INSERT INTO products VALUES (1, 'Product A', '[0.1, 0.2, 0.3, 0.4]');
INSERT INTO products VALUES (2, 'Product B', '[0.5, 0.6, 0.7, 0.8]');
INSERT INTO products VALUES (3, 'Product C', '[0.2, 0.3, 0.4, 0.5]');
INSERT INTO products VALUES (4, 'Product D', '[0.9, 0.8, 0.7, 0.6]');
INSERT INTO products VALUES (5, 'Product E', '[0.3, 0.4, 0.5, 0.6]');
-- Create IVF index
CREATE INDEX idx_products_embedding
USING IVFFLAT ON products(embedding)
LISTS=2 OP_TYPE "vector_l2_ops";
-- Set probe limit for query
SET @PROBE_LIMIT = 1;
Query Examples¶
-- Fast approximate search (pre-ranking)
SELECT id, name, l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') ASC
LIMIT 3
BY RANK WITH OPTION 'mode = pre';
-- Full scan and exact sort (force mode in v4.2.5)
SELECT id, name, l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') ASC
LIMIT 3
BY RANK WITH OPTION 'mode = force';
-- High-accuracy search (post-ranking)
SELECT id, name, l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') ASC
LIMIT 3
BY RANK WITH OPTION 'mode = post';
Best Practices¶
Choosing the Right Mode¶
For real-time applications (e.g., search-as-you-type): Use
mode = preFor batch processing where accuracy matters: Use
mode = postWhen IVF candidate pruning lowers recall for filtered searches: Compare
mode = forcewith indexed modes and inspectEXPLAIN
Combining with Probe Limit¶
The @PROBE_LIMIT variable controls how many IVF centroids are scanned on indexed paths. It does not make force use the IVF index in the verified v4.2.5 behavior:
-- High accuracy configuration
SET @PROBE_LIMIT = 10;
SELECT id, name, l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') ASC
LIMIT 5
BY RANK WITH OPTION 'mode = post';
-- High speed configuration
SET @PROBE_LIMIT = 1;
SELECT id, name, l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') ASC
LIMIT 5
BY RANK WITH OPTION 'mode = pre';
Performance Tuning¶
Start with
mode = prefor most use casesMonitor recall rate and switch to
mode = postif accuracy is insufficientUse
mode = forceonly after checking the full-scan cost and whether nullable vectors need an explicitIS NOT NULLfilter
Limitations¶
Indexed modes require a usable IVF index;
forcefollows the scalar scan path in the verified v4.2.5 behaviorCurrently supports only
l2_distancemetricThe mode option must be specified as a string literal