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

mode

Selects the ranking path. Values include pre, post, auto, and force.

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 WHERE clause by exact distance

  • Performance: 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

pre

Uses IVF to filter first

Rank within IVF candidates

⚡ Fastest

Lower

High-throughput, latency-sensitive

force

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

post

Uses IVF for candidates

Re-ranks with exact distance

🐢 Slower

⭐ Highest

High-recall requirements

auto

Planner-selected IVF or scalar path

Depends on the plan

Depends on the plan

Depends on the plan

Let the planner choose; inspect EXPLAIN

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

  1. For real-time applications (e.g., search-as-you-type): Use mode = pre

  2. For batch processing where accuracy matters: Use mode = post

  3. When IVF candidate pruning lowers recall for filtered searches: Compare mode = force with indexed modes and inspect EXPLAIN

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 = pre for most use cases

  • Monitor recall rate and switch to mode = post if accuracy is insufficient

  • Use mode = force only after checking the full-scan cost and whether nullable vectors need an explicit IS NOT NULL filter

Limitations

  • Indexed modes require a usable IVF index; force follows the scalar scan path in the verified v4.2.5 behavior

  • Currently supports only l2_distance metric

  • The mode option must be specified as a string literal