Using multiple indexes

How to use multiple secondary indexes.

• The table cycling.alt_stats can yield the statistics about cyclists.

  CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
    id UUID PRIMARY KEY,
    lastname text,
    birthday date,
    nationality text,
    weight float,
    w_units text,
    height float,
    first_race date,
    last_race date
  );

• Create indexes on the columns birthday and nationality.

  CREATE INDEX IF NOT EXISTS birthday_idx
  ON cycling.cyclist_alt_stats (birthday);

  CREATE INDEX IF NOT EXISTS nationality_idx
  ON cycling.cyclist_alt_stats (nationality);

• The following query attempts to retrieve the cyclists with a specified birthday and nationality. The query returns an error.

  SELECT *
  FROM cycling.cyclist_alt_stats
  WHERE birthday = '1982-01-29'
    AND nationality = 'Russia';

  InvalidRequest: Error from server: code=2200 [Invalid query]
  message="Cannot execute this query as it might involve data
  filtering and thus may have unpredictable performance.
  If you want to execute this query despite the performance
  unpredictability, use ALLOW FILTERING"

• The indexes have been created on appropriate low-cardinality columns, but the previous query still fails. Why? The answer lies with the partition key, which has not been defined. When you attempt a potentially expensive query, such as searching a range of rows, the database requires the ALLOW FILTERING directive. The error is not due to multiple indexes, but the lack of a partition key definition in the query.

  SELECT *
  FROM cycling.cyclist_alt_stats
  WHERE birthday = '1982-01-29'
    AND nationality = 'Russia'
  ALLOW FILTERING;

   id                                   | birthday   | first_race | height | last_race  | lastname | nationality | w_units | weight
  --------------------------------------+------------+------------+--------+------------+----------+-------------+---------+--------
   e0953617-07eb-4c82-8f91-3b2757981625 | 1982-01-29 | 1998-02-15 |   1.78 | 2017-04-16 |    BRUTT |      Russia |      kg |     68
  
  (1 rows)