Hi Team, We are using Apache Pinot for API develo...
# general
a
Hi Team, We are using Apache Pinot for API developer dashboard showing graphs for API Call volume for time buckets, Error Volume, Median Latency and a Grid for Api Level metrics Desinged a startree index but from using
explain 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
Copy code
"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
Copy code
"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
Copy code
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
Copy code
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
Copy code
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