Example 1:

Let's start with a simple use case. We'll create a special 'lance' table that can be used to store vectors and perform "nearest neighbor" searches on them. This lance table has three columns of the following types:

(bigint, embedding, lvarchar)

The bigint column stores vector IDs. These must be unique numbers.

The embedding column can store vectors with any number of dimensions, but that number must be specified at table creation time.

The lvarchar column is for storing textual metadata. This metadata can be anything you like and can be useful for filtering (see example 5).


-- Create a database
create database examples with log;
-- Create a lance table called 'sample_tab1' to store vectors of 8
-- dimensions. This operation should also auto-register your vector
-- blade.
EXECUTE PROCEDURE lvector_create_table('sample_tab1', 8);
-- Insert some vectors. This must be done inside an explicit
-- transaction.
EXECUTE PROCEDURE lvector_sync_insert (
'sample_tab1', 1,
lvector_in('[1,2,3,4,5,6,7,8]'), 'This is vector 1'
);
EXECUTE PROCEDURE lvector_sync_insert (
'sample_tab1', 2,
lvector_in('[8,7,6,5,4,3,2,1]'), 'This is vector 2'
);
EXECUTE PROCEDURE lvector_sync_insert (
'sample_tab1', 3,
lvector_in('[1,1,2,2,3,3,4,4]'), 'This is vector 3'
);
EXECUTE PROCEDURE lvector_sync_insert (
'sample_tab1', 4,
lvector_in('[5,5,6,6,7,7,8,8]'), 'This is vector 4'
);
EXECUTE PROCEDURE lvector_sync_insert (
'sample_tab1', 5,
lvector_in('[3,3,3,3,3,3,3,3]'), 'This is vector 5'
); COMMIT;
-- Now let's find the vector in our table that is most like a given
-- vector: [1,1,2,2,3,3,3,3]
SELECT * FROM TABLE
(
lvector_search (
'sample_tab1', lvector_in('[1,1,2,2,3,3,3,3]'), 1
)
);
The lvector_search() routine returns a row type with the following structure: create row type lvector_search_result
(
id bigint, distance float
);

Note: it does not return a vector. It returns the ID of the vector judged to be most similar to the given vector, along with a distance value indicating how similar they were. The lower the distance value, the more similar the vectors are. The distance between two identical vectors is 0.

In the case of the query above, the results are: unnamed_col_1 ROW(3 ,2.000000000000)

This means the nearest neighbor to the given vector had an ID of 3, and the distance between the vectors was 2.000000000000. In other words, given a vector of [1,1,2,2,3,3,3,3], the following vector was judged to be the nearest neighbor:

[1,1,2,2,3,3,4,4]

We could also do the following to find the 3 nearest neighbors:


SELECT * FROM TABLE
(
lvector_search (
Chapter 4. Using the Blade
'sample_tab1',
lvector_in('[1,1,2,2,3,3,3,3]'), 3
)
);

The result of that search is: unnamed_col_1 ROW(3 ,2.000000000000)

unnamed_col_1 ROW(5 ,10.00000000000)

unnamed_col_1 ROW(1 ,60.00000000000)

In this case the distance values indicate that the first nearest neighbor is significantly more similar to the given vector than the second and third nearest neighbors.

Let's make this output a little more convenient to deal with by teasing apart the returned elements:


SELECT results.unnamed_col_1.id id, results.unnamed_col_1.distance distance
FROM TABLE (
lvector_search (
'sample_tab1', lvector_in('[1,1,2,2,3,3,3,3]'), 3
)::lvector_search_result
) results;
The result of that statement is: id distance
3 2.000000000000
5 10.00000000000
1 60.00000000000

Note: the IDs returned by lvector_search() are guaranteed to be sorted by increasing distance. Sorting on the distance column in the query itself is not necessary.

If our only column of interest was the id, we could use a different function:


SELECT * FROM TABLE
(
lvector_id_search (
'sample_tab1', lvector_in('[1,1,2,2,3,3,3,3]'),
3
)
);

As with lvector_search() the set of IDs returned by lvector_id_search() is guaranteed to be sorted by increasing distance.