Vector Indexes

On this page

New in v25.2: A vector index enables efficient approximate nearest neighbor (ANN) search on high-dimensional VECTOR columns. Use vector indexes to improve the performance of similarity searches over large datasets, such as embeddings generated by machine learning models.

This page describes how to create and use vector indexes on CockroachDB.

How do vector indexes work?

CockroachDB vector indexes organize vectors into a hierarchical structure of partitions using k-means clustering. This partition structure groups similar vectors together and enables efficient, tunable ANN searches.

When a query uses a vector index, CockroachDB explores a subset of partitions based on their proximity to the query vector. It then retrieves and evaluates a candidate set of vectors using the configured distance metric and returns the top nearest results.

Enable vector indexes

To enable the use of vector indexes, set the feature.vector_index.enabled cluster setting:

SET CLUSTER SETTING feature.vector_index.enabled = true;

To enable the creation of vector indexes on non-empty tables, also disable the sql_safe_updates session setting. This allows vector indexes to be backfilled on existing rows, during which table writes are blocked to ensure vector index consistency. This blocking behavior is a known limitation that is currently being tracked.

SET sql_safe_updates = false;

Create vector indexes

To create a vector index, use the CREATE VECTOR INDEX statement:

CREATE VECTOR INDEX ON {table} (column});

You can also specify a vector index during table creation. For example:

CREATE TABLE items (
    department_id INT,
    category_id INT,
    embedding VECTOR(1536),
    VECTOR INDEX (embedding)
);

Define prefix columns

You can create a vector index with one or more prefix columns to pre-filter the search space. This is especially useful for tables containing millions of vectors or more.

CREATE TABLE items (
    department_id INT,
    category_id INT,
    embedding VECTOR(1536),
    VECTOR INDEX (department_id, category_id, embedding)
);

A vector index is only used if each prefix column is constrained to a specific value in the query. For example:

WHERE department_id = 100 AND category_id = 200

You can filter on multiple prefix values using IN:

WHERE (department_id, category_id) IN ((100, 200), (300, 400))

The following example will not use the vector index:

WHERE department_id = 100 AND category_id >= 200

For an example, refer to Create and query a vector index.

Specify an opclass

You can optionally specify an opclass (operator class) that defines how the VECTOR data type is handled by the index. If not specified, the default is vector_l2_ops:

CREATE TABLE items (
    department_id INT,
    category_id INT,
    embedding VECTOR(1536),
    VECTOR INDEX embed_idx (embedding vector_l2_ops)
);

Comparisons

Vector indexes on VECTOR columns support the following comparison operator:

• L2 distance: <->

For usage, refer to the Example.

Tune vector indexes

The following vector index parameters are tunable:

• Search beam size, using vector_search_beam_size
• Partition size, using min_partition_size and max_partition_size
• Build beam size, using build_beam_size (not recommended)

For guidelines on how these parameters affect search accuracy and write performance, refer to Tuning considerations.

Set the following storage parameters when you create a vector index:

• min_partition_size: Minimum number of vectors in a partition before it is merged with another partition. This value must be greater than or equal to 1, up to a maximum of 1024. The default value is 16.
• max_partition_size: Maximum number of vectors in a partition before it is split into smaller partitions. This must be at least 4 times the value of min_partition_size, up to a maximum of 4096. The default value is 128.
• build_beam_size: Beam size for index build (how many branches of the k-means tree are explored when assigning a vector to a partition). The default value is 8. Cockroach Labs does not recommend changing this setting. It is more effective to increase vector_search_beam_size.

For example, the following statement creates a vector index with a custom partition size:

CREATE VECTOR INDEX ON items (category, embedding) WITH (min_partition_size=16, max_partition_size=128);

Set the vector_search_beam_size session setting to determine how many vector partitions will be considered during query execution. The default value is 32, which represents the number of partitions that are explored at each level of the k-means tree.

For example:

SET vector_search_beam_size = 16;

Tuning considerations

Partition size and beam size interact to control both the precision of nearest neighbor search and the cost of maintaining the index. You can improve the accuracy of vector searches by increasing either the search beam size or partition size:

• A larger search beam size improves accuracy by exploring more partitions, which increases the number of candidate vectors evaluated.

• A larger partition size improves accuracy by placing more vectors in each partition, which increases the number of candidate vectors retrieved for a given beam size. It also improves write performance by reducing the frequency of partition splits and merges.

In both cases, read latency and CPU usage increase with size.

Search accuracy

To explore more partitions during a search, increase vector_search_beam_size. This improves search accuracy by evaluating more of the index, but increases CPU usage and read latency because more partitions are scanned.

To group more vectors into each partition, increase the partition size with min_partition_size and max_partition_size. This improves search accuracy by causing more candidate vectors to be evaluated per partition. However, it increases CPU usage and increases read latency because each partition contains more vectors. For the same reason, you can often reduce vector_search_beam_size without sacrificing accuracy.

Search accuracy is highly dependent on workload factors such as partition size, the number of VECTOR dimensions, how well the embeddings reflect semantic similarity, and how vectors are distributed in the dataset. Experiment with vector_search_beam_size, min_partition_size, and max_partition_size on a representative dataset to find the optimal setting for your workload.

Write performance

Because a larger partition size leads to fewer partition splits and merges, it enables faster insert performance.

To optimize writes, increase the partition size with min_partition_size and max_partition_size. A larger partition size leads to fewer partition splits and merges, resulting in faster insert performance.

Example

In the following example, a vector index with a prefix column is used to optimize searches on a VECTOR column in 512 dimensions. The example table is populated with 156,541 example rows, using a Python script to quickly insert the vectors.

Before you begin

• Create a virtual python3 environment and install psycopg[binary]:

  python3 -m venv ~/venv
  source ~/venv/bin/activate
  pip install psycopg[binary]
  

• Download the Python script and sample data:

  curl -O https://vector-examples.s3.us-east-2.amazonaws.com/fast_insert.py
  curl -O https://vector-examples.s3.us-east-2.amazonaws.com/clip_embeddings_with_customers.csv
  

  Ensure that the .py and .csv files are located in the same directory.

Create and query a vector index

1. Start a single-node cluster:

   cockroach start-single-node \
   --insecure \
   --listen-addr=localhost:26257 \
   --http-addr=localhost:8080
   

2. In a separate terminal, open a SQL shell on the cluster:

   cockroach sql --insecure
   

3. Enable vector indexes on the cluster:

   SET CLUSTER SETTING feature.vector_index.enabled = true;
   

4. Create an items table that includes a VECTOR column called embedding, along with a vector index that uses customer_id as the prefix column:

   CREATE TABLE items (
       id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
       customer_id INT NOT NULL,
       name TEXT,
       embedding VECTOR(512),
       VECTOR INDEX (customer_id, embedding)
   );
   

5. In another terminal, run the Python script to insert the clip_embeddings_with_customers.csv data into the items table:

   python fast_insert.py
   

   This process should take approximately 5-10 minutes.

6. When the script is finished executing, verify that items is populated:

   SHOW TABLES;
   

     schema_name | table_name | type  | owner | estimated_row_count | locality
   --------------+------------+-------+-------+---------------------+-----------
     public      | items      | table | root  |              156541 | NULL
   

