pg-vector not using indexes
Problem
I have these tables: [code block] [code block] `company_fact_table` has around 16 M rows `nlp_vectors.chat_gpt_company_embeddings` has about 1.3 M rows. I am using an IVFFLAT index of class `vector_cosine_ops` in the text_vector column with `lists = 1024` When I run this query: [code block] I get this query plan: [code block] Which doesn't use the IVFFLAT index. It starts to use the index when the limit is greater than 600. Because I want to return all the data of the company (which is contained in company_fact_table) I am joining with that table like this: [code block] And its giving this query plan: [code block] Which uses the index, but I get a very slow response time (I need to get results in under 5 seconds) An approach that I thought about was to change the order in which I do the order and the join. In the original query, I join the two tables and then perform the sorting. In my suggested approach, I first sort the subquery and then join the two tables. The query would look like this: [code block] Which gives me this query plan: [code block] It gives me a much better execution time, and accuracy of the results is between 99% and 100% compared to the results of the original query. Changing the LIMIT of the query above to 600 (when it starts using the index), I get this query plan: [code block] My questions are: 1. Why does it only use the index when the limit is greater than 600, given that it seems to compute all the cosine_distances? 2. Why does the JOIN slo
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Optimize Query for pg-vector Index Usage
The query planner in PostgreSQL may choose not to use the IVFFLAT index for smaller LIMIT values because it estimates that scanning the entire table is cheaper than using the index. This behavior is influenced by the cost estimates for the operations involved. When the LIMIT is increased, the planner determines that using the index becomes more efficient. Additionally, the JOIN operation can be slow due to the large size of the `company_fact_table`, which results in a significant overhead when joining with the embeddings table.
Awaiting Verification
Be the first to verify this fix
- 1
Adjust Query Structure
Reorder the query to first filter and sort using the vector index before performing the JOIN with the company_fact_table. This allows the database to leverage the index effectively.
sqlSELECT * FROM company_fact_table c JOIN (SELECT * FROM nlp_vectors.chat_gpt_company_embeddings ORDER BY text_vector <-> 'your_vector' LIMIT 600) e ON c.id = e.company_id; - 2
Increase Index Lists
Consider increasing the number of lists in the IVFFLAT index to improve the search performance. A higher number of lists can lead to better indexing and faster query execution.
sqlALTER INDEX your_index_name SET (lists = 2048); - 3
Analyze and Vacuum Tables
Run ANALYZE and VACUUM on both tables to update the statistics and optimize the query planner's decisions. This can help the planner make better choices regarding index usage.
sqlANALYZE company_fact_table; VACUUM company_fact_table; ANALYZE nlp_vectors.chat_gpt_company_embeddings; VACUUM nlp_vectors.chat_gpt_company_embeddings; - 4
Adjust Query Cost Settings
Modify PostgreSQL's cost settings to favor index scans over sequential scans. This can be done by adjusting the 'random_page_cost' and 'seq_page_cost' parameters.
sqlSET random_page_cost = 1.1; SET seq_page_cost = 1.0; - 5
Test with Varying Limits
Run the modified query with different LIMIT values to observe the performance and ensure that the index is being utilized effectively across various scenarios.
sqlSELECT * FROM company_fact_table c JOIN (SELECT * FROM nlp_vectors.chat_gpt_company_embeddings ORDER BY text_vector <-> 'your_vector' LIMIT 1000) e ON c.id = e.company_id;
Validation
Confirm that the execution time for the modified query is consistently under 5 seconds and that the query plan shows the use of the IVFFLAT index for various LIMIT values. Use EXPLAIN ANALYZE to check the query plan.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep