Description

Adding Timestamp index for Pinot data type TIMESTAMP.

Timestamp stores value as millisecond epoch long value. Typical workload for timestamp is filtering on a time range and group by with different time granularities(days/month/etc).

The current implementation requires the query executor to extract values, apply the function then do filter/groupBy, no leverage on dictionary or index.

This PR introduces the Timestamp Index. Users can configure the most useful granularities for a Timestamp data type column.

1. Pinot will pre-generate one column per time granularity with forward index and range index. The naming convention is $${ts_column_name}$${ts_granularity}, e.g. Timestamp column ts with granularities DAY, MONTH will have two extra columns generated: $ts$DAY and $ts$MONTH.
2. Query overwrite for predicate and selection/groupby:
   2.1 GROUPBY: functions like dateTrunc('DAY', ts) will be translated to use the underly column $ts$DAY to fetch data.
   2.2 PREDICATE: range index is auto-built for all granularity columns.

Example query usage:

select count(*), datetrunc('WEEK', ts) as tsWeek from airlineStats WHERE tsWeek > fromDateTime('2014-01-16', 'yyyy-MM-dd') group by tsWeek  limit 10

Some preliminary benchmark shows the query perf over 2.7 billion records improved from 45 secs to 4.2 secs

select dateTrunc('YEAR', event_time) as y, dateTrunc('MONTH', event_time) as m,  sum(pull_request_commits) from githubEvents group by y, m limit 1000  Option(timeoutMs=3000000)

vs.

Release Notes

Introduces the Timestamp Index. Users can configure the most useful granularities for a Timestamp data type column.

1. Pinot will pre-generate one column per time granularity with forward index and range index. The naming convention is $${ts_column_name}$${ts_granularity}, e.g. Timestamp column ts with granularities DAY, MONTH will have two extra columns generated: $ts$DAY and $ts$MONTH.
2. Query overwrite for predicate and selection/groupby:
   2.1 SELECTION/GROUPBY: functions like dateTrunc('DAY', ts) will be translated to use the underly column $ts$DAY to fetch data.
   2.2 PREDICATE: range index is auto-built for all granularity columns.

Documentation