<h3 id="summary-problem-motivation">Problem/Motivation</h3> <p>For cosine similarity, <code>PostgresPgvectorClient::vectorSearch()</code> builds this query:</p> <pre>SELECT (1-subquery.real_distance) as distance, ... FROM (SELECT embedding &lt;=&gt; ?::vector as real_distance, ... FROM {table}) as subquery ORDER BY distance DESC LIMIT ... OFFSET ...;</pre> <p>pgvector can use an HNSW or IVFFlat index only when the query sorts by the distance operator itself, in ascending order (<code>ORDER BY embedding &lt;=&gt; ?::vector</code>). Sorting by <code>1 - distance DESC</code> gives the same order, but the planner can't match it to the index. So every cosine search reads the whole table, even when the table has a cosine HNSW index.</p> <p>When the table is in the cache, the full scan is fast enough that nobody notices. When the cache is cold, for example on a shared database after an idle period, one search can take about 10 seconds on a table with about 10,000 chunks of 1024 dimensions. With the index, the same search reads only a small part of the table.</p> <p>The same problem was fixed in ai_vdb_provider_postgres in <a href="https://www.drupal.org/project/ai_vdb_provider_postgres/issues/3609274">#3609274</a> (commit 62b4b9f, released in 1.0.0-alpha4). This module took its Postgres code from that project before the fix, so it still has the old query. The query is the same in 1.3.x, 1.4.x and 2.0.x.</p> <h3 id="summary-steps-reproduce">Steps to reproduce</h3> <ol> <li>Set up a Search API index on the amazee.io vector database with the cosine similarity metric, and index some content.</li> <li>Add a cosine HNSW index to the collection table: <code>CREATE INDEX ON {table} USING hnsw (embedding vector_cosine_ops);</code></li> <li>Run <code>EXPLAIN</code> on the query that <code>vectorSearch()</code> builds. The plan shows a Seq Scan and a Sort, with no Index Scan, even with <code>SET enable_seqscan = off</code>.</li> <li>Run <code>EXPLAIN</code> on the same query with <code>ORDER BY real_distance</code> in the inner query. The plan can now use the index.</li> </ol> <h3 id="summary-proposed-resolution">Proposed resolution</h3> <p>For cosine similarity, sort and limit inside the subquery by the raw distance in ascending order. Then compute <code>1 - real_distance</code> in the outer query, so the returned score stays the same. Apply the change to both query paths, with and without filters. Results and scores do not change, so sites don't need to re-index.</p> <p>Euclidean distance and inner product already sort by the operator directly, so they don't need a change.</p> <h3 id="summary-remaining-tasks">Remaining tasks</h3> <ul> <li>Fix the query on 1.3.x.</li> <li>Confirm with <code>EXPLAIN</code> that a cosine search uses the HNSW index.</li> <li>Port the fix to 1.4.x and 2.0.x.</li> </ul> <h3 id="summary-related-issues">Related issues</h3> <ul> <li><a href="https://www.drupal.org/project/ai_vdb_provider_postgres/issues/3609274">#3609274</a>: the same fix in ai_vdb_provider_postgres.</li> <li><a href="https://www.drupal.org/project/ai_provider_amazeeio/issues/3559138">#3559138</a>: this bug came from the copied pgvector code drifting from upstream.</li> <li><a href="https://www.drupal.org/project/ai_provider_amazeeio/issues/3586240">#3586240</a>: changes the same file on 1.4.x and 2.0.x.</li> </ul> <h3 id="summary-ui-changes">User interface changes</h3> <p>None.</p> <h3 id="summary-api-changes">API changes</h3> <p>None.</p> <h3 id="summary-data-model-changes">Data model changes</h3> <p>None.</p>