· poetry python til

Apache Pinot: Experimenting with the StarTree Index

My colleagues Sandeep Dabade and Kulbir Nijjer recently wrote a three part blog post series about the StarTree index, an Apache Pinot indexing technique that dynamically builds a tree structure to maintain aggregates across a group of dimensions. I’ve not used this index before and wanted to give it a try and in this blog post, I’ll share what I learned.

I’ve put all the code in the startreedata/pinot-recipes GitHub repository in case you want to try it out yourself.


Let’s start by cloning the repository and navigating to the StarTree index recipe:

git clone git@github.com:startreedata/pinot-recipes.git
cd pinot-recipes/recipes/startree-index

We can then spin up Apache Pinot and friends using Docker Compose:

docker compose up

Data Generator

I’ve created a data generator that simulates website traffic. It used the Faker library, so make sure that you install that:

pip install faker

Now let’s run it and have a look at one of the generated messages:

python datagen.py 2>/dev/null | head -n1 | jq
  "ts": 1690559577897,
  "userID": "a56717ef",
  "timeSpent": 622,
  "pageID": "page032",
  "country": "Saint Lucia",
  "deviceType": "Mobile",
  "deviceBrand": "Dell",
  "deviceModel": "Google Pixel 6",
  "browserType": "Chrome",
  "browserVersion": "92.0.4515.159",
  "locale": "zh_CN"

We have a variety of information about the page visit, including details about the device and browser used. We’re going to ingest that data into Apache Kafka, so let’s first create a topic:

rpk topic create -p 5 webtraffic

And now let’s ingest data into that topic:

python datagen.py 2>/dev/null |
jq -cr --arg sep ø '[.userID, tostring] | join($sep)' |
kcat -P -b localhost:9092 -t webtraffic -Kø

Apache Pinot Tables

We’re going to create several tables in Apache Pinot to explore this dataset:


No indexes


Inverted index on country, browserType, and deviceBrand. The config looks like this:

    "tableIndexConfig": {
        "invertedIndexColumns": [
            "country", "browserType", "deviceBrand"

StarTree index splitting on country, browserType, and deviceBrand, aggregating COUNT(*), SUM(timeSpent), and AVG(timeSpent). The config looks like this:

    "tableIndexConfig": {
        "starTreeIndexConfigs": [
            "dimensionsSplitOrder": [
            "skipStarNodeCreationForDimensions": [],
            "functionColumnPairs": [
            "maxLeafRecords": 10000


Once I’d created the tables, I waited for the data generator to get a bunch of data ingested. I stopped the generator once it had got to 175 million rows, which isn’t big data, but should be enough records for us to see how the indexes work.

From my understanding, the StarTree index should work well when we have to do filtering and then aggregation of data. An inverted index is also good at filtering, so that configuration will serve as a useful comparison.

I came up with the following queries to kick the tyres:

Aggregating time spent by country

This is a pure aggregation query, so the inverted index shouldn’t help at all.

select country, sum(timeSpent) AS totalTime
from webtraffic
group by country
order by totalTime DESC
limit 10
Filtering by country and aggregating by count

This query filters by the country column and then counts the records returned (2.2 million) by browser type. We’d expect the inverted index to do better than the one with no indexes, but StarTree’s pre indexing should give it the edge.

select browserType, count(*)
from webtraffic
WHERE country IN ('Germany', 'United Kingdom', 'Spain')
GROUP BY browserType
limit 10

Comparing query performance

I ran these queries a few times in the Pinot UI to see how well they performed, but I figured that I should probably use a load generator to get more consistent results. I’m going to use a Python performance testing tool called Locust to do this.

Keep in mind that I’m doing all these experiments on my laptop, so you can undoubtedly achieve better results if you use a cluster and don’t have the load-testing tool running on the same machine. But for my purposes of doing something quick and dirty to understand how these indexes help with query performance, this setup does the job.

After installing Locust:

pip install locust

I created a locustfile.py that looked like this:

from locust import FastHttpUser, task

import requests
import random

query1 = """
select country, sum(timeSpent) AS totalTime
from webtraffic
group by country
order by totalTime DESC
limit 10

query2 = """
select browserType, count(*)
from webtraffic
WHERE country IN ('Germany', 'United Kingdom', 'Spain')
GROUP BY browserType
limit 10

class PinotUser(FastHttpUser):

    def run_q1(self):
        with super().rest("POST", "/query/sql", json={"sql": query3}, name="Web Traffic (StarTree)") as r:
            if r.status_code == requests.codes.ok:
                # print("/query/sql   - q1" + ': success (200)')
            elif r.status_code == 0:
                print("/query/sql - q1" + ': success (0)')
                print("/query/sql - q1" + ': failure (' + str(r.status_code) + ')')

I then ran the load generator configured to simulate a single user executing the query lots of times:

locust --host http://localhost:8099  \
  --autostart \
  -u 1 \ (1)
  --run-time 1m \
  --autoquit 5 (2)
1 Simulate having 1 user.
2 Exit 5 seconds after the test has been completed.

I ran this script 3 times for each query, manually updating the script to run each query against each table. I then collected the results that were printed to the console into the tables shown below:

Table 1. Aggregating time spent by country
Name # reqs Avg Min Max Med req/s

Web Traffic







Web Traffic (Inverted)







Web Traffic (StarTree)







I asked ChatGPT to create a box and whisker plot of this data, which is shown below:

box whisker plot web traffic
Figure 1. Box and whisker chart - aggregate time spent by country query
Table 2. Filtering by country and aggregating by count
Name # reqs Avg Min Max Med req/s

Web Traffic Filter







Web Traffic Filter (Inverted)







Web Traffic Filter (StarTree)







box whisker plot filter
Figure 2. Box and whisker chart - aggregate time spent by country query

From these results, we can see that the StarTree index does best on both queries, but there’s not much in it on the filtering query. I still haven’t quite worked out how many records you need in the aggregation step to see a noticeable improvement compared to doing normal aggregation.

A fun experiment though and I’ll have to do some more of these!

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket