Multi-column index? about pgvector

[ankane]

Hey @zhihongx0, multi-column support would be pretty difficult and I think is outside the scope of IVFFlat indexes (and pgvector doesn't support GiST), but there are two strategies you can use to efficiently query on conditions:

1. Use a partial index

CREATE INDEX ON table USING ivfflat (column) WHERE (other_column = 123);

2. If you need to query on many different values of other_column, it should be more efficient to partition the table on other_column (so each partition will get a separate IVFFlat index)

[ankane]

Added this info to the readme.

Here's a quick example for partitioning with Postgres 13 (should work with range, list, and hash).

CREATE EXTENSION IF NOT EXISTS vector;
DROP TABLE IF EXISTS items;
CREATE TABLE items (
  id bigserial,
  factors vector(3),
  category_id bigint
) PARTITION BY HASH(category_id);

CREATE TABLE items_part1 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE items_part2 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE items_part3 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 2);

INSERT INTO items (factors, category_id) SELECT ARRAY[RANDOM(), RANDOM(), RANDOM()], n % 10 FROM generate_series(1, 100000) n;

CREATE INDEX ON items USING ivfflat (factors);

SELECT * FROM items WHERE category_id = 2 ORDER BY factors <-> '[0.5,0.5,0.5]' LIMIT 5;

EXPLAIN ANALYZE SELECT * FROM items WHERE category_id = 2 ORDER BY factors <-> '[0.5,0.5,0.5]' LIMIT 5;