Query with SAI

This page demonstrates how to run queries on tables with SAI indexes.

To query with SAI, you need a keyspace with a table and one or more SAI indexes.

If you haven’t created an SAI index yet, see the SAI quickstart to learn how to create multiple indexes based on non-primary key columns.

Query operators supported for SAI

SAI only supports SELECT queries, but not UPDATE or DELETE queries.

SAI supports the following query operators for tables with SAI indexes:

Numerics: =, <, >, ⇐, >=, AND, IN, OR (for vector databases)
Strings: =, AND, IN
Collections: = CONTAINS, CONTAINS KEY

SAI does not supports the following query operators for tables with SAI indexes:

Strings or Numerics: LIKE, OR (for non-vector databases)

Vector search with SAI

For an example and explanation of vector search with CQL, see Vector search quickstart.

Single index match on a column

This example uses the following table and indexes:

CREATE TABLE IF NOT EXISTS cycling.comments_vs (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  comment_vector VECTOR <FLOAT, 5>,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
CREATE CUSTOM INDEX commenter_idx ON cycling.comments_vs (commenter)
  USING 'StorageAttachedIndex' 
  WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};
CREATE CUSTOM INDEX created_at_idx ON cycling.comments_vs (created_at) 
  USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX comment_ann_idx ON cycling.comments_vs(comment_vector) 
  USING 'StorageAttachedIndex';

The column commenter is not the partition key in this table, so an index is required to query on it.

Query for a match on that column:

SELECT * FROM cycling.comments_vs WHERE commenter = 'Alex';

Results

 id                                   | created_at                      | comment                                | comment_vector                | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+-------------------------------+-----------+--------------------------------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 | LATE RIDERS SHOULD NOT DELAY THE START |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Alex | 700ebed0-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 |      Second rest stop was out of water |  [0.99, 0.5, 0.99, 0.1, 0.34] |      Alex | 700d8650-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.000000+0000 | Raining too hard should have postponed | [0.45, 0.09, 0.01, 0.2, 0.11] |      Alex | 6fe2f3e0-4084-11ef-893f-0fd142cea825

(3 rows)

Note that a failure will occur if you try this query before creating the index:

SELECT * FROM cycling.comments_vs WHERE commenter = 'Alex';

Results

 id                                   | created_at                      | comment                                | comment_vector                | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+-------------------------------+-----------+--------------------------------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 | LATE RIDERS SHOULD NOT DELAY THE START |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Alex | 700ebed0-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 |      Second rest stop was out of water |  [0.99, 0.5, 0.99, 0.1, 0.34] |      Alex | 700d8650-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.000000+0000 | Raining too hard should have postponed | [0.45, 0.09, 0.01, 0.2, 0.11] |      Alex | 6fe2f3e0-4084-11ef-893f-0fd142cea825

(3 rows)

Single index match on a column with options

SAI options include case_sensitive, ascii, and normalize.

This example uses the following table and indexes:

CREATE TABLE IF NOT EXISTS cycling.comments_vs (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  comment_vector VECTOR <FLOAT, 5>,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
CREATE CUSTOM INDEX commenter_idx ON cycling.comments_vs (commenter)
  USING 'StorageAttachedIndex' 
  WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};

The column commenter is not the partition key in this table, so an index is required to query on it. If we want to check commenter as a case-sensitive value, we can use the case_sensitive option set to true.

Note that no results are returned if you use an inappropriately case-sensitive value in the query:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs 
  WHERE commenter : 'alex';

Results

comment               | comment_vector               | commenter
-----------------------+------------------------------+-----------
 Rest stops were great | [0.99, 0.5, 0.99, 0.1, 0.34] |      alex

(1 rows)

When we switch the case of the cyclist’s name to match the case in the index, the query succeeds:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs 
  WHERE commenter : 'Alex';

Results

 comment                                | comment_vector                | commenter
----------------------------------------+-------------------------------+-----------
 LATE RIDERS SHOULD NOT DELAY THE START |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Alex
      Second rest stop was out of water |  [0.99, 0.5, 0.99, 0.1, 0.34] |      Alex
 Raining too hard should have postponed | [0.45, 0.09, 0.01, 0.2, 0.11] |      Alex
      LATE RIDERS SHOULD NOT BE ALLOWED |  [0.9, 0.54, 0.12, 0.1, 0.95] | alexander

(4 rows)

Query with SAI

Query operators supported for SAI

Vector search with SAI

Single index match on a column

Single index match on a column with options

Case-sensitivity

See also

Was this helpful?

Give Feedback