Multiple indexes matched with OR

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';

Several indexes are created for the table to demonstrate how to query for matches on more than one column.

Query for a match on either one column or the other:

SELECT * FROM cycling.comments_vs 
  WHERE created_at='2017-03-21 21:11:09.999' 
  OR created_at='2017-03-22 01:16:59.001';

Results

 id                                   | created_at                      | comment                           | comment_vector               | commenter | record_id
--------------------------------------+---------------------------------+-----------------------------------+------------------------------+-----------+--------------------------------------
 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
 c7fceba0-c141-4207-9494-a29f9809de6f | 2017-03-22 01:16:59.001000+0000 |     Great snacks at all reststops |  [0.1, 0.4, 0.1, 0.52, 0.09] |       Amy | 70143d10-4084-11ef-893f-0fd142cea825

(2 rows)

Multiple indexes matched with OR

Was this helpful?

Give Feedback