Range Index

This page describes configuring the range index for Apache Pinot.

Range indexing allows you to get better performance for queries that involve filtering over a range.

When to use

Use a range index when queries filter on a column with range predicates such as >, <, >=, <=, or BETWEEN. It is especially effective on metric columns with high cardinality, where an inverted index would be too large.

A good rule of thumb is to use a range index when you want to apply range predicates on metric columns that have a very large number of unique values. Using an inverted index for such columns creates a very large index that is inefficient in terms of storage and performance.

How it works

A range index is a variant of an inverted index. Instead of creating a mapping from individual values to document IDs, it creates a mapping from a range of values to document IDs. At query time Pinot uses the range index to quickly identify the set of documents whose column values fall within the requested range.

Supported column types

Range index is supported on:

• Dictionary-enabled columns of any data type except MAP (INT, LONG, FLOAT, DOUBLE, STRING, BIG_DECIMAL, BYTES, BOOLEAN, TIMESTAMP). The dictionary can be paired with either a dictionary-encoded forward index or a RAW forward index with a standalone dictionary.

• Raw-encoded columns without a dictionary for numeric types (INT, LONG, FLOAT, DOUBLE, BIG_DECIMAL).

A range index can also be used on a dictionary-encoded time column using STRING type, because Pinot only supports datetime formats that are in lexicographical order.

Configuration

The recommended way to enable a range index is through the fieldConfigList in the table configuration:

Recommended: fieldConfigList

{
  "fieldConfigList": [
    {
      "name": "hits",
      "indexes": {
        "range": {}
      }
    }
  ]
}

You can also specify the range index version (default is 2):

With explicit version

{
  "fieldConfigList": [
    {
      "name": "hits",
      "indexes": {
        "range": {
          "version": 2
        }
      }
    }
  ]
}

If the column keeps a RAW forward index but also needs a dictionary for the range index, configure the standalone dictionary explicitly and use range index version 2:

RAW forward index with dictionary-backed range index

{
  "fieldConfigList": [
    {
      "name": "eventDate",
      "encodingType": "RAW",
      "indexes": {
        "dictionary": {},
        "range": {
          "version": 2
        }
      }
    }
  ]
}

Range index version 1 is rejected for the combination of RAW forward index plus dictionary, because version 1 can fall back to scans that compare raw forward values with dictionary-ID evaluators. Version 2 uses the BitSliced range index and is the required form for RAW + dictionary.

Older configuration

The older approach uses rangeIndexColumns in tableIndexConfig:

Legacy: tableIndexConfig

{
  "tableIndexConfig": {
    "rangeIndexColumns": [
      "hits"
    ]
  }
}

Query examples

SELECT COUNT(*)
FROM baseballStats
WHERE hits > 11

SELECT playerName, hits
FROM baseballStats
WHERE hits BETWEEN 50 AND 100
ORDER BY hits DESC
LIMIT 20

SELECT teamID, AVG(hits) AS avgHits
FROM baseballStats
WHERE hits >= 10 AND hits <= 200
GROUP BY teamID
ORDER BY avgHits DESC

Limitations

• The range index does not support MAP columns.

• When the forward index is disabled for a column, the column must be single-valued and use range index version 2 for range queries to work.

• Numeric RAW columns can use a range index without a dictionary. Non-numeric RAW columns need a dictionary, and RAW + dictionary range indexes must use version 2.