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 OF query,
  • why they agree on cMovie’s vectors, and when they wouldn’t, and
  • how to choose a similarity_function for 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_function when you create the index. Change it by dropping the index and creating it again.
  • Scores can be read in SELECT, but not used in WHERE or ORDER 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.