7. Perform a vector search using the <-> L2 distance operator. Include the WHERE clause to query only the vectors associated with a given customer_id, thus narrowing the vector search space:

   SELECT id, name, embedding
   FROM items
   WHERE customer_id = 1
   ORDER BY embedding <-> '[0.00644,-0.00866,-0.00977,0.02129,0.02191,-0.02144,-0.01439,-0.08154,-0.0587,0.06555,-0.00967,0.00842,0.02094,-0.02939,0.0192,-0.007866,-0.10443,0.0015745,-0.0217,-0.02982,-0.08563,-0.02203,0.0501,-0.003183,-0.01802,0.0655,0.01355,0.01678,-0.03091,-0.02046,0.01982,0.01646,0.01002,-0.0065,0.01979,0.004524,0.01073,-0.05038,-0.03114,-0.101,-0.04953,-0.02039,-0.023,-0.02094,-0.00508,0.0516,0.04684,0.04715,-0.0394,-0.007435,0.01808,-0.0278,0.05685,0.0329,0.00896,0.010826,-0.005516,-0.010315,-0.02145,0.02296,0.1049,-0.005733,0.03433,0.001492,0.02802,-0.0556,0.006145,0.1013,0.0001343,-0.03726,-0.006786,0.00227,-0.04358,0.04694,0.03943,0.03278,-0.0363,-0.01343,-0.03497,-0.0328,0.01595,0.05524,-0.01823,0.001669,-0.00695,0.02367,0.01013,0.002083,0.01569,-0.02184,0.02864,0.01169,-0.601,0.02724,0.02263,-0.02649,-0.0441,0.00481,-0.03882,0.03064,0.01927,0.03833,-0.010216,0.004444,0.00893,0.0193,0.03137,0.03958,0.01245,0.02023,0.02443,-0.0112,-0.0187,-0.03604,0.004948,-0.01305,-0.03836,0.02914,0.01202,-0.00994,0.0363,-0.02258,0.001203,-0.00885,-0.01624,-0.0581,0.0009136,-0.003847,-0.0367,0.02737,0.00599,-0.0483,-0.0118,0.0759,0.00767,0.003883,0.03516,0.02686,-0.03726,-0.00888,-0.01439,0.001924,-0.01883,0.002098,0.008156,0.02428,0.00918,0.0635,-0.0732,0.02007,-0.04968,-0.007645,0.0289,0.02925,0.0748,-0.01974,-0.005478,0.01422,-0.00888,0.00675,-0.0424,-0.0303,0.04932,0.02055,0.02792,0.0472,-0.003717,0.002607,-0.02156,-0.01054,0.01159,0.004093,0.0001816,-0.01353,-0.01317,-0.05783,-0.03,0.01709,-0.01575,0.0304,0.02939,-0.01793,0.00964,0.0189,-0.01825,0.01331,-0.00656,-0.04044,-0.02902,-0.01347,0.004368,0.001875,0.005116,0.01038,-0.00973,0.002888,0.000518,0.02673,0.0642,-0.03763,-0.02138,-0.0416,0.00846,-0.02809,0.0316,0.001358,-0.02815,-0.001282,-0.01662,0.02885,-0.05585,0.01015,-0.001081,0.03366,0.013054,-0.001288,-0.003696,0.0241,0.0399,-0.00688,-0.0214,0.05804,-0.02957,0.001987,-0.05627,0.02072,-0.01066,-0.03433,0.01738,0.009476,-0.04984,-0.01028,-0.01958,-0.01398,-0.002432,-0.007023,0.00878,-0.01415,-0.007385,0.0034,0.01254,0.01034,0.00281,-0.01055,-0.03906,0.1171,0.00905,0.04788,-0.0153,0.0007515,-0.02356,0.02296,-0.01756,0.02966,-0.011826,-0.005955,0.01316,0.02348,0.163,-0.01337,0.04013,0.02695,-0.067,-0.01836,-0.01407,-0.03992,-0.02078,-0.03387,-0.009224,-0.02885,-0.0403,0.01034,-0.01936,-0.05518,0.01933,-0.02551,-0.01865,0.011566,0.004776,-0.006454,-0.00337,0.0293,-0.04312,0.02434,-0.01068,0.0129,0.1411,-0.009705,0.00439,0.007706,-0.01176,0.002438,0.01431,0.000449,-0.0555,-0.01441,-0.10535,-0.02295,-0.01897,0.010284,-0.012115,0.001859,0.03467,0.03192,0.001146,-0.02293,-0.02399,-0.005974,-0.010124,-0.0008497,-0.000651,0.01846,0.0758,-0.0278,0.006382,0.03943,0.01388,0.05035,0.01521,0.06116,0.03442,-0.01788,-0.0569,0.006584,0.01228,0.01238,-0.03021,-0.01166,-0.006516,-0.008354,-0.02957,-0.02144,0.03128,0.03708,-0.01941,0.010666,-0.001733,-0.01843,0.046,-0.002312,0.00544,-0.03162,-0.02313,0.03387,0.02907,-0.00806,0.007584,-0.005272,0.001955,0.01826,0.08746,0.00287,0.06085,-0.029,-0.002197,-0.1055,-0.0318,0.189,0.02068,-0.02332,0.009674,-0.02429,-0.01633,-0.00488,-0.0756,0.01032,0.06146,-0.0911,-0.02176,0.03766,-0.01198,0.063,-0.014595,-0.01846,-0.00856,0.02599,0.0907,-0.01367,0.0222,-0.02231,-0.0008545,0.0555,-5.585e-05,0.005394,0.02129,0.011,0.02121,-0.01152,-0.00693,0.1929,-0.01228,0.02162,0.01978,0.01041,0.02802,-0.02838,0.04248,0.02315,0.1019,-0.01604,-0.00924,0.01413,-0.02165,-0.02916,0.010605,-0.04526,-0.0052,-0.06186,-0.00414,0.02492,0.0158,0.01962,-0.010445,0.006237,-0.0173,0.000725,-0.04904,0.00871,0.02087,0.007633,0.04895,-0.01091,0.03452,0.05344,-0.03525,0.0241,-0.03035,-0.0248,0.1195,-0.005257,-0.02145,-0.004898,0.06647,0.0414,-0.02187,0.00893,0.01549,0.002043,-0.01074,0.00422,0.01991,0.04913,0.04034,0.00208,0.01962,-0.02525,-0.03244,-0.0376,-0.03757,0.003544,0.01556,0.0317,-0.05896,0.01587,0.03357,-0.0199,0.02716,-0.03397,0.02982,0.05362,0.0505,-0.0003583,0.00599,0.01162,0.014046,-0.01706,-0.0169,0.0216,-0.006832,0.04095,-0.01227,0.00589,0.01863,-0.00428,0.00327,0.00678,-0.02963,-0.012665,0.05237,-0.02408,0.0238,-0.02515,-0.01116,0.00577,-0.00711,0.05258,0.003374,0.04153,0.01464,-0.01508,-0.03598,-0.01016,0.0322,-0.0585,0.005238,-0.02141,-0.001159,0.010124,-0.02986,-0.02534,-0.005997,0.004307,-0.02657,0.01692,-0.03638,-0.05765,-0.03622,0.003136,-0.0616,0.07184,-0.006992,-0.007393]'
   LIMIT 3;
   

                      id                  | name |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                embedding
   ---------------------------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     2866cda3-dec1-4db6-92d3-e0409a7b60dd | NULL | [-0.03644,0.00482,0.006996,0.0397,0.02031,0.003174,0.05167,-0.02255,-0.0743,0.0247,0.02051,0.00207,0.02278,0.0117,0.04782,-0.02684,-0.1234,0.00639,-0.03387,-0.0494,-0.02823,-0.02385,0.03036,-0.0217,-0.04425,0.02248,-0.0113,0.00833,0.01473,0.02982,-0.03186,0.032,0.008354,-0.00673,0.003376,0.00646,-0.02066,-0.05078,-0.004715,-0.07306,-0.01863,-0.01497,-0.01407,-0.002058,0.04614,0.03128,0.02272,0.00572,0.000955,0.0413,0.005558,0.02249,0.00388,0.001359,0.01732,-0.02692,0.0137,0.02469,-0.01049,0.03384,0.0442,0.00991,-0.004353,0.00511,0.01435,-0.0434,0.00959,-0.04965,-0.0172,0.013466,0.01852,0.03873,-0.0102,0.00707,0.002037,0.03177,-0.02678,-0.03214,-0.03102,-0.03455,-0.005142,-0.05164,0.03394,-0.03485,0.0359,0.02263,0.009865,-0.0018,-0.000672,0.01395,0.04688,0.01104,-0.607,-0.011375,-0.006874,0.02104,-0.01787,-0.007572,-0.004025,0.0017395,0.0186,0.015205,-0.03333,0.0474,0.039,-0.00796,-0.02977,0.05194,0.006454,0.01056,-0.02045,-0.00486,-0.03824,0.006428,0.001293,-0.0268,-0.07,0.01863,0.001716,-0.0899,0.05594,-0.07916,0.01593,0.02011,0.013504,-0.04227,-0.04062,0.03958,-0.01197,-0.01006,0.0192,-0.0353,-0.002686,0.08795,0.002575,0.001193,-0.02109,-0.02652,-0.03099,0.03323,0.02167,-0.03955,0.004143,0.03265,0.02802,0.003092,-0.01134,0.003077,-0.004684,-0.02434,-0.02345,0.01712,0.0695,0.00811,0.03577,-0.03026,0.004063,0.0642,0.02876,-0.01639,-0.01843,-0.0696,0.004375,0.015434,0.01572,-0.016,0.06903,0.01205,0.02037,-0.03323,0.006317,0.009705,-0.002304,-0.0001507,-0.01113,-0.002771,-0.04773,0.014145,0.05124,0.04495,-0.001354,-0.0288,0.0207,0.02036,0.01369,-0.00684,-0.01397,-0.01591,-0.01817,-0.01554,-0.004925,-0.01067,0.002968,0.0596,0.005974,0.02592,-0.02792,0.005314,-0.00833,-0.00875,0.01863,-0.02574,0.02136,0.01362,0.002321,0.03162,-0.04065,0.000871,0.01346,0.0535,-0.011566,0.07074,0.007618,0.04932,0.01361,-0.001603,0.0289,0.03278,0.01909,0.02127,-0.04688,0.1057,-0.03738,0.01984,-0.04022,0.0111,-0.00466,-0.01808,0.05374,-0.01866,-0.02579,-0.0648,-0.0267,0.0648,-0.003239,0.03595,-0.00921,-0.003769,-0.00759,-0.0316,0.02364,-0.011086,0.03192,-0.0008087,-0.002354,-0.01492,-0.008965,0.03827,0.0317,0.05157,0.0001527,0.01423,0.006557,0.011765,-0.03833,0.00889,-0.06104,0.02867,0.1672,0.014336,-0.0161,0.015434,0.013176,-0.005596,0.02539,-0.01558,-0.05176,-0.01062,0.009155,-0.02776,-0.004932,0.01782,-0.0332,-0.03375,0.00864,-0.004425,0.01204,-0.03041,-0.01678,0.01637,0.02888,0.02235,0.003016,-0.005768,-0.00348,0.0488,-0.02907,-0.04785,-0.00077,0.03354,-0.00788,0.03424,0.01717,0.01683,-0.03534,0.0598,-0.04453,-0.004646,0.001023,0.0466,-0.011185,0.00532,0.02937,-0.01613,0.02042,-0.02347,-0.02483,-0.001838,0.02417,-0.001924,-0.0326,-0.01213,0.0877,-0.01035,-0.02525,0.02266,0.05524,0.0865,-0.00938,0.02348,0.01572,-0.004414,-0.03546,-0.0697,0.001506,0.02872,-0.00553,-0.009735,0.014336,0.01701,-0.01287,-0.01846,0.004284,-0.0694,-0.00635,-0.01692,-0.000953,-0.007477,-0.0008273,0.00807,0.00921,-0.011894,0.03998,0.0329,-0.03455,0.00799,0.05063,-0.00874,0.0002373,0.0226,0.0742,0.02228,0.04156,-0.03574,-0.02008,-0.0889,-0.03833,-0.01566,-0.000461,0.0349,0.0313,-0.02667,-0.01878,0.00832,-0.1144,0.02379,0.02774,-0.1357,0.03107,0.03583,0.00709,-0.00172,0.01248,-0.0072,0.02652,-0.01532,0.0684,-0.0369,3.62e-05,0.013435,-0.04797,0.0583,-0.0008154,-0.003048,-0.02094,0.001323,0.0204,-0.002422,0.013306,0.266,0.03226,0.04645,-0.005733,-0.03748,0.0342,-0.00557,0.007454,-0.01736,-0.03677,0.02315,-0.01697,0.02109,0.0515,-0.01747,0.041,-0.00872,-0.003311,-0.02701,-0.0381,-0.00983,-0.03528,0.00798,-0.009315,0.01622,-0.00243,-0.03995,-0.004265,-0.02551,-0.00563,0.01226,-0.0163,-0.0433,0.0512,0.11676,-0.02383,-0.00274,0.0289,-0.04498,0.1505,-0.003675,0.02513,0.03104,0.1508,0.00901,-0.005783,-0.03717,-0.03342,0.02475,-0.003098,-0.002007,0.01438,0.03046,-0.007656,-0.01273,-0.00438,-0.02122,-0.001858,-0.03226,-0.01491,0.006985,0.010124,-0.03723,-0.02298,-0.0126,0.0179,0.004795,0.01403,-0.006718,-0.01395,0.00429,-0.04205,0.000871,0.02074,-0.001945,0.07294,-0.00494,0.004997,-0.01564,-0.01724,0.00482,-0.02258,-0.003819,0.002863,0.00866,0.02623,-0.01046,0.01287,-0.02518,-0.005424,-0.01311,0.0372,-0.03754,0.03026,0.0295,-0.01616,-0.007473,0.01662,0.01788,0.007236,-0.02623,-0.03452,0.03406,0.002787,-0.04785,-0.0353,0.03503,-0.04565,0.01727,0.0008183,0.01628,-0.08044,-0.01057,0.02364,0.01581,0.01917,0.01651,0.003077,0.05594,0.004616,0.007362,0.01497,-0.01765]
     513f4590-6752-4b2f-bb82-67d72c70f422 | NULL | [-0.02757,0.03363,-0.002504,-0.03143,0.010445,-0.01143,0.03604,-0.01811,0.005474,0.01024,0.003687,0.03348,-0.01122,-0.01259,-0.02325,-0.02614,-0.04807,-0.0165,-0.05173,-0.01097,-0.1339,0.005417,0.0317,0.00859,-0.0477,0.004086,0.002243,0.007233,0.001098,0.002834,-0.01364,0.03265,0.001186,-0.01025,0.0677,-0.02202,0.02513,0.01933,0.01411,-0.006374,0.02174,-0.001868,-0.02908,-0.046,-0.007904,-0.01037,0.05484,0.02788,-0.04868,0.04016,0.01578,-0.02301,0.007183,-0.001128,-0.02968,-0.004166,0.0356,-0.01477,0.005505,0.03494,0.02481,-0.01596,-0.03235,-0.001357,-0.002623,-0.03552,-0.02594,0.04453,-0.0286,-0.0174,0.011215,0.01537,0.003532,0.0501,0.00502,-0.00243,-0.02594,-0.03433,-0.009895,-0.004185,-0.01077,-0.009315,-0.02747,0.06113,0.01248,0.06012,-0.01628,-0.01474,0.0099,-0.03476,0.0008,-0.01125,-0.662,-0.05505,-0.01677,0.03537,0.01775,-0.002335,-0.1058,-0.0397,0.03235,0.0475,-0.01093,-0.00847,0.08856,0.03558,0.007225,-0.02354,-0.002796,0.01747,-0.000744,-0.071,-0.01648,-0.0071,0.01656,-0.02428,-0.002491,0.01855,0.0413,0.01563,0.0225,-0.0977,0.01176,-0.02167,0.02638,-0.06323,0.00852,0.001434,0.0004199,-0.00258,0.005215,0.003527,-0.01863,0.08484,0.003096,-0.01843,0.01115,0.002579,-0.03516,0.01261,0.01038,-0.003784,-0.014465,0.04703,-0.0346,-0.004364,-0.01103,0.03534,-0.0427,-0.03128,0.00154,0.003956,0.0849,0.01046,0.01349,-0.0556,0.03403,0.01761,-0.00954,-0.03506,-0.03653,-0.05484,-0.006783,0.0165,-0.01657,0.007904,0.0392,0.01097,0.02182,-0.04132,0.03023,0.01999,-0.04117,-0.011284,0.00849,-0.004578,-0.0568,0.007545,0.10474,0.02103,0.01229,-0.01224,-0.000572,0.02428,0.006638,-0.02933,-0.02191,0.02292,0.01004,-0.02577,0.005447,-0.0259,0.0088,0.043,0.006,0.00954,0.00209,-0.011185,0.00913,-0.02692,0.004944,0.02112,0.0145,0.005737,0.0094,0.02824,-0.02846,0.006325,-0.0215,0.008026,0.00893,0.04208,0.006428,0.03897,0.01944,-0.006557,0.0367,0.01634,-0.03888,0.003195,0.02547,0.163,-0.01797,0.032,-0.0434,-0.03114,0.03226,0.00644,0.05112,0.014175,-0.02707,-0.01158,0.04343,-0.004475,0.01518,-0.0232,-0.0006795,-0.01715,-0.03503,0.02348,0.01886,0.01442,0.01683,-0.02095,-0.02638,-0.009926,0.01022,0.01305,-0.004284,0.05594,-0.02388,0.01686,-0.02864,0.03714,-0.0329,-0.02339,-0.05585,0.01872,0.03062,-0.03558,-0.00523,0.001399,-0.0071,-0.1349,0.002829,-0.001425,0.002012,0.01302,0.0006456,-0.0382,-0.01566,0.0003338,-0.003765,-0.02191,0.013954,-0.0102,-0.02231,-0.05237,0.012085,0.01254,0.02873,0.00871,-0.0205,0.006245,0.00924,0.01877,-0.03482,-0.02145,0.00811,0.03677,-0.005703,0.04358,0.001885,-0.002228,-0.05258,0.008804,-0.04688,0.01163,0.02103,-0.0003643,0.03784,0.00559,0.01167,0.04205,0.04227,0.01569,0.02556,0.005676,-0.0214,0.015594,-0.02084,0.04962,0.0846,0.01317,0.00768,0.003391,0.04996,0.03001,0.0334,0.04266,0.02325,-0.04535,0.0067,-0.01184,0.01546,0.001275,0.00804,0.01394,-0.02615,0.01249,-0.0357,-0.000846,-0.04144,-0.04398,-0.005173,0.01415,0.003267,-0.01496,0.03235,-0.03256,0.0088,0.004997,0.01761,0.03683,-0.01743,0.01863,0.01194,-0.004982,0.02257,0.0003695,0.1049,0.013176,0.0371,-0.0324,-0.0156,-0.10675,-0.06903,0.09406,-0.007797,0.0532,0.10223,0.01275,-0.03586,0.03604,-0.01872,0.01129,0.01526,-0.1514,0.01288,0.0208,-0.03937,0.03928,-0.02327,-0.007015,-0.001682,0.01135,0.05408,-0.011375,-0.01837,0.01842,-0.02496,0.0001932,-0.01177,-0.01588,-0.0254,0.01057,0.04688,-0.002123,-0.04083,0.1378,-0.00748,0.0416,0.02693,-0.02605,0.02408,-0.04498,0.03806,0.0009537,-0.02771,-0.09216,0.005604,0.0324,0.02536,-0.02414,0.00976,-0.002401,0.02286,-0.02615,0.02904,-0.000424,0.02724,0.01851,-0.001937,-0.001205,-0.02917,0.009995,-0.07245,0.00454,-0.005375,-0.0172,-0.010345,-0.0474,0.02202,0.071,-0.0132,0.001837,-0.0014715,0.02368,0.0693,0.04727,0.0005713,0.0287,0.09436,-0.00534,-0.006596,-0.04266,-0.01209,0.0471,0.02582,-0.03198,0.01307,-0.0127,-0.001337,-0.006454,-0.02988,0.02591,-0.002998,-0.03296,-0.03275,-0.02698,0.0742,-0.01837,-0.03986,0.01974,0.02505,-0.000777,0.015335,-0.01904,-0.013916,0.01323,0.001882,0.03152,-0.007282,0.03372,0.03308,0.005276,0.00905,-0.044,-0.06903,0.001275,-0.03032,0.02202,-0.01657,-0.02832,-0.005085,-0.0206,-0.01184,-0.01447,-0.01884,-0.01501,-0.01541,-0.02992,0.004375,0.03162,-0.02385,0.02718,-0.01422,0.04727,0.0358,0.02664,-0.03604,-0.006096,-0.006454,-0.04654,-0.02336,0.012924,-0.008446,0.01808,-0.03503,0.02748,-0.03174,-0.02052,0.014275,0.002783,-0.02283,0.01484,-0.02454,0.003788,-0.0421,0.02864,-0.03177,-0.001041]
     0f6db280-2f5c-46e2-b205-1a9789274fdd | NULL | [-0.000977,0.03008,0.01264,-0.0191,0.058,-0.02066,0.013016,-0.0173,-0.04626,-0.002918,0.00225,0.04648,0.08905,0.01449,0.02306,-0.003393,0.00471,0.0274,-0.01582,-0.001243,-0.0779,0.01241,0.04013,-0.0328,-0.03958,0.04245,0.0472,-0.00982,-0.00791,-0.01234,0.02104,0.01468,0.001149,-0.0204,0.04776,0.013565,0.02327,-0.03024,-0.01694,0.0321,0.006046,-0.03445,-0.04147,-0.036,0.02162,-0.001715,0.04346,0.0189,0.002394,-0.002722,0.03888,0.0211,0.007782,-0.00607,0.02922,0.04376,0.0466,-0.0001429,-0.04004,-0.01465,-0.0274,0.0381,0.01874,-0.02205,0.001417,-0.02008,0.00832,0.1,0.02376,-0.01936,0.03363,0.01427,-0.02505,0.02441,0.01863,-0.003431,-0.06323,0.006477,-0.01357,-0.02254,-0.001032,0.03503,-0.02559,0.001091,-0.01418,0.00471,-0.002892,-0.005604,0.04144,0.006493,0.002033,0.001527,-0.5815,-0.0408,-0.01397,-0.0269,0.03784,-0.003103,0.00254,-0.01134,0.0172,0.0203,-0.03253,0.01131,0.03918,-0.0003843,0.02423,-0.03638,-0.004627,0.00826,-0.02834,0.00881,-0.0187,0.02817,-0.02803,-0.03262,-0.01036,-0.01593,-0.001248,-0.04688,0.02213,-0.0231,0.01172,0.04047,-0.02185,-0.03723,-0.03105,-0.01598,-0.01811,0.0363,0.05566,-0.03192,0.05182,0.08356,-0.00711,-0.001823,0.0008454,0.02731,-0.04236,0.0138,0.02322,-0.00859,-0.02583,0.03041,0.00799,-0.0394,-0.000641,0.003445,-0.03802,-0.03464,-0.01482,0.0001334,0.1339,0.0358,0.002865,-0.063,0.02773,0.03336,0.0234,0.02739,-0.0608,-0.05664,0.01071,0.001357,-0.01196,0.02057,0.005497,0.001594,-0.03125,-0.03665,0.08344,0.02264,0.02051,-0.00868,-0.01581,0.0344,-0.12396,0.01107,0.04828,0.03357,0.02113,-0.0349,-0.01306,-0.01755,0.001984,-0.003275,-0.003826,0.03635,-0.03099,-0.00834,0.009575,-0.01292,-0.01805,0.02097,-0.0591,0.01932,-0.01383,0.00875,-0.02512,-0.07135,0.01423,-0.04843,0.02608,0.01518,0.010994,-0.011024,-0.05884,0.02287,-0.02579,0.0718,-0.107,-0.01247,-0.01697,0.02719,-0.003199,0.00372,0.0431,0.00832,-0.0082,0.01275,-0.0127,0.1322,-0.02583,-0.0173,-0.04126,-0.03934,0.006298,0.02167,0.06726,-0.03516,0.0186,-0.0665,-0.05072,0.05667,0.02922,-0.0575,-0.0249,0.01446,-0.009605,0.04834,0.01805,0.01423,0.01926,-0.0001755,0.0232,0.01836,0.02002,0.03305,-0.00636,0.04385,0.02626,0.001762,-0.00881,0.02336,-0.02011,-0.05426,-0.0093,0.0816,0.05774,-0.01006,0.02441,0.01507,0.0001404,-0.1952,-0.01779,0.006603,0.02126,-0.0118,0.01395,-0.02205,-0.0316,0.01348,-0.041,-0.02565,-6.056e-05,-0.0371,0.00999,0.003849,-0.01478,0.02855,0.01816,0.02171,-0.05563,0.02286,-0.01959,0.02803,0.19,-0.02783,-0.00093,0.03513,-0.02428,0.02867,0.02393,0.013405,-0.03455,0.011696,-0.12396,-0.02718,-0.00989,0.04935,0.0084,-0.005417,0.04126,0.02371,-0.001195,-0.0379,0.001037,0.02473,-0.03035,-0.003489,-0.03854,0.0676,0.08325,-0.01851,-0.02739,0.02844,0.02875,0.03857,-0.02931,0.09625,-0.01741,-0.1842,-0.02097,-0.015076,-0.00682,-0.02135,0.01218,0.0367,0.03955,-0.00705,-0.0797,0.01304,0.01625,-0.10333,-0.010376,-0.00648,0.0401,-0.003351,-0.004044,-0.02838,0.000996,-0.00687,-0.01151,0.04446,0.0206,-0.03183,0.02011,-0.04276,0.02425,-0.01828,0.0662,0.02696,0.04562,-0.02335,0.01321,-0.05106,-0.02187,0.01258,-0.01883,0.009155,0.07544,0.01332,-0.00706,0.02206,-0.09143,-0.005,0.02393,-0.0724,-0.00705,0.04205,-0.0215,0.03262,-0.02193,-0.02773,-0.0324,0.0167,0.08716,-0.02177,0.04605,0.005444,-0.03143,0.04556,0.005333,-0.02884,0.0079,0.01171,0.004105,-0.04163,-0.0182,0.02641,-0.02374,-0.002714,-0.0346,0.00935,0.01842,0.01767,0.006275,-0.02153,-0.01002,-0.01343,-0.0115,0.01061,-0.04398,0.003918,-0.01535,-0.02837,0.001749,-0.02745,-0.05524,-0.002453,-0.03714,0.01064,-0.01642,0.02625,-0.02249,-0.02637,-0.0637,-0.00944,0.04187,0.007,-0.006554,0.000269,-0.04156,0.01999,-0.02803,0.01653,0.001995,0.01417,0.01898,-0.003187,0.01182,0.05963,0.06696,0.02614,-0.01329,-0.01974,-0.01865,0.02406,-0.04266,-0.012535,0.01552,0.02473,-0.0001897,-0.02084,0.0084,-0.02176,0.006947,-0.04846,-0.04532,-0.0499,-0.01336,0.01921,-0.02257,-0.05414,0.05032,0.000645,0.03763,-0.02065,-0.03738,-0.02173,0.02533,0.02586,-0.005615,0.0093,0.01039,0.025,-0.0235,-0.0345,-0.02386,-0.003807,0.01294,-0.03625,-0.001739,-0.003138,0.04724,0.03128,0.01614,-0.004505,0.00232,0.02686,0.009514,-0.04816,0.00592,0.00842,-0.03784,0.04224,0.00503,0.0304,0.01193,-0.011894,-0.01842,0.0158,0.02986,-0.01178,-0.001757,0.0504,0.01653,0.039,-0.005116,0.000661,-0.05786,-0.01895,0.02576,-0.001175,-0.02713,0.0174,-0.03708,-0.00614,-0.003826,0.079,0.00843,0.002083]
   (3 rows)
   
   Time: 14ms total (execution 13ms / network 1ms)
   

