Indexing parts of vector separately and together about pgvector

[jkatz]

Can you please explain the use case a bit more? What problem are you trying to solve? Why are you concatenating two vectors together?

[phobrain]

The vectors are color histograms for images. I'm using the histogram combinations as one element in a list of potential 'mood filters' for choosing an image.

I'd also like to try sampling imagenet vectors, since it turns out that averaging, say, VGG16's 7x7 blocks of 512, and then folding that down to sizes as small as 2 can preserve theme.

[256 is luminance(128) concatenated with greyscale(128)].

E.g. it just took 202 minutes to insert 6 permutations of NASNetLarge pre-averaged vectors on 100K records, albeit with 6 reads of all the vectors. Still, I'm sure 100K inserts would be noticeably faster than 600K.

[ankane]

Hi @phobrain, you can use array slicing and expression indexing for this. For instance, to index and query the first 3 dimensions:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(5));

INSERT INTO items (embedding) VALUES ('[1,2,3,4,10000000]'), ('[5,6,7,8,9]');

CREATE INDEX ON items USING hnsw (((embedding::float4[])[0:3]::vector(3)) vector_l2_ops);

-- SET enable_seqscan = off; to use index when table has little data
SELECT * FROM items ORDER BY (embedding::float4[])[0:3]::vector(3) <-> '[1,2,3]'::vector LIMIT 5;

A function for slicing vectors could make this slightly simpler (vector_slice(embedding, 0, 3)), but it doesn't seem like enough of an improvement or common enough to add right now.

[phobrain]

Thanks @ankane, that covers my immediate need, and I can implement vector_slice() as an sql generator.

Can one concatenate noncontiguous fields in that notation?

[ankane]

Yes, Postgres also has an array_cat function:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3), embedding2 vector(2));

INSERT INTO items (embedding, embedding2) VALUES ('[1,2,3]', '[4,10000000]'), ('[5,6,7]', '[8,9]');

CREATE INDEX ON items USING hnsw ((array_cat(embedding::float4[], embedding2::float4[])::vector(5)) vector_l2_ops);

SELECT * FROM items ORDER BY array_cat(embedding::float4[], embedding2::float4[])::vector(5) <-> '[1,2,3,4,5]'::vector LIMIT 5;

[ankane]

Just fyi, there will be a subvector function and concat operator in 0.7.0 (#508).

SELECT subvector('[1,2,3,4,5]'::vector, 1, 3);
-- [1,2,3]

SELECT '[1,2,3]'::vector || '[4,5]';
-- [1,2,3,4,5]