> ## Documentation Index
> Fetch the complete documentation index at: https://docs.startree.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Supported Indexes

> Which Pinot indexes External Tables support — inverted, range, JSON, text, bloom, star-tree, and more — with config examples and why columns are RAW.

<Warning>
  This feature is available starting in **StarTree release 0.15.0**. It must be enabled on demand — contact your StarTree representative to have it activated for your environment.
</Warning>

External tables support a broad set of Pinot indexes out of the box. Indexes are key to getting low-latency query performance from external tables. Without them, every query performs a full segment scan across data read from object storage. With the right indexes configured for your query patterns, Pinot can skip irrelevant segments and data blocks entirely, reducing query time from minutes/seconds to seconds/milliseconds on large datasets, while also cutting S3 I/O costs.

The most impactful indexes to configure based on your workload:

* **JSON index** — required for filtering or extracting nested fields from complex columns (`STRUCT`, `LIST`, `MAP`).
* **Text index** — enables full-text search on free-text string columns.
* **Range index** — speeds up range predicates on numeric or timestamp columns (e.g., `WHERE ts > X AND ts < Y`).
* **Inverted index** — efficient equality filtering on low-cardinality columns (e.g., `WHERE status = 'active'`).

***

## Supported

| Index                      | Status           | Notes                                                                                                                                                                                  |
| -------------------------- | ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Forward index (remote)** | ✅ Supported      | Reads raw values directly from Parquet on object storage.                                                                                                                              |
| **Inverted index**         | ✅ Supported      | Dictionary is retained in the index sidecar; see below.                                                                                                                                |
| **Range index**            | ✅ Supported      |                                                                                                                                                                                        |
| **Timestamp index**        | ✅ Supported      |                                                                                                                                                                                        |
| **JSON index**             | ✅ Supported      | Required for `JSON_MATCH` and `JSON_EXTRACT_SCALAR` on complex columns (`STRUCT`, `LIST`, `MAP`).                                                                                      |
| **Composite JSON index**   | ✅ Supported      |                                                                                                                                                                                        |
| **Text index (Lucene)**    | ✅ Supported      | Enables full-text search on string columns.                                                                                                                                            |
| **Sparse index**           | ✅ Supported      |                                                                                                                                                                                        |
| **Star-tree index**        | ✅ Supported      | Pre-aggregates on low-cardinality dimensions.                                                                                                                                          |
| **Bloom filter**           | ✅ Supported      |                                                                                                                                                                                        |
| **FST index**              | ✅ Supported      |                                                                                                                                                                                        |
| **IFST index**             | ✅ Supported      |                                                                                                                                                                                        |
| **Null value vector**      | ✅ Supported      | Used internally when `nullHandlingEnabled: true` is set on the table.                                                                                                                  |
| **Vector index (HNSW)**    | ✅ Supported      | The index config must set `"storeInSegmentFile": true` — without it the HNSW index is not loaded and vector queries silently fall back to exact scan (`VECTOR_SIMILARITY_EXACT_SCAN`). |
| **Sorted index**           | 🚫 Not supported | Requires data to be sorted at ingestion time; not applicable to external tables.                                                                                                       |
| **Geospatial / H3 index**  | 🚫 Not supported |                                                                                                                                                                                        |

<Note>
  Sorted and H3 are the only index types the controller rejects on an external table. Two related validations to be aware of: every non-virtual column must have a `fieldConfig` with `encodingType: RAW` (a column without one would default to dictionary encoding and is rejected), and derived columns / `transformConfigs` are not allowed.
</Note>

***

## Why are columns RAW (no dictionary)?

In a standard Pinot table, every column is dictionary-encoded by default. Pinot builds a lookup table that maps each unique value to a compact integer ID and stores those IDs in the forward index. This dictionary is created during ingestion, when data is converted into Pinot's native segment format.

External tables never go through that ingestion step — they read Parquet files directly from object storage at query time. Because the data is never materialized into Pinot segments, there is no opportunity to build a dictionary, so every column is encoded as `RAW` (no dictionary).

You can explicitly see this in the column config generated by the onboarding flow — every column gets RAW encoding and a RAW forward index, and nothing else:

```json theme={null}
{
  "name": "payment_type",
  "encodingType": "RAW",
  "indexes": {
    "forward": { "encodingType": "RAW" }
  }
}
```

This means the forward index stores the actual values from Parquet, not dictionary IDs.

**Indexes that need a dictionary — inverted, FST, IFST, star-tree** — still work because Pinot builds a per-segment dictionary sidecar when it writes the index files alongside the Parquet data. The main forward index stays RAW; only the index sidecar carries the dictionary mapping. You opt in by adding a `"dictionary": {}` block to the column's `indexes` config (see examples below).

**Sorted index is ruled out entirely** because it requires data to be physically sorted during the ingestion write path. External tables have no write path — they read whatever order the Parquet files are in — so a sorted index cannot be built or maintained.

***

## Adding an index

### Choosing the right index

| Query pattern                            | Use          | Cardinality guidance                         |
| ---------------------------------------- | ------------ | -------------------------------------------- |
| Equality / IN filter                     | Inverted     | Best for low-to-medium cardinality           |
| Range filter (`BETWEEN`), including time | Range        | Any cardinality; ideal for numeric/timestamp |
| "Does this value exist?" segment pruning | Bloom filter | Best for high-cardinality equality filters   |
| Filtering/extracting nested JSON fields  | JSON         | Works on raw JSON regardless of cardinality  |
| Full-text / token search                 | Text         | For free-text columns; use `TEXT_MATCH`      |
| Prefix / fuzzy string match              | FST          | Dictionary-backed; low-to-medium cardinality |
| Repeated aggregations / group-bys        | Star-tree    | Pre-aggregates on low-cardinality dimensions |
| Time-bucketed queries                    | Timestamp    | On the time column                           |

<Tip>
  If you're unsure, start with a range index on time and numeric filters and an inverted index on the low-cardinality string columns you filter on most. Add others only when a query needs them.
</Tip>

### Inverted index (low-cardinality equality)

Dictionary-backed, so keep the forward index RAW and add a dictionary block:

```json theme={null}
{
  "name": "payment_type",
  "encodingType": "RAW",
  "indexes": {
    "forward":    { "encodingType": "RAW" },
    "dictionary": {},
    "inverted":   { "disabled": false }
  }
}
```

Example query:

```sql theme={null}
SELECT count(*) FROM nyc_taxi_trips WHERE payment_type = 'CASH';
```

<Note>
  Optional tuning: for External (tiered) tables, the cluster config `pinot.server.index.inverted.enable.startree.reader = true` selects the StarTree S3-optimized inverted index reader.
</Note>

### Range index (numeric / time ranges)

No dictionary needed:

```json theme={null}
{
  "name": "fare_amount",
  "encodingType": "RAW",
  "indexes": { "range": {} }
}
```

Example query:

```sql theme={null}
SELECT count(*) FROM nyc_taxi_trips WHERE fare_amount BETWEEN 10 AND 50;
```

### Bloom filter (high-cardinality equality)

```json theme={null}
{
  "name": "trip_id",
  "encodingType": "RAW",
  "indexes": { "bloom": {} }
}
```

Example query:

```sql theme={null}
SELECT * FROM nyc_taxi_trips WHERE trip_id = 't_8f3a91';
```

### JSON index

```json theme={null}
{
  "name": "surcharges",
  "encodingType": "RAW",
  "indexes": { "json": {} }
}
```

Example query:

```sql theme={null}
SELECT count(*) FROM nyc_taxi_trips WHERE JSON_MATCH(surcharges, '"$.type" = ''airport''');
```

### Text index (full-text search)

```json theme={null}
{
  "name": "notes",
  "encodingType": "RAW",
  "indexes": { "text": {} }
}
```

Example query:

```sql theme={null}
SELECT count(*) FROM nyc_taxi_trips WHERE TEXT_MATCH(notes, 'airport AND delay');
```

<Note>
  The onboarding/preview flow generates the base `fieldConfigList` with RAW encoding for every column. To add an index, merge the `indexes` block above into the matching column entry — don't change `encodingType`.
</Note>