8. Use EXPLAIN to show how the vector index pre-filtered the vector search space:

   EXPLAIN SELECT id, name, embedding
   FROM items
   WHERE customer_id = 1
   ORDER BY embedding <-> '[0.00644,-0.00866,-0.00977,0.02129,0.02191,-0.02144,-0.01439,-0.08154,-0.0587,0.06555,-0.00967,0.00842,0.02094,-0.02939,0.0192,-0.007866,-0.10443,0.0015745,-0.0217,-0.02982,-0.08563,-0.02203,0.0501,-0.003183,-0.01802,0.0655,0.01355,0.01678,-0.03091,-0.02046,0.01982,0.01646,0.01002,-0.0065,0.01979,0.004524,0.01073,-0.05038,-0.03114,-0.101,-0.04953,-0.02039,-0.023,-0.02094,-0.00508,0.0516,0.04684,0.04715,-0.0394,-0.007435,0.01808,-0.0278,0.05685,0.0329,0.00896,0.010826,-0.005516,-0.010315,-0.02145,0.02296,0.1049,-0.005733,0.03433,0.001492,0.02802,-0.0556,0.006145,0.1013,0.0001343,-0.03726,-0.006786,0.00227,-0.04358,0.04694,0.03943,0.03278,-0.0363,-0.01343,-0.03497,-0.0328,0.01595,0.05524,-0.01823,0.001669,-0.00695,0.02367,0.01013,0.002083,0.01569,-0.02184,0.02864,0.01169,-0.601,0.02724,0.02263,-0.02649,-0.0441,0.00481,-0.03882,0.03064,0.01927,0.03833,-0.010216,0.004444,0.00893,0.0193,0.03137,0.03958,0.01245,0.02023,0.02443,-0.0112,-0.0187,-0.03604,0.004948,-0.01305,-0.03836,0.02914,0.01202,-0.00994,0.0363,-0.02258,0.001203,-0.00885,-0.01624,-0.0581,0.0009136,-0.003847,-0.0367,0.02737,0.00599,-0.0483,-0.0118,0.0759,0.00767,0.003883,0.03516,0.02686,-0.03726,-0.00888,-0.01439,0.001924,-0.01883,0.002098,0.008156,0.02428,0.00918,0.0635,-0.0732,0.02007,-0.04968,-0.007645,0.0289,0.02925,0.0748,-0.01974,-0.005478,0.01422,-0.00888,0.00675,-0.0424,-0.0303,0.04932,0.02055,0.02792,0.0472,-0.003717,0.002607,-0.02156,-0.01054,0.01159,0.004093,0.0001816,-0.01353,-0.01317,-0.05783,-0.03,0.01709,-0.01575,0.0304,0.02939,-0.01793,0.00964,0.0189,-0.01825,0.01331,-0.00656,-0.04044,-0.02902,-0.01347,0.004368,0.001875,0.005116,0.01038,-0.00973,0.002888,0.000518,0.02673,0.0642,-0.03763,-0.02138,-0.0416,0.00846,-0.02809,0.0316,0.001358,-0.02815,-0.001282,-0.01662,0.02885,-0.05585,0.01015,-0.001081,0.03366,0.013054,-0.001288,-0.003696,0.0241,0.0399,-0.00688,-0.0214,0.05804,-0.02957,0.001987,-0.05627,0.02072,-0.01066,-0.03433,0.01738,0.009476,-0.04984,-0.01028,-0.01958,-0.01398,-0.002432,-0.007023,0.00878,-0.01415,-0.007385,0.0034,0.01254,0.01034,0.00281,-0.01055,-0.03906,0.1171,0.00905,0.04788,-0.0153,0.0007515,-0.02356,0.02296,-0.01756,0.02966,-0.011826,-0.005955,0.01316,0.02348,0.163,-0.01337,0.04013,0.02695,-0.067,-0.01836,-0.01407,-0.03992,-0.02078,-0.03387,-0.009224,-0.02885,-0.0403,0.01034,-0.01936,-0.05518,0.01933,-0.02551,-0.01865,0.011566,0.004776,-0.006454,-0.00337,0.0293,-0.04312,0.02434,-0.01068,0.0129,0.1411,-0.009705,0.00439,0.007706,-0.01176,0.002438,0.01431,0.000449,-0.0555,-0.01441,-0.10535,-0.02295,-0.01897,0.010284,-0.012115,0.001859,0.03467,0.03192,0.001146,-0.02293,-0.02399,-0.005974,-0.010124,-0.0008497,-0.000651,0.01846,0.0758,-0.0278,0.006382,0.03943,0.01388,0.05035,0.01521,0.06116,0.03442,-0.01788,-0.0569,0.006584,0.01228,0.01238,-0.03021,-0.01166,-0.006516,-0.008354,-0.02957,-0.02144,0.03128,0.03708,-0.01941,0.010666,-0.001733,-0.01843,0.046,-0.002312,0.00544,-0.03162,-0.02313,0.03387,0.02907,-0.00806,0.007584,-0.005272,0.001955,0.01826,0.08746,0.00287,0.06085,-0.029,-0.002197,-0.1055,-0.0318,0.189,0.02068,-0.02332,0.009674,-0.02429,-0.01633,-0.00488,-0.0756,0.01032,0.06146,-0.0911,-0.02176,0.03766,-0.01198,0.063,-0.014595,-0.01846,-0.00856,0.02599,0.0907,-0.01367,0.0222,-0.02231,-0.0008545,0.0555,-5.585e-05,0.005394,0.02129,0.011,0.02121,-0.01152,-0.00693,0.1929,-0.01228,0.02162,0.01978,0.01041,0.02802,-0.02838,0.04248,0.02315,0.1019,-0.01604,-0.00924,0.01413,-0.02165,-0.02916,0.010605,-0.04526,-0.0052,-0.06186,-0.00414,0.02492,0.0158,0.01962,-0.010445,0.006237,-0.0173,0.000725,-0.04904,0.00871,0.02087,0.007633,0.04895,-0.01091,0.03452,0.05344,-0.03525,0.0241,-0.03035,-0.0248,0.1195,-0.005257,-0.02145,-0.004898,0.06647,0.0414,-0.02187,0.00893,0.01549,0.002043,-0.01074,0.00422,0.01991,0.04913,0.04034,0.00208,0.01962,-0.02525,-0.03244,-0.0376,-0.03757,0.003544,0.01556,0.0317,-0.05896,0.01587,0.03357,-0.0199,0.02716,-0.03397,0.02982,0.05362,0.0505,-0.0003583,0.00599,0.01162,0.014046,-0.01706,-0.0169,0.0216,-0.006832,0.04095,-0.01227,0.00589,0.01863,-0.00428,0.00327,0.00678,-0.02963,-0.012665,0.05237,-0.02408,0.0238,-0.02515,-0.01116,0.00577,-0.00711,0.05258,0.003374,0.04153,0.01464,-0.01508,-0.03598,-0.01016,0.0322,-0.0585,0.005238,-0.02141,-0.001159,0.010124,-0.02986,-0.02534,-0.005997,0.004307,-0.02657,0.01692,-0.03638,-0.05765,-0.03622,0.003136,-0.0616,0.07184,-0.006992,-0.007393]'
   LIMIT 3;
   

                                info
   --------------------------------------------------------------
     distribution: local
     vectorized: true
   
     • top-k
     │ estimated row count: 3
     │ order: +column9
     │ k: 3
     │
     └── • render
         │
         └── • lookup join
             │ table: items@items_pkey
             │ equality: (id) = (id)
             │ equality cols are key
             │
             └── • vector search
                   table: items@items_customer_id_embedding_idx
                   target count: 3
                   prefix spans: [/1 - /1]
   

   In the preceding output, prefix spans: [/1 - /1] shows that the search is limited to the part of the index where customer_id = 1.

Known limitations

• Large batch inserts of VECTOR types can cause performance degradation. When inserting vectors, batching should be avoided. For an example, refer to Create and query a vector index.
• Creating a vector index through a backfill disables mutations (INSERT, UPSERT, UPDATE, DELETE) on the table. #144443
• IMPORT INTO is not supported on tables with vector indexes. You can import the vectors first and create the index after import is complete. #145227
• Only L2 distance (<->) searches are accelerated. #144016
• Index acceleration with filters is only supported if the filters match prefix columns. #146145
• Index recommendations are not provided for vector indexes. #146146
• Vector index queries may return incorrect results when the underlying table uses multiple column families. #146046
• Queries against a vector index may ignore filter conditions (e.g., a WHERE clause) when multiple vector indexes exist on the same VECTOR column, and one has a prefix column. #146257

See also

• VECTOR
• CREATE INDEX
• Indexes

Was this helpful?