|
1 | | -#Speeding up vector recall by 5x with HNSW |
2 | | - |
3 | | - |
4 | | - |
5 | | -Silas Marvin |
6 | | - |
7 | | -October 2, 2023 |
8 | | - |
9 | | -PostgresML makes it easy to use machine learning with your database and to scale workloads horizontally in our cloud. Our SDK makes it even easier. |
10 | | - |
11 | | - |
12 | | - |
13 | | -_HNSW (hierarchical navigable small worlds) is an indexing method that greatly improves vector recall_ |
14 | | - |
15 | | -##Introducing HNSW |
16 | | - |
17 | | -Underneath the hood our SDK utilizes[pgvector](https://github.com/pgvector/pgvector) to store, index, and recall vectors. Up until this point our SDK used IVFFlat indexing to divide vectors into lists, search a subset of those lists, and return the closest vector matches. |
18 | | - |
19 | | -While the IVFFlat indexing method is fast, it is not as fast as HNSW. Thanks to the latest update of[pgvector](https://github.com/pgvector/pgvector) our SDK now utilizes HNSW indexing, creating multi-layer graphs instead of lists and removing the required training step IVFFlat imposed. |
20 | | - |
21 | | -The results are not disappointing. |
22 | | - |
23 | | -##Comparing HNSW and IVFFlat |
24 | | - |
25 | | -In one of our previous posts: Tuning vector recall while generating query embeddings in the database we were working on a dataset with over 5 million Amazon Movie Reviews, and after embedding the reviews, performed semantic similarity search to get the closest 5 reviews. |
26 | | - |
27 | | -Let's run that query again: |
28 | | - |
29 | | -!!! generic |
30 | | - |
31 | | -!!! code\_block time="89.118 ms" |
32 | | - |
33 | | -```postgresql |
34 | | -WITH request AS ( |
35 | | - SELECT pgml.embed( |
36 | | - 'intfloat/e5-large', |
37 | | - 'query: Best 1980''s scifi movie' |
38 | | - )::vector(1024) AS embedding |
39 | | -) |
40 | | -
|
41 | | -SELECT |
42 | | - id, |
43 | | - 1 - ( |
44 | | - review_embedding_e5_large <=> ( |
45 | | - SELECT embedding FROM request |
46 | | - ) |
47 | | - ) AS cosine_similarity |
48 | | -FROM pgml.amazon_us_reviews |
49 | | -ORDER BY review_embedding_e5_large <=> (SELECT embedding FROM request) |
50 | | -LIMIT 5; |
51 | | -``` |
52 | | - |
53 | | -!!! |
54 | | - |
55 | | -!!! results |
56 | | - |
57 | | -| review\_body| product\_title| star\_rating| total\_votes| cosine\_similarity| |
58 | | -| ------------------------------------------------| -------------------------------------------------------------| ------------| ------------| ------------------| |
59 | | -| best 80s SciFi movie ever| The Adventures of Buckaroo Banzai Across the Eighth Dimension| 5| 1| 0.9495371273162286| |
60 | | -| the best of 80s sci fi horror!| The Blob| 5| 2| 0.9097434758143605| |
61 | | -| Three of the best sci-fi movies of the seventies| Sci-Fi: Triple Feature (BD)\[Blu-ray]| 5| 0| 0.9008723412875651| |
62 | | -| best sci fi movie ever| The Day the Earth Stood Still (Special Edition)\[Blu-ray]| 5| 2| 0.8943620968858654| |
63 | | -| Great Science Fiction movie| Bloodsport / Timecop (Action Double Feature)\[Blu-ray]| 5| 0| 0.894282454374093| |
64 | | - |
65 | | -!!! |
66 | | - |
67 | | -!!! |
68 | | - |
69 | | -This query utilized IVFFlat indexing and queried through over 5 million rows in 89.118ms. Pretty fast! |
70 | | - |
71 | | -Let's drop our IVFFlat index and create an HNSW index. |
72 | | - |
73 | | -!!! generic |
74 | | - |
75 | | -!!! code\_block time="10255099.233 ms (02:50:55.099)" |
76 | | - |
77 | | -```postgresql |
78 | | -DROP INDEX index_amazon_us_reviews_on_review_embedding_e5_large; |
79 | | -CREATE INDEX CONCURRENTLY ON pgml.amazon_us_reviews USING hnsw (review_embedding_e5_large vector_cosine_ops); |
80 | | -``` |
81 | | - |
82 | | -!!! |
83 | | - |
84 | | -!!! results |
85 | | - |
86 | | -!!! |
87 | | - |
88 | | -!!! |
89 | | - |
90 | | -Now let's try the query again utilizing the new HNSW index we created. |
91 | | - |
92 | | -!!! generic |
93 | | - |
94 | | -!!! code\_block time="17.465 ms" |
95 | | - |
96 | | -```postgresql |
97 | | -WITH request AS ( |
98 | | - SELECT pgml.embed( |
99 | | - 'intfloat/e5-large', |
100 | | - 'query: Best 1980''s scifi movie' |
101 | | - )::vector(1024) AS embedding |
102 | | -) |
103 | | -
|
104 | | -SELECT |
105 | | - id, |
106 | | - 1 - ( |
107 | | - review_embedding_e5_large <=> ( |
108 | | - SELECT embedding FROM request |
109 | | - ) |
110 | | - ) AS cosine_similarity |
111 | | -FROM pgml.amazon_us_reviews |
112 | | -ORDER BY review_embedding_e5_large <=> (SELECT embedding FROM request) |
113 | | -LIMIT 5; |
114 | | -``` |
115 | | - |
116 | | -!!! |
117 | | - |
118 | | -!!! results |
119 | | - |
120 | | -| review\_body| product\_title| star\_rating| total\_votes| cosine\_similarity| |
121 | | -| ------------------------------| -------------------------------------------------------------| ------------| ------------| ------------------| |
122 | | -| best 80s SciFi movie ever| The Adventures of Buckaroo Banzai Across the Eighth Dimension| 5| 1| 0.9495371273162286| |
123 | | -| the best of 80s sci fi horror!| The Blob| 5| 2| 0.9097434758143605| |
124 | | -| One of the Better 80's Sci-Fi| Krull (Special Edition)| 3| 5| 0.9093884940741694| |
125 | | -| Good 1980s movie| Can't Buy Me Love| 4| 0| 0.9090294438721961| |
126 | | -| great 80's movie| How I Got Into College| 5| 0| 0.9016508795301296| |
127 | | - |
128 | | -!!! |
129 | | - |
130 | | -!!! |
131 | | - |
132 | | -Not only are the results better (the`cosine_similarity` is higher overall), but HNSW is over 5x faster, reducing our search and embedding time to 17.465ms. |
133 | | - |
134 | | -This is a massive upgrade to the recall speed utilized by our SDK and greatly improves overall performance. |
135 | | - |
136 | | -For a deeper dive into HNSW checkout[Jonathan Katz's excellent article on HNSW in pgvector](https://jkatz05.com/post/postgres/pgvector-hnsw-performance/). |
| 1 | +--- |
| 2 | +description:Our blog posts |
| 3 | +--- |
| 4 | + |
| 5 | +#Home |
| 6 | + |
| 7 | +*[speeding-up-vector-recall-by-5x-with-hnsw.md](speeding-up-vector-recall-by-5x-with-hnsw.md"mention") |
| 8 | +*[how-to-improve-search-results-with-machine-learning.md](how-to-improve-search-results-with-machine-learning.md"mention") |
| 9 | +*[pgml-chat-a-command-line-tool-for-deploying-low-latency-knowledge-based-chatbots-part-i.md](pgml-chat-a-command-line-tool-for-deploying-low-latency-knowledge-based-chatbots-part-i.md"mention") |
| 10 | +*[announcing-support-for-aws-us-east-1-region.md](announcing-support-for-aws-us-east-1-region.md"mention") |
| 11 | +*[how-we-generate-javascript-and-python-sdks-from-our-canonical-rust-sdk.md](how-we-generate-javascript-and-python-sdks-from-our-canonical-rust-sdk.md"mention") |
| 12 | +*[announcing-gptq-and-ggml-quantized-llm-support-for-huggingface-transformers.md](announcing-gptq-and-ggml-quantized-llm-support-for-huggingface-transformers.md"mention") |
| 13 | +*[making-postgres-30-percent-faster-in-production.md](making-postgres-30-percent-faster-in-production.md"mention") |
| 14 | +*[mindsdb-vs-postgresml.md](mindsdb-vs-postgresml.md"mention") |
| 15 | +*[introducing-postgresml-python-sdk-build-end-to-end-vector-search-applications-without-openai-and-pin.md](introducing-postgresml-python-sdk-build-end-to-end-vector-search-applications-without-openai-and-pin.md"mention") |
| 16 | +*[postgresml-raises-usd4.7m-to-launch-serverless-ai-application-databases-based-on-postgres.md](postgresml-raises-usd4.7m-to-launch-serverless-ai-application-databases-based-on-postgres.md"mention") |
| 17 | +*[pg-stat-sysinfo-a-postgres-extension-for-querying-system-statistics.md](pg-stat-sysinfo-a-postgres-extension-for-querying-system-statistics.md"mention") |
| 18 | +*[postgresml-as-a-memory-backend-to-auto-gpt.md](postgresml-as-a-memory-backend-to-auto-gpt.md"mention") |
| 19 | +*[which-database-that-is-the-question.md](which-database-that-is-the-question.md"mention") |
| 20 | +*[personalize-embedding-results-with-application-data-in-your-database.md](personalize-embedding-results-with-application-data-in-your-database.md"mention") |
| 21 | +*[tuning-vector-recall-while-generating-query-embeddings-in-the-database.md](tuning-vector-recall-while-generating-query-embeddings-in-the-database.md"mention") |
| 22 | +*[generating-llm-embeddings-with-open-source-models-in-postgresml.md](generating-llm-embeddings-with-open-source-models-in-postgresml.md"mention") |
| 23 | +*[scaling-postgresml-to-1-million-requests-per-second.md](scaling-postgresml-to-1-million-requests-per-second.md"mention") |
| 24 | +*[backwards-compatible-or-bust-python-inside-rust-inside-postgres.md](backwards-compatible-or-bust-python-inside-rust-inside-postgres.md"mention") |
| 25 | +*[postgresml-is-moving-to-rust-for-our-2.0-release.md](postgresml-is-moving-to-rust-for-our-2.0-release.md"mention") |
| 26 | +*[postgresml-is-8-40x-faster-than-python-http-microservices.md](postgresml-is-8-40x-faster-than-python-http-microservices.md"mention") |
| 27 | +*[postgres-full-text-search-is-awesome.md](postgres-full-text-search-is-awesome.md"mention") |
| 28 | +*[oxidizing-machine-learning.md](oxidizing-machine-learning.md"mention") |
| 29 | +*[optimizing-semantic-search-results-with-an-xgboost-model-in-your-database.md](optimizing-semantic-search-results-with-an-xgboost-model-in-your-database.md"mention") |
| 30 | +*[data-is-living-and-relational.md](data-is-living-and-relational.md"mention") |