Arkoprovo Sircar
01/16/2025, 8:05 PMexplain plan for <query> it looks like star-tree index is not even being used for most queries.
For the Error Volume where simple Count(*) based on filter on ResponseStatus, star tree gets used, but other queries not being used
This data is directly coming into Pinot from API Gateway, hence , anticipating huge data.
Need help with proper indexing if possible
We have starTree Index like below
Json
"starTreeIndexConfigs": [
{
"dimensionsSplitOrder": [
"request_metadata.request.account",
"request_metadata.request.application_id",
"request_metadata.request.verb",
"Normalized_Path",
"Is_Error",
"created_at"
],
"functionColumnPairs": [
"COUNT__*",
"PERCENTILE_EST__request_metadata.response.elapsed_time"
],
"maxLeafRecords": 10000
}
],
Is_Error and Normalized_Path are transformed columns
Json
"transformConfigs": [
{
"columnName": "Is_Error",
"transformFunction": "not(greater_than_or_equal(request_metadata.response.response_status, 200) AND less_than_or_equal(request_metadata.response.response_status, 299))"
},
{
"columnName": "Normalized_Path",
"transformFunction": "regexp_replace(request_metadata.request.path, '/([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}|[0-9]+|[0-9a-fA-F]{24})(?=/|$)', '/{id}')"
}
],
now the query where the query Plan shows a full scan instead of using star tree index is ( Detailed API level usage metrics)
SQL
SELECT Normalized_Path AS Route,
request_metadata.request.verb AS HttpVerb,
COUNT(*) AS CallVolume,
PERCENTILE50(request_metadata.response.elapsed_time) AS MedianLatency,
PERCENTILE95(request_metadata.response.elapsed_time) AS P95Latency,
( SUM(
CASE
WHEN NOT (request_metadata.response.response_status >= 200
AND request_metadata.response.response_status <= 299) THEN 1
ELSE 0
END ) * 100.0 / COUNT(*) ) AS ErrorPercentage
FROM GatewayApiUsageTest
WHERE created_at >= '2025-01-01 11:30:00'
AND created_at <= '2025-02-01 18:30:00'
AND request_metadata.request.account = 'photfix'
GROUP BY Normalized_Path,
HttpVerb
ORDER BY CallVolume DESC LIMIT 90
The query that does use the star tree index from our above index is below ( Getting Error Count in time bucket of api's called)
SQL
SELECT dateTimeConvert( created_at, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss', '12:HOURS' ) AS MetricUsageDate,
COUNT(*) AS ErrorVolume
FROM GatewayWithStarTree
WHERE created_at >= '2025-01-01 11:30:00'
AND created_at <= '2025-02-01 18:30:00'
AND request_metadata.request.account = 'photfix'
AND NOT (request_metadata.response.response_status >= 200
AND request_metadata.response.response_status <= 299)
GROUP BY MetricUsageDate
ORDER BY MetricUsageDate LIMIT 1000
When aggregating for PERCENTILE_EST , then also Star tree not used ( For getting median latency)
SQL
SELECT dateTimeConvert( created_at, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss', '12:HOURS' ) AS MetricUsageDate,
PERCENTILE_EST(request_metadata.response.elapsed_time,50) AS MedianLatency
FROM GatewayWithStarTree
WHERE created_at >= '2025-01-01 11:30:00'
AND created_at <= '2025-02-01 18:30:00'
AND request_metadata.request.account = 'photfix'
GROUP BY MetricUsageDate
ORDER BY MetricUsageDate LIMIT 1000
Can somebody please explain what is going wrong in the index?
OR how to design the index based on these query patterns?
Please do let me know if i need to share more details