Why does Cassandra give the same movie three different closeness scores? When
you ask a vector search how close two movies are, Apache Cassandra® 5.0 can
answer in three ways: cosine, Euclidean distance or dot product. This post
selects all three beside an ANN OF query, shows why they agree on cMovie’s
vectors, and explains when they wouldn’t.
This is the seventh instalment in the Cassandra 5.0 series where I’ll show you how to build an AI movie recommender app with Python and TypeScript.
One query, three scores
Let’s ask the cMovie database for “an old man and his machine become
unlikely friends”, and have Cassandra score each result three ways. In the
statement, [...] stands for the 3,072 numbers that represent the phrase. We’ll
see where they come from shortly.
SELECT title, release_year,
similarity_cosine(plot_embedding, [...]) AS cosine,
similarity_euclidean(plot_embedding, [...]) AS euclidean,
similarity_dot_product(plot_embedding, [...]) AS dot_product
FROM movies
ORDER BY plot_embedding ANN OF [...] LIMIT 5;
title | release_year | cosine | euclidean | dot_product
----------------+--------------+----------+-----------+-------------
The Creator | 2023 | 0.791831 | 0.54565 | 0.791831
The Iron Giant | 1999 | 0.790864 | 0.544501 | 0.790864
Contact | 1997 | 0.788964 | 0.542257 | 0.788964
M3GAN | 2022 | 0.784302 | 0.536829 | 0.784302
Companion | 2025 | 0.780267 | 0.532218 | 0.780267
(5 rows)
Warnings :
Top-K queries can only be run with consistency level ONE / LOCAL_ONE / NODE_LOCAL. Consistency level LOCAL_QUORUM was requested. Downgrading the consistency level to LOCAL_ONE.
The warning under the results isn’t a problem with the query. The console sends
statements at a high consistency level, LOCAL_QUORUM, which means several
copies of the data must answer. A vector query can only run at a low level, where
one copy answers, so Cassandra runs it at LOCAL_ONE and says so. The blocks
below leave it out to keep them short.
Two things stand out. The cosine and dot product columns hold the same numbers, and the Euclidean column sits lower. The movies also come back in the same order down all three columns. So which score is “right”, and does the function you pick change what your users see? On cMovie’s data, it doesn’t, and the rest of this post explains why.
What you’ll learn and what you need
You’ll learn:
- how to read the three similarity scores beside an
ANN OFquery, - why they agree on cMovie’s vectors, and when they wouldn’t, and
- how to choose a
similarity_functionfor an index, and what the scores can’t do in a query.
A quick recap, in case you skipped earlier weeks. An embedding is a list of
numbers that captures what a text means, and the plot_embedding column holds
one for every movie’s plot. ANN OF asks the vector index for the stored
embeddings closest to the one you give it. In week
3 we created that index with
similarity_function set to COSINE and promised to explain the choice. This is
that post.
We’ll continue to use the same Astra
DB
and the movies table from week 3, with every plot embedded and the vector index
built.
For full disclosure, I’m an Apache Cassandra committer and a Developer Advocate at DataStax, now an IBM company. We’re using Astra DB for convenience so you can focus on building apps as a developer without worrying about how to install or configure a cluster, but the examples in this post are verified to run on Cassandra 5.0.
You also need the repo and virtual environment from the series setup
page
and your GEMINI_API_KEY in the .env file, the Google AI Studio key from
section 3 of the setup
page.
This post sends one phrase to Gemini, so the same note about Google’s
terms applies: only type phrases you’d
be happy for Google to see.
We run every statement in the Astra CQL
console, the
page in Astra DB where you type Cassandra Query Language (CQL) statements.
Start each console session with USE default_keyspace;. A keyspace is the
container that holds your tables, and this statement points the console at the
one where the loader put the movies table. If you skip it, the console
rejects the next statement with the error No keyspace has been specified. If
your database has been idle for a while, check that it isn’t hibernated first.
Section 2.4 of the setup
page
shows how.
Get the scores
The CQL console can’t call Gemini, so we use the helper from week 3 to turn the
phrase into a vector. Its new --scores option adds the three score columns for
you. From the repo’s root directory, run:
python tools/embed_query.py "an old man and his machine become unlikely friends" --scores
The helper prints a complete SELECT statement, which is long: it writes the
3,072 numbers into each of the three score columns as well as the ANN OF
clause, so the statement runs to about 150 KB of text. Copy it as it is and paste
it into the console. The run costs one text of your free tier allowance.
Why the three agree
Here is each function in plain words. Cosine compares the direction two vectors point in and ignores how long they are. Dot product multiplies the two vectors number by number and adds the results, so length counts. Euclidean measures the straight-line distance between the two vectors’ ends, so length counts there too. Each function scales its score in its own way, which is why the Euclidean column reads lower than the others. The scores are for comparing results with each other, not percentages.
If every vector has length 1, the three become views of the same thing, the angle
between two vectors, so they rank movies in the same order. That is the case for
cMovie. I measured the length of all 1,000 stored plot_embedding vectors, and
every one is between 0.9999998 and 1.0000002. That matches what we saw in week 3:
Gemini’s API returns vectors already scaled to unit length. It also explains why
the cosine and dot product columns show the same numbers.
Cassandra doesn’t make vectors length 1 for you. In the 5.0
source,
which I read at commit 81b458017d, the only check on a vector before it enters
the index rejects an all-zero vector when the function is cosine. Nothing scales
a vector. Cassandra scores the numbers you give it.
So on cMovie any of the three functions would do, and COSINE, the default, is
a safe choice. That’s the answer to the question week 3 left open. The next
section shows what changes when your vectors aren’t length 1.
When they disagree
Let’s build a tiny table where the vectors have different lengths. It lives
beside movies, so nothing here touches the index you built in week 3. Each
vector column holds the same data and has its own SAI vector index with a
different function, because one column can have only one vector index. Run these
statements in the console:
CREATE TABLE IF NOT EXISTS similarity_demo (
id text PRIMARY KEY,
v_cosine vector<float, 3>,
v_dot_product vector<float, 3>,
v_euclidean vector<float, 3>
);
Each index names its function in the similarity_function option:
CREATE CUSTOM INDEX IF NOT EXISTS similarity_demo_cosine_idx
ON similarity_demo (v_cosine) USING 'StorageAttachedIndex'
WITH OPTIONS = {'similarity_function': 'COSINE'};
CREATE CUSTOM INDEX IF NOT EXISTS similarity_demo_dot_product_idx
ON similarity_demo (v_dot_product) USING 'StorageAttachedIndex'
WITH OPTIONS = {'similarity_function': 'DOT_PRODUCT'};
CREATE CUSTOM INDEX IF NOT EXISTS similarity_demo_euclidean_idx
ON similarity_demo (v_euclidean) USING 'StorageAttachedIndex'
WITH OPTIONS = {'similarity_function': 'EUCLIDEAN'};
Now three rows, and none has length 1. Row A points exactly the way our
query will point, but it’s short. Row B is long and points slightly off. Row
C is close to the query in space, and points a little off too:
INSERT INTO similarity_demo (id, v_cosine, v_dot_product, v_euclidean)
VALUES ('A', [0.2, 0.0, 0.0], [0.2, 0.0, 0.0], [0.2, 0.0, 0.0]);
INSERT INTO similarity_demo (id, v_cosine, v_dot_product, v_euclidean)
VALUES ('B', [10.0, 1.0, 0.0], [10.0, 1.0, 0.0], [10.0, 1.0, 0.0]);
INSERT INTO similarity_demo (id, v_cosine, v_dot_product, v_euclidean)
VALUES ('C', [0.9, 0.3, 0.0], [0.9, 0.3, 0.0], [0.9, 0.3, 0.0]);
The index takes a few seconds to build after you create it. On Astra DB, mine was
ready in under 20 seconds. If a query below fails because the index isn’t
available yet, wait a moment and run it again. Let’s ask each index for the rows
closest to [1.0, 0.0, 0.0]:
SELECT id FROM similarity_demo ORDER BY v_cosine ANN OF [1.0, 0.0, 0.0] LIMIT 3;
id
----
A
B
C
(3 rows)
SELECT id FROM similarity_demo ORDER BY v_dot_product ANN OF [1.0, 0.0, 0.0] LIMIT 3;
id
----
B
C
A
(3 rows)
SELECT id FROM similarity_demo ORDER BY v_euclidean ANN OF [1.0, 0.0, 0.0] LIMIT 3;
id
----
C
A
B
(3 rows)
Same rows, same query, three different winners. Cosine picks A, because it
points exactly the right way and length doesn’t matter. Dot product picks B,
because it rewards a long vector. Euclidean picks C, because it’s the nearest
in space. I got the same orders on Astra DB and on Cassandra 5.0.9.
The same thing happens at full size. As a test, I rescaled copies of cMovie’s
vectors to lengths between 0.2 and 5.0 and built all three indexes. The top
results changed with the function: cosine gave Her, Sleepless in Seattle and
500 Days of Summer, dot product gave Her, Contact and The Matrix, and
Euclidean started with Sleepless in Seattle, Love Actually and The Theory of
Everything. That was a test on rescaled copies, not on your movies table.
Here is why this matters to you. If you swap in another embedding model, as the model swap section in week 3 describes, check whether its vectors have length 1 before you choose a function. If they don’t, the function decides what ranks first. Your model’s docs usually say which function it was trained for, and I recommend following them.
Set the function on an index
You’ve seen the option three times already: WITH OPTIONS = {'similarity_function': '...'}.
The valid values are COSINE, DOT_PRODUCT and EUCLIDEAN, and the default is
COSINE. The value is case insensitive. I checked the option parsing in the
5.0
source.
There is no way to change the function on an existing index. You drop the index and create it again. A column holds only one vector index, so drop first. Let’s change the dot product index, starting with a typo to see what Cassandra says:
DROP INDEX similarity_demo_dot_product_idx;
CREATE CUSTOM INDEX similarity_demo_dot_product_idx
ON similarity_demo (v_dot_product) USING 'StorageAttachedIndex'
WITH OPTIONS = {'similarity_function': 'MANHATTAN'};
InvalidRequest: Error from server: code=2200 [Invalid query] message="Similarity function MANHATTAN was not recognized for index null. Valid values are: EUCLIDEAN, DOT_PRODUCT, COSINE"
The error lists the three valid values, so it’s the quickest way to check the list. Now create the index again with a valid one:
CREATE CUSTOM INDEX similarity_demo_dot_product_idx
ON similarity_demo (v_dot_product) USING 'StorageAttachedIndex'
WITH OPTIONS = {'similarity_function': 'COSINE'};
On cMovie, rebuilding is cheap. On a large table it isn’t, so choose the function
before you load millions of rows. That’s also why I didn’t have us change the
index on movies: weeks 8 and 10 rely on it.
The gotcha: scores can’t filter or sort
The three functions work in the SELECT list beside ANN OF, as we saw. They
don’t work in WHERE or ORDER BY. Let’s try to keep only the movies scoring
above 0.8:
SELECT title FROM movies
WHERE similarity_cosine(plot_embedding, [...]) > 0.8 LIMIT 5;
SyntaxException: line 1:48 no viable alternative at input '(' (SELECT title FROM movies WHERE [similarity_cosine](...)
That looks like a typo, but it isn’t. CQL accepts only a column name on the left
of a WHERE condition, and only a column name, optionally with ANN OF, in
ORDER BY, so a function call is a syntax error. Unfortunately there is no
CQL-only way to say “scores above 0.8”. ORDER BY ... ANN OF already sorts by
the index’s own function, and a threshold has to be applied in your application
code after the rows come back. Week 8 builds exactly
that in TypeScript.
Let’s tidy up the scratch table:
DROP TABLE similarity_demo;
Recap
- Cosine, dot product and Euclidean score the same pair of vectors differently.
You can select all three beside
ANN OF. - On cMovie they agree, because every stored vector has length 1. They disagree when lengths vary.
- Set the function with
similarity_functionwhen you create the index. Change it by dropping the index and creating it again. - Scores can be read in
SELECT, but not used inWHEREorORDER BY.
Info
Vector indexes are experimental in Cassandra 5.0. Storage Attached Index (SAI) vector indexes are not recommended for production use yet. Astra DB doesn’t print the 5.0 warning, but the restrictions apply there too. Week 9 compares 5.0 and Astra DB in full.
Next week, we build “more like this” recommendations in TypeScript. It reads the similarity scores from this post and applies a threshold in code, since CQL can’t filter on them.
Try your own phrase with --scores, then change the vectors in the scratch table
and see which function wins. The statements from this post are in
tutorials/week-07/
in the series repo. If you want next week’s post when it lands, follow me using
the social buttons on the 8567.me homepage.
Movie facts from Wikidata,
CC0. Plot text is
fetched by the loader from each movie’s English Wikipedia article,
CC BY-SA 4.0, and the
wikipedia_url column links to each article.
Apache Cassandra, Cassandra, Apache, the Apache logo, and the Apache Cassandra project logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.
