https://pinot.apache.org/ logo
Join Slack
Powered by
Hello everyone, I’m trying to understand how we sh...
# troubleshooting
d
Hello everyone, I’m trying to understand how we should approach this kind of analytical query in Pinot. Whenever I run a query like the one below, it eventually times out. For context, my 
events
table has around 3*B events per month*, so I’m wondering whether this type of multi-stage query is realistically expected to work at this scale, and if so, what the recommended approach would be. The query is essentially doing the following: • Finding each user’s first 
APP_INSTALL
event. • Looking at their sessions in the following 7 days. • Deduplicating sessions using 
ROW_NUMBER()
because, for a given 
(session_id, platform)
, there can be multiple rows and we need to select the row with the highest 
event_count
. • Identifying users who were active on exactly 3 distinct days and had exactly 3 sessions during those 7 days. • Finally, scanning their sessions again to find their last usage timestamp. I’m using the multi-stage engine:
Copy code
SET useMultistageEngine = true;
and the query currently times out even with:
Copy code
OPTION(timeoutMs=90000);
My main questions are: 1. Can Pinot efficiently handle this type of query at this scale, or am I approaching it in the wrong way? 2. Would adding more/heavier indexes help here, or are the expensive parts mainly caused by the 
GROUP BY
, window function, joins, 
COUNT(DISTINCT ...)
, and repeated scans of 
sessions
? 3. What is the recommended way to plan indexing for a query like this? Should I first use something like 
EXPLAIN
/ query plan mode to understand which operators are expensive and then decide which columns/indexes to optimize? 4. Are there specific indexes or table/data-model changes that would make these joins and aggregations more efficient in Pinot? 5. Would it be better to pre-aggregate or maintain some of these metrics rather than calculating them at query time? I’m particularly interested in understanding the right process for optimizing Pinot queries: query plan → identify the expensive operators/scans → determine whether indexing can help → benchmark the change, rather than blindly adding indexes. Here is the full query for reference:
Copy code
SET useMultistageEngine = true;

WITH first_install AS (
    SELECT
        user_id,
        MIN(event_timestamp) AS first_install_timestamp,
        MIN(event_date_ist) AS first_install_date
    FROM events
    WHERE event_type = 'APP_INSTALL'
      AND user_id IS NOT NULL
      AND event_year = 2026
      AND event_month = 9
    GROUP BY user_id
),

ranked_sessions AS (
    SELECT
        s.*,
        ROW_NUMBER() OVER (
            PARTITION BY s.session_id, s.platform
            ORDER BY s.event_count DESC
        ) AS rn
    FROM sessions s
    WHERE s.session_id IS NOT NULL
),

dedup_sessions AS (
    SELECT
        session_id,
        platform,
        user_id,
        property_id,
        event_count,
        page_view_count,
        min_timestamp,
        max_timestamp,
        min_date_ist
    FROM ranked_sessions
    WHERE rn = 1
),

sessions_7d AS (
    SELECT
        fi.user_id,
        fi.first_install_timestamp,
        fi.first_install_date,
        s.session_id,
        s.platform,
        s.min_timestamp,
        s.max_timestamp,
        s.min_date_ist
    FROM first_install fi
    JOIN dedup_sessions s
        ON fi.user_id = s.user_id
    WHERE s.min_timestamp >= fi.first_install_timestamp
      AND s.min_timestamp < fi.first_install_timestamp + 604800000
),

qualified_users AS (
    SELECT
        user_id,
        MIN(first_install_timestamp) AS first_install_timestamp,
        MIN(first_install_date) AS first_install_date,
        COUNT(DISTINCT min_date_ist) AS active_days_7d,
        COUNT(DISTINCT session_id) AS session_count_7d
    FROM sessions_7d
    GROUP BY user_id
    HAVING COUNT(DISTINCT min_date_ist) = 3
       AND COUNT(DISTINCT session_id) = 3
),

last_usage AS (
    SELECT
        s.user_id,
        MAX(s.max_timestamp) AS last_used_timestamp
    FROM dedup_sessions s
    JOIN first_install fi
        ON s.user_id = fi.user_id
    GROUP BY s.user_id
)

SELECT
    q.user_id,
    q.first_install_date,

    DATETIMECONVERT(
        lu.last_used_timestamp,
        '1:MILLISECONDS:EPOCH',
        '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(Asia/Kolkata)',
        '1:DAYS'
    ) AS last_used_date,

    q.active_days_7d,
    q.session_count_7d

FROM qualified_users q
LEFT JOIN last_usage lu
    ON q.user_id = lu.user_id

ORDER BY q.first_install_date

OPTION(timeoutMs=90000);
m
@Yash Mayya @Gonzalo Ortiz ^^
g
Hi @Dhanesh Dhakrey! I’m going to share a pretty dense AI-generated report for your use case. It won’t be 100% perfect because I don’t know the Pinot version you are using, and we would need the table configs, schema and especially stageStats to know exactly where the bottleneck is. I hope it is useful, we can use it as a starting point
pinot-funnel-query-optimization.md
The TL;DR is : 1. Without stageStats we can only do static analysis, so take that in mind 2. Your bottleneck is query shape, not indexes. In Pinot, indexes only affect the leaf stage (segment scan + filter). They do nothing for joins, window functions, or intermediate group-bys — which is where essentially all of your time goes. 3. The ROW_NUMBER() CTE is the main cost. MSE’s window operator is fully blocking: it buffers every input row on-heap before emitting anything, and your ranked_sessions reads the whole sessions table unfiltered. Default cap is 1M rows per worker, overflow mode THROW. 4. Two fixes for it: push a time filter below the window (installs in Sept + 7 days ⇒ you never need sessions outside ~Sept 1–Oct 8), and replace ROW_NUMBER() with a GROUP BY session_id, platform using MIN/MAX — a streaming aggregation instead of a full materialization. 5. Your CTEs run twice. first_install and dedup_sessions are each referenced twice, and Pinot doesn’t materialize CTEs by default — so that full-table window function executes twice. SET useSpools = true shares them instead. This may help, but not all reused CTEs can be spooled 6. last_usage is unbounded — it aggregates all sessions for all installers across all time, when the final join only needs the handful of users with exactly 3 sessions. Drive it from qualified_users. 7. The thing that actually helps your joins is partitioning, not indexing. user_id is the join key everywhere — if both tables are partitioned on it with the same function and count, the shuffle disappears entirely (is_colocated_by_join_keys hint, or SET inferPartitionHint=true). 8. Consider pre-aggregating. Your session dedup is really an ingestion-time problem: an upsert table keyed on (session_id, platform) with partial-upsert MAX on event_count removes the window function from the query permanently.
d
@Gonzalo Ortiz thanks ,i will read this and directly ask you if anything is required
👍 1
2 Views
Open in Slack
PreviousNext
Powered by