4 ms·

> SQL vector database Cool, but does it actually return the correct results for these SQL statements, especially when ORDER BY is concerned? I.e. does it some

> SQL vector database Cool, but does it actually return the correct results for these SQL statements, especially when ORDER BY is concerned? I.e. does it somehow have a way to get a recall of 100% from its indexes?

basil-rash 3y ago
This page has a fairly helpful breakdown of the various types of indexes available, but from what I can tell it doesn’t explicitly mention the correctness trade offs made when using an index vs a pure table scan. I know in pgvector HNSW for instance is inexact. But it doesn’t really matter in a RAG context - even the embeddings themselves are stochastic. https://myscale.com/blog/everything-about-vector-indexing/ https://myscale.com/blog/everything-about-vector-indexing/
- nrjames 3y ago
  I'm not sure how much of the underlying Clickhouse feature set is available, but the Clickhouse documentation about index types is thorough and informative. https://clickhouse.com/docs/en/optimize/sparse-primary-indexes https://clickhouse.com/docs/en/optimize/sparse-primary-index...
lqhl 3y ago
MyScaleDB utilizes approximate nearest neighbors (ANN) algorithms such as ScaNN, HNSW, and IVF. As a result, it may not achieve a 100% recall rate. However, depending on the search parameters used, it can attain recall rates of up to 95% or even 99%. Considering that embedding vectors represent a lossy compression of the original text or images, is achieving a 100% recall necessary? I am interested in understanding its practical implications. Disclaimer: I am an employee at MyScale.
- mattashii 3y ago
  > Considering that embedding vectors represent a lossy compression of the original text or images, is achieving a 100% recall necessary? For the app, maybe not. But as a database absolutist, I think you must be able to dump all rows of a table with WITH limit_result AS (SELECT *, {similarity} AS metric FROM table ORDER BY {similarity} ASC LIMIT 10), dist AS (SELECT MAX(metric) AS max_m FROM limit_result) SELECT *, {similarity} AS metric FROM table, dist WHERE {similarity} > dist.max_m UNION ALL SELECT * FROM limit_result ... assuming that the ordered values are unique across the table and fully sortable A recall of <100% may skip some rows in the limit_result, which then also won't show up in the main table's scan result, thus potentially corrupting a data dump process that uses sorted output.

basil-rash 3y ago

This page has a fairly helpful breakdown of the various types of indexes available, but from what I can tell it doesn’t explicitly mention the correctness trade offs made when using an index vs a pure table scan. I know in pgvector HNSW for instance is inexact. But it doesn’t really matter in a RAG context - even the embeddings themselves are stochastic. https://myscale.com/blog/everything-about-vector-indexing/ https://myscale.com/blog/everything-about-vector-indexing/

nrjames 3y ago
I'm not sure how much of the underlying Clickhouse feature set is available, but the Clickhouse documentation about index types is thorough and informative. https://clickhouse.com/docs/en/optimize/sparse-primary-indexes https://clickhouse.com/docs/en/optimize/sparse-primary-index...

lqhl 3y ago

MyScaleDB utilizes approximate nearest neighbors (ANN) algorithms such as ScaNN, HNSW, and IVF. As a result, it may not achieve a 100% recall rate. However, depending on the search parameters used, it can attain recall rates of up to 95% or even 99%. Considering that embedding vectors represent a lossy compression of the original text or images, is achieving a 100% recall necessary? I am interested in understanding its practical implications. Disclaimer: I am an employee at MyScale.

mattashii 3y ago
> Considering that embedding vectors represent a lossy compression of the original text or images, is achieving a 100% recall necessary? For the app, maybe not. But as a database absolutist, I think you must be able to dump all rows of a table with WITH limit_result AS (SELECT *, {similarity} AS metric FROM table ORDER BY {similarity} ASC LIMIT 10), dist AS (SELECT MAX(metric) AS max_m FROM limit_result) SELECT *, {similarity} AS metric FROM table, dist WHERE {similarity} > dist.max_m UNION ALL SELECT * FROM limit_result ... assuming that the ordered values are unique across the table and fully sortable A recall of <100% may skip some rows in the limit_result, which then also won't show up in the main table's scan result, thus potentially corrupting a data dump process that uses sorted output.