postgresqlindexingembeddingvector-databasepgvector

SELECT query not using pgvector (HNSW) index


Given a table of about 1 mil. rows with columns id of type integer and embedding of type vector(2000), I ran the following query in pgAdmin query tool:

CREATE INDEX ON table USING hnsw (embedding vector_cosine_ops);

Whatever the SELECT query I ran after, I'm not seeing the index being used when prefixing the query with EXPLAIN ANALYZE.

After seeing @ankane's comments "ordering by an expression [...] won't use the index" and "Postgres only supports ASC order index scans on operators", I tried a simple SELECT id, embedding <=> $1 FROM table but it's still not using the index.

Many blog articles such as this GCP one and this other one recommend to use EXPLAIN to check if the index is used.

Thus my following questions:


Solution

  • I believe your issue could be because you are not using ORDER BY with LIMIT. Under the querying section there is this note...

    Note: Combine with ORDER BY and LIMIT to use an index

    pgvector Querying Readme