ZEBIN KANG
08/26/2025, 9:45 PM{
"name": "request_ts",
"dataType": "TIMESTAMP",
"format": "1:SECONDS:EPOCH",
"granularity": "1:SECONDS"
},
to improve the index, we also doing
1. "timeColumnName": "request_ts",
2. "rangeIndexColumns": ["request_ts"],
3. "routing": {"segmentPrunerTypes": ["time"]}
4. add request_ts to timestampConfig with granularities like ["DAY","WEEK","MONTH"]
Could you please share if the operation is helpful or some of them does not improve the performance too much π
cc: @Neeraja Sridharan @Sai Tarun TadakamallaMayank
Neeraja Sridharan
08/26/2025, 10:34 PMSELECT
x,
DISTINCTCOUNTMV(ids1) FILTER(WHERE ids1_count > 0) as num_unique_ids1,
DISTINCTCOUNTMV(ids2) FILTER(WHERE ids2_count > 0) as num_unique_ids2
FROM real_time_pinot_metrics_table
WHERE
x in ${IDs}
AND team = {$TEAM_ID}
AND request_ts >= {$REQUEST_TS_START_RANGE}
AND request_ts <= {$REQUEST_TS_END_RANGE}
GROUP BY x
Currently, request_ts query range is last 24 hours. We might have any future use-cases querying for a day, as an example.
Does it make sense to have all the 3 listed below?
1. Range index
2. Timestamp index
3. Time based segment pruning
If so, any insights into how each one of them would benefit this scenario? πββοΈMayank
Neeraja Sridharan
08/26/2025, 10:51 PMMayank
Mayank
Neeraja Sridharan
08/26/2025, 10:56 PMMayank
Mayank
Neeraja Sridharan
08/26/2025, 10:58 PMSEGMENTPARTITIONEDDISTINCTCOUNT that you shared above - does it work for multi-value columns as well?Mayank
Neeraja Sridharan
09/04/2025, 4:19 AMTimestamp index for a request_ts column, where the data being ingested for this column is in epoch seconds format:
{
"name": "request_ts",
"dataType": "LONG",
"format": "1:SECONDS:EPOCH",
"granularity": "1:SECONDS"
}
Based on this document for Timestamp index :
Supported data type
AIs it mandatory to have theindex can only be created on theTIMESTAMPdata type.TIMESTAMP
TIMESTAMP data type for request_ts in the Schema config? If so, appreciate any help in providing a recommended approach with the set up needed (schema & table config) for declaring timestamp index on this column πββοΈ
"fieldConfigList": [
{
"name": "request_ts",
"indexTypes": ["TIMESTAMP"],
"timestampConfig": {
"granularities": ["DAY", "WEEK", "MONTH"]
}
}
]
cc: @Sai Tarun TadakamallaMayank
Neeraja Sridharan
09/04/2025, 4:23 AM{
"name": "request_ts",
"dataType": "LONG",
"format": "1:SECONDS:EPOCH",
"granularity": "1:SECONDS"
}
Based on this document for Timestamp index :
Supported data type
Aindex can only be created on theTIMESTAMPdata type.TIMESTAMP
Mayank
"dataType": "TIMESTAMP",Mayank
Neeraja Sridharan
09/04/2025, 4:27 AM{
"name": "request_ts",
"dataType": "TIMESTAMP",
"format": "1:SECONDS:EPOCH",
"granularity": "1:SECONDS"
}Mayank
Mayank
Neeraja Sridharan
09/04/2025, 4:31 AM{
"name": "request_ts",
"dataType": "TIMESTAMP",
"format": "1:SECONDS:EPOCH",
"granularity": "1:SECONDS"
}
Table config:
"fieldConfigList": [
{
"name": "request_ts",
"indexTypes": ["TIMESTAMP"],
"timestampConfig": {
"granularities": ["HOUR", "DAY", "WEEK", "MONTH"]
}
}
]Neeraja Sridharan
09/05/2025, 7:53 PMrequest_ts could not be converted to timestamp and we had ingestion failures while consuming the real-time data. I can try MILLISECONDS instead of SECONDS, but wanted to check if you could help unblock us with any other recommended solution. Just trying to see if we can avoid creating another transform column to be able to declare TIMESTAMP datatype and thereby TIMESTAMP index on it.
It'd be great if there is a way to create TS index on request_ts, where the incoming data is in epoch seconds format πββοΈNeeraja Sridharan
09/06/2025, 4:46 AMdataType as TIMESTAMP in the schema, and subsequently declare Timestamp index on it (though this is not documented). Could you please confirm?
"fieldConfigList": [
{
"name": "DateEpoch",
"encodingType": "DICTIONARY",
"indexTypes": ["TIMESTAMP"],
"timestampConfig": {
"granularities": [
"DAY",
"WEEK",
"MONTH"
]
}
}
]
"transformConfigs": [
{"columnName": "DateEpoch", "transformFunction": "FromDateTime(\"Date\", 'MM/dd/yyyy hh:mm:ss a')" }
]
"dateTimeFieldSpecs": [
{
"name": "DateEpoch",
"dataType": "TIMESTAMP",
"format" : "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS"
}
]
Difference in my case being that the corresponding date column's incoming data is Epoch Seconds, while the example in the recipe is Floating Timestamp. So, wanted to check if Epoch Seconds is naturally compatible with TIMESTAMP dataType without any conversion, but we're seeing ingestion failures as attached in my earlier post βοΈ.
I suppose a transform like the below might be needed in my case - after which I can declare with timestamp dataType and thereby, TS index?
"transformConfigs": [
{
"columnName": "request_ts",
"transformFunction": "fromEpochSeconds(request_ts)"
}
]Neeraja Sridharan
09/06/2025, 4:55 AMIf there's a equality predicate on a high cardinality item (say team), then sort (and optionally partition) on that.re: this ^^ note shared earlier, I see the reverse in Pinot docs i.e. "_The most frequently used predicate column with low cardinality should be used for applying sorted inverted index ._" So, hope declaring
Timestamp index on the request_ts column and Sorted index on a different low cardinality enterprise_id column still works (on top of range index and other inverted index) πββοΈNeeraja Sridharan
09/06/2025, 4:57 AMMayank
Neeraja Sridharan
09/10/2025, 6:33 AMIndexes section:
The most frequently used predicate column with low cardinality should be used for applying sorted inverted index .Also noticed in a few other Pinot-related discussions as well.
Neeraja Sridharan
09/10/2025, 6:35 AMMayank
Neeraja Sridharan
09/10/2025, 9:03 PMenterprise_id column is not a high-cardinality column for our real-time Pinot table, but wondering if we can still set it up as sortedColumn based on the discussion here.
cc: @Sai Tarun TadakamallaMayank
Sai Tarun Tadakamalla
09/10/2025, 10:22 PMenterprise_id is pretty low. Only about 0.001% of unique values per day.Mayank
Neeraja Sridharan
09/11/2025, 5:23 AMNeeraja Sridharan
09/11/2025, 5:32 AMinverted index set up for ~6 columns and planning to add ~5 more columns to inverted index. Is there a restriction or recommendation on the number of columns that can be used for this index?
Wanted to see if enterprise_id (which is always an equality predicate/filter) can be added to sorted index. Here is the sample query:
SELECT
x,
DISTINCTCOUNTMV(ids1) FILTER(WHERE ids1_count > 0) as num_unique_ids1,
DISTINCTCOUNTMV(ids2) FILTER(WHERE ids2_count > 0) as num_unique_ids2
FROM real_time_pinot_metrics_table
WHERE
x in ${XIDs}
AND enterprise_id = {$ENTERPRISE_ID}
AND request_timestamp >= CAST(toEpochSeconds(now()) * 1000 - 86400000 AS LONG) -- Last 24 hours UTC
GROUP BY xMayank
Mayank
Neeraja Sridharan
09/11/2025, 5:36 AMrequest_timestamp is a transform column to enable Timestamp index that we discussed earlier in this thread. Did you mean creating another derived column for CAST(toEpochSeconds(now()) * 1000 - 86400000 AS LONG) to get the Last 24 hours UTC?Mayank
Neeraja Sridharan
09/11/2025, 5:39 AMrequest_timestamp column would still work as expected - is that correct?Mayank
Neeraja Sridharan
09/11/2025, 5:40 AMMayank
Mayank
Neeraja Sridharan
09/11/2025, 5:45 AMNeeraja Sridharan
09/11/2025, 5:47 AMMayank
Mayank
Neeraja Sridharan
09/11/2025, 5:50 AMNeeraja Sridharan
09/11/2025, 7:29 AMWhatβs the max number rows for an enterprise for 24 hrs@Mayank This ^^ is ~25 million rows for an enterprise for last 24 hrs (chose an enterprise with max rows). Are we still good to set up
enterprise_id as sortedColumn, or would you rather recommend leaving it as invertedIndex?
Here is the sample query:
SELECT
x,
DISTINCTCOUNTMV(ids1) FILTER(WHERE ids1_count > 0) as num_unique_ids1,
DISTINCTCOUNTMV(ids2) FILTER(WHERE ids2_count > 0) as num_unique_ids2
FROM real_time_pinot_metrics_table
WHERE
x in ${XIDs}
AND enterprise_id = {$ENTERPRISE_ID}
AND request_timestamp >= CAST(toEpochSeconds(now()) * 1000 - 86400000 AS LONG) -- Last 24 hours UTC
GROUP BY xMayank
Neeraja Sridharan
09/12/2025, 12:09 AMrequest_timestamp column which is our primary time column (on top of Timestamp & Range index), and moved enterprise_id to inverted index [TBD: performance evaluation]. DISTINCTCOUNTMV related optimizations to follow, in case of poor performance with the query sample shared above.Mayank
Neeraja Sridharan
09/12/2025, 11:52 PMNeeraja Sridharan
09/12/2025, 11:53 PMMayank
Mayank
Neeraja Sridharan
09/13/2025, 12:24 AMMayank
Neeraja Sridharan
09/13/2025, 1:18 AMDISTINCTCOUNTMV on 2 array columns ids1 & ids2 - does it make sense to implement bloom filters and no-dictionary columns for these multi-value fields. DISTINCTCOUNTHLLMV is also having server timeout.
May be add Star-Tree indexes for aggregation optimization? but hesitant about the additional storage it'll consume.
{
"noDictionaryColumns": [
"ids1",
"ids2"
],
"bloomFilterColumns": [
"ids1",
"ids2"
],
"starTreeIndexConfigs": [
{
"dimensionsSplitOrder": [
"enterprise_id",
"app_id"
],
"functionColumnPairs": [
"DISTINCTCOUNTMV__ids1",
"DISTINCTCOUNTMV__ids2"
]
}
]
}