Using multiple indexes

How to use multiple secondary indexes.

Indexes can be created on multiple columns and used in queries. The general rule about cardinality applies to all columns indexed. In a production environment, certain columns might not be good choices, depending on their cardinality.


  • The table cycling.alt_stats can yield the statistics about cyclists.
    CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
      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.
    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'
     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)