SELECT query not using (HNSW) index
Problem
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: [code block] 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](https://github.com/pgvector/pgvector/issues/216#issuecomment-1668244772)" 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: - How can I check that the index is actually built? With `EXPLAIN` I can see that even with the simplest query does not use the index, thus I'm even wondering if the index has actually been created (despite the `CREATE INDEX` having "returned successfully in 1 hr 6 min.") - How to use the HNSW index with a `SELECT` statement? - Assuming an index has been created, what happens if I rerun a `CREATE INDEX` statement? If I create an HNSW index, I assume it overwrites the existing index (is it?), does it overwrite if I create an `IVFFLAT` index instead? Related issues: #174, #216
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Ensure HNSW Index is Used in SELECT Queries
The HNSW index may not be utilized due to the query structure, or the index may not have been created successfully. PostgreSQL requires specific query formats to leverage vector indexes effectively, and if the index is not detected, it may indicate a problem with its creation or the query's compatibility with the index.
Awaiting Verification
Be the first to verify this fix
- 1
Verify Index Creation
Check if the HNSW index has been created successfully by querying the pg_indexes system catalog. This will confirm the existence of the index on the specified table.
sqlSELECT * FROM pg_indexes WHERE tablename = 'your_table_name'; - 2
Use Correct Query Format
To utilize the HNSW index, ensure your SELECT query uses the correct syntax. Use the '<=>' operator for similarity search and avoid any ORDER BY clauses that could prevent index usage.
sqlSELECT id FROM your_table_name ORDER BY embedding <=> $1 LIMIT 10; - 3
Recreate the Index if Necessary
If the index is not being used, consider dropping and recreating it. Use the CREATE INDEX statement specifically for HNSW. Note that creating a new index will replace any existing index with the same name.
sqlDROP INDEX IF EXISTS your_hnsw_index_name; CREATE INDEX your_hnsw_index_name ON your_table_name USING hnsw (embedding); - 4
Test Query with EXPLAIN ANALYZE
After ensuring the index is created and the query is formatted correctly, run the query prefixed with EXPLAIN ANALYZE to confirm that the index is being utilized.
sqlEXPLAIN ANALYZE SELECT id FROM your_table_name ORDER BY embedding <=> $1 LIMIT 10; - 5
Check for Index Overwrites
Understand that creating a new index with the same name will overwrite the existing index. If you want to switch to a different index type (e.g., IVFFLAT), drop the existing index before creating the new one.
sqlDROP INDEX IF EXISTS your_index_name; CREATE INDEX your_index_name ON your_table_name USING ivfflat (embedding);
Validation
Confirm that the index is being used by running the SELECT query with EXPLAIN ANALYZE and checking for an index scan in the output. Additionally, verify that the index exists in the pg_indexes catalog.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep