You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by "rohityadav1993 (via GitHub)" <gi...@apache.org> on 2026/06/03 15:54:46 UTC

[I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

rohityadav1993 opened a new issue, #18667:
URL: https://github.com/apache/pinot/issues/18667

   # Proposal
   **Sorted Merge join**
   When the data from left input and right input is sorted by the join columns we can leverage [sorted merge join](https://en.wikipedia.org/wiki/Sort-merge_join) for a more scalable join computation by avoiding hash based joins (e.g. [ClickHouse full sort-merge join](https://clickhouse.com/blog/clickhouse-fully-supports-joins-full-sort-partial-merge-part3#full-sorting-merge-join)).
   
   **Sorted key group by**
   When data is sorted by the group-by keys in the aggregation query, we can avoid hash aggregations by computing the aggregation in a streaming manner (e.g. [MySQL GROUP BY Optimization](https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html)).
   
   ## Why avoid hash join
   
   Hash join materializes the entire right side into a hash table before probing begins, so memory grows linearly with right-side row count https://github.com/apache/pinot/blob/9e2335de3c3f252eef1147b7af7f5e1d16fde403/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/BaseJoinOperator.java#L186-L189. For multi-column join keys, Pinot falls back from primitive-typed lookup tables (`IntLookupTable`, `LongLookupTable`) to a generic `ObjectLookupTable` that allocates a composite key object per row, increasing GC pressure at scale. When data is already partitioned and sorted on the join key, a streaming merge join avoids the hash table entirely.
   
   ## Why avoid hash aggregation
   Hash-based GROUP BY in Pinot's MSE (MultistageGroupByExecutor) assigns each distinct group key a slot in a hash map — Long2IntOpenHashMap for single LONG keys, Object2IntOpenHashMap<FixedIntArray> for multi-column keys — and accumulates aggregation state for every group  simultaneously https://github.com/apache/pinot/blob/9e2335de3c3f252eef1147b7af7f5e1d16fde403/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/MultistageGroupByExecutor.java#L447-L451 Memory grows with the number of distinct groups, which is why queries with high-cardinality keys require SET numGroupsLimit = 50000000 to avoid hitting the default cap.
                                                                                                                
   When input rows are already sorted by the group-by key, none of that is necessary. E.g. MySQL calls this loose/tight index scan (https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html): once rows are ordered by key, you only need to hold state for the current key — flush the accumulator to output when the key changes, reset, and continue. Memory is O(1) regardless of group count, and there are no hash collisions, no map resizes, and no FixedIntArray allocations per group.
   
   **Note:** *Can create two separate issues but starting with one as there are commonalities in improvement at leaf stage to achieve either.*
   
   ---
   ## POC
   
   We did a POC on a table (`user_events`) sorted on `correlation_id` within each segment and partitioned across servers using a Murmur hash on `correlation_id` (128 buckets). This ensures all events for a given user land on the same server.
   
   **Design choices in `SortedMergeJoinOperator`:**
   - Lazy block reading: blocks are fetched from left and right inputs on demand — only one block per side is held in memory at a time.
   - `LeafStageSortJoinRule.addSortToLeafStage()` injects a `PhysicalSort` with no fetch limit and no offset, so the leaf ORDER BY is unbounded by design.
   
   <details>
   
   <summary>Funnel count query using sorted join:</summary>
   
   ```sql
   SET useMultistageEngine = true;
   SET usePhysicalOptimizer = true;
   SET maxRowsInJoin = 1234194304;
   SET timeoutMs = 120000;
   
   WITH joined AS (
     SELECT /*+ joinOptions(join_strategy='sorted') */
       CAST(
         DATETIMECONVERT(
           CAST(step1.occurred_at_min AS BIGINT) * 1000,
           '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '600:SECONDS'
         ) AS BIGINT
       ) AS ts,
       step1.correlation_id AS u1,
       step2.correlation_id AS u2
     FROM user_events
       /*+ tableOptions(partition_key='correlation_id', partition_function='Murmur', partition_size='128') */
       AS step1
     LEFT JOIN user_events
       /*+ tableOptions(partition_key='correlation_id', partition_function='Murmur', partition_size='128') */
       AS step2
       ON step1.correlation_id = step2.correlation_id
       AND step2.name = 'addToCart'
       AND step2.occurred_at_min BETWEEN {start_epoch} AND {end_epoch}
     WHERE step1.name = 'checkout'
       AND step1.occurred_at_min BETWEEN {start_epoch} AND {end_epoch}
   )
   SELECT ts, COUNT(DISTINCT u1) AS step1, COUNT(DISTINCT u2) AS step2
   FROM joined
   GROUP BY ts ORDER BY ts LIMIT 100000
   ```
   
   Physical plan (`EXPLAIN PLAN FOR` with `usePhysicalOptimizer=true`):
   
   ```
   PhysicalExchange(exchangeStrategy=[SINGLETON_EXCHANGE])
     PhysicalSort(sort0=[$0], dir0=[ASC], fetch=[100000])
       PhysicalAggregate(group=[{1}], agg#0=[DISTINCTCOUNT($0)], ...)
         PhysicalJoin(condition=[=($0, $2)], joinType=[left])
           PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE])
             PhysicalSort(sort0=[$0], dir0=[ASC])          ← pushed into leaf by LeafStageSortJoinRule
               PhysicalProject(correlation_id, ts=[DATETIMECONVERT(...)])
                 PhysicalFilter(name=checkout AND ...)
                   PhysicalTableScan(user_events)
           PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE])
             PhysicalSort(sort0=[$0], dir0=[ASC])          ← pushed into leaf by LeafStageSortJoinRule
               PhysicalProject(correlation_id)
                 PhysicalFilter(name=addToCart AND ...)
                   PhysicalTableScan(user_events)
   ```
   `IDENTITY_EXCHANGE` on both sides confirms partition co-location — no cross-server shuffle for the join input.
   </details>
   
   <details>
   <summary> Alternate single-scan query:</summary>
   
   ```sql
   SET useMultistageEngine = true;
   SET numGroupsLimit = 50000000;
   SET timeoutMs = 30000;
   
   SET useMultistageEngine = true;
   SET numGroupsLimit = 50000000;
   SET timeoutMs = 30000;
   
   WITH per_id AS (
     SELECT
     /*+ aggOptions(is_partitioned_by_group_by_keys='true') */
     FLOOR(occurred_at_min / 600) * 600 AS ts,
     correlation_id,
       MAX(CASE WHEN name = 'addToCart'  THEN 1 ELSE 0 END) AS has_step1,
       MAX(CASE WHEN name = 'checkout'   THEN 1 ELSE 0 END) AS has_step2
     FROM user_events
     WHERE occurred_at_min BETWEEN {start_epoch} AND {end_epoch}
       AND (name = 'addToCart' OR name = 'checkout')
     GROUP BY ts, correlation_id
   )
   SELECT
     ts,
     SUM(has_step1) AS step1_count,
     SUM(CASE WHEN has_step1 = 1 AND has_step2 = 1 THEN 1 ELSE 0 END) AS step2_count
   FROM per_id
   GROUP BY ts ORDER BY ts LIMIT 200
   ```
   
   Explain plan
   ```
   LogicalSort(sort0=[$0], dir0=[ASC], offset=[0], fetch=[200])
     PinotLogicalSortExchange(distribution=[hash], collation=[[0]], isSortOnSender=[false], isSortOnReceiver=[true])
       LogicalSort(sort0=[$0], dir0=[ASC], fetch=[200])
         PinotLogicalAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[COUNT($2)], aggType=[FINAL])
           PinotLogicalExchange(distribution=[hash[0]])
             PinotLogicalAggregate(group=[{0}], agg#0=[$SUM0($1)], agg#1=[COUNT() FILTER $2], aggType=[LEAF])
               LogicalProject(ts=[$0], has_step1=[$2], $f3=[AND(=($2, 1), =($3, 1))])
                 PinotLogicalAggregate(group=[{0, 1}], agg#0=[MAX($2)], agg#1=[MAX($3)], aggType=[DIRECT])
                   LogicalProject(ts=[*(FLOOR(/($31, 600)), 600)], correlation_id=[$14], ...)
                     LogicalFilter(...)
                       PinotLogicalTableScan(table=[[default, user_events]]) --- Notice this is a single scan
   ```
   
   This uses a single `TableScan` (vs two in the join).
   </details>
   
   ---
   
   ## Challenges
   
   ### 1. Leaf stage ORDER BY in sorted join will materialize all segments into a single block
   
   `LeafStageSortJoinRule` injects a `PhysicalSort` with no fetch limit or offset into the leaf stage so both join inputs arrive sorted. `ServerPlanRequestVisitor.visitSort()` only sets a LIMIT when `fetch >= 0`, so the injected sort — which has no fetch — generates an **unbounded** V1 `ORDER BY` with no LIMIT clause. `CombinePlanNode` selects `MinMaxValueBasedSelectionOrderByCombineOperator` for this ORDER BY, which merges results from all segments into a single block before returning. At large data volumes this exceeds the leaf stage's CPU budget, triggering `EarlyTerminationException` from `ThreadAccountant` inside `SelectionOperatorUtils.mergeWithOrdering()`, which propagates as a spurious `Cancelled by sender` to the broker.
   
   **Suggestions:**
   - New `MinMaxValueBasedSelectionOrderByCombineOperator` to extend `BaseStreamingCombineOperator`, using a bounded queue and emitting sorted blocks incrementally via a k-way heap merge across segments. The existing MinMax segment-pruning optimization can be preserved. Consuming or unsorted segments would sort in memory per segment before joining the heap.
   - **Implement k-way merge in `SortedMailboxReceiveOperator` when the sending stage is known to be sorted (already noted as a TODO in that class), rather than accumulating all rows and re-sorting
   https://github.com/apache/pinot/blob/b50a421740aa0749d63a12e2740595dccac21c37/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/SortedMailboxReceiveOperator.java#L92
   
   ```mermaid
   flowchart TD
       subgraph leaf["Leaf Stage — per server"]
           S1["Segment 1\n(sorted by join key)"]
           S2["Segment 2\n(sorted by join key)"]
           SN["Segment N\n(sorted or sort-in-memory)"]
           S1 & S2 & SN --> KH["K-way Heap Merge\nMinMaxValueBased→StreamingCombineOperator\npreserves collation, emits sorted blocks\nincrementally — MinMax pruning retained"]
       end
   
       KH --> MS["MailboxSendOperator\n(sorted blocks sent downstream)"]
   
       MS -->|"IDENTITY_EXCHANGE\n(co-located — no network shuffle)"| MR
   
       subgraph recv["Intermediate Stage"]
           MR["SortedMailboxReceiveOperator\nOption A: k-way merge across mailboxes\n\nOption B: receives already-merged stream\nfrom streaming combine"]
       end
   
       MR --> SMJ["SortedMergeJoinOperator\n(streaming two-pointer join\nlazy block loading — one block per side in memory)"]
    ```
   
   
   ### 2. Join queries that reduce per key can be rewritten as sorted GROUP BY, but MSE GROUP BY is not sort-aware
   
   Some join queries — including the funnel query above — can be expressed as a single-scan GROUP BY (shown in the alternate query). MSE aggregation is also required for correctness. This is not just a rewrite convenience: SSE `FUNNELCOUNT(settings('partitioned','sorted'))` is not equivalent here because SSE processes each segment independently. When a user's funnel events span two segments on the same server, `SortedAggregationResult` resets its per-key state at the segment boundary and `PartitionedMergeStrategy` merges results by plain addition — producing incorrect counts.
   
   With `/*+ aggOptions(is_partitioned_by_group_by_keys='true') */`, the alternate query achieves partition co-location by avoiding PinotLogicalExchange before first PinotLogicalAggregate, but the intermediate and final GROUP BY stages still use hash-based aggregation. When data is sorted on the group-by keys, a streaming sorted aggregation — accumulating state per key and flushing when the key changes — would eliminate hash table construction and the associated group count limits, mirroring how ClickHouse's `MergingSortedTransform` handles aggregation on MergeTree data. In the past we have seen hash based group by aggergation having issues with misconfigured hashtable size when cardinality is high.
   
   **Suggestions:**
   - Implement a sorted-input-aware aggregation operator in MSE that streams over sorted blocks without a hash table, activated when the leaf stage advertises sorted collation on the group-by keys.
   ```mermaid
   flowchart TB
       subgraph current["Current — two hash map layers"]
           direction TB
   
           subgraph leaf["V1 Leaf Stage (per server, across segments)"]
               direction TB
               S1["Seg 1\nGROUP BY scan\n→ GroupByResultsBlock\n(per-segment hash map)"]
               S2["Seg 2\nGROUP BY scan\n→ GroupByResultsBlock"]
               SN["Seg N\nGROUP BY scan\n→ GroupByResultsBlock"]
               S1 & S2 & SN --> GBC["GroupByCombineOperator\nextends BaseSingleBlockCombineOperator\n(NOT streaming)\nmerges all segments into\nshared IndexedTable hash map\n→ single GroupByResultsBlock"]
           end
   
           GBC -->|"mailbox send"| MSE
   
           subgraph mse["MSE Intermediate Stage"]
               direction TB
               MSE["MultistageGroupByExecutor\ngenerateGroupByKeys()\nObject2IntOpenHashMap or Long2IntOpenHashMap\nFixedIntArray per multi-column key\ngrows O(distinct groups)\n→ SET numGroupsLimit = 50000000 needed"]
           end
       end
   
       subgraph proposed["Proposed — sorted streaming (data sorted by GROUP BY key)"]
           direction TB
   
           subgraph pleaf["V1 Leaf Stage (streaming)"]
               direction TB
               T1["Seg 1\n(sorted by key)"] & T2["Seg 2\n(sorted by key)"] & TN["Seg N\n(sorted by key)"] --> KH["K-way Heap Merge\n(streaming combine)\nemits groups in key order\nno IndexedTable — O(1) per key"]
           end
   
           KH -->|"sorted blocks via mailbox"| SMSE
   
           subgraph smse["MSE Intermediate Stage (streaming)"]
               direction TB
               SMSE["Sorted Streaming Aggregator\nCompare key vs previous key"]
               SMSE -->|"same key"| ACC["Accumulate into\ncurrent state\nO(1) memory"]
               SMSE -->|"key changes"| EMIT["Emit completed group\nreset state"]
               ACC --> SMSE
               EMIT --> SMSE
           end
       end
   ```
   
   
   Looking for community input on the preferred direction and would be happy to contribute the finalised approach.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "xiangfu0 (via GitHub)" <gi...@apache.org>.
xiangfu0 commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-4636019107

   cc: @gortiz @Jackie-Jiang 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-4691535362

   Thanks for the detailed write-up @rohityadav1993 — the motivation is solid and the POC plan/EXPLAIN output is really helpful. A few thoughts on the foundation this needs to stand on, plus one cross-cutting issue I think materially affects how well it will perform.
   
   **Pinot doesn't enforce or even track order across segments.**
   It's worth making the underlying data-layout reality explicit, because the whole feature hinges on it:
   
   - The `segmentsConfig` sorted column only applies to **realtime**, and only sorts the consuming segment *at commit time* — a per-segment, single-column property.
   - For **offline**, a column is physically sorted iff the producer fed pre-sorted rows. Pinot detects this and records `isSorted` per column in `ColumnMetadata` (and builds a sorted forward index), so it's *knowable per segment*, but it is never *enforced* or *guaranteed*.
   - **Between segments, nothing is ordered or recorded.** The only cross-segment signal is per-column min/max.
   - A segment can have at most one physically sorted column, so **multi-column** join/group-by keys can rely on physical sortedness for the leading column at best.
   
   The consequence is that the **planner can't prove the leaf emits sorted data** — collation traits are derived from the query, not from segment layout. The only way to *guarantee* sorted input is to inject a sort (which is what the POC's `LeafStageSortJoinRule` does), and an injected unbounded sort is exactly what produces the materialization blowup in Challenge #1. So I think the real foundation of this feature isn't the join/aggregation operators — it's a **runtime-adaptive leaf combine** that k-way merges the segments already sorted on the key (free ordered scan from the sorted forward index) and only sorts the stragglers in memory, emitting incrementally. A k-way heap merge handles "no inter-segment order" naturally — it only needs per-segment sortedness, which we do record. Everything downstream in your diagrams is comparatively mechanical once that exists. I'd also add a **runtime monotonicity assertion** (fail the query if a key goes backwards) rather than trusting a h
 int, since offline sortedness can't be backed up by the engine.
   
   **This same leaf combine directly accelerates plain `ORDER BY` too.**
   Worth calling out because it broadens the payoff and reinforces doing the combine first: a streaming sorted combine that merges per-segment sorted forward indexes and emits incrementally is exactly what `ORDER BY [col] [LIMIT n]` wants — today `MinMaxValueBasedSelectionOrderByCombineOperator` materializes a single block, and the same k-way-merge work in `SortedMailboxReceiveOperator` (which already has a TODO for it) feeds ORDER BY as well. So the leaf-combine investment pays off three ways — sort-merge join, sorted group-by, and ordinary ORDER BY — independent of each other.
   
   **The min/max + null interaction will quietly hurt this feature's effectiveness.**
   This is independent of the feature, but I want to flag it because it directly limits the payoff. Pinot stores nulls as the type **default** value, and for ascending order that default is typically the minimum (`Integer.MIN_VALUE`, `Long.MIN_VALUE`, etc.). That means:
   
   - A segment with **even a single null** in the key column gets its recorded **min dragged down to the default**, so its min/max range artificially overlaps essentially every other segment.
   - Any optimization that uses min/max to **order segments** for streaming emission, or to **prune/skip** segments (the MinMax pruning in `MinMaxValueBasedSelectionOrderByCombineOperator` that the proposal wants to preserve), becomes ineffective — segments stop being separable by range, forcing a full merge and defeating the skip.
   
   So the merge itself stays correct (it only needs `isSorted`), but the min/max-driven *speedups* this feature wants to lean on degrade sharply in the presence of nulls. **This feature would be substantially more effective if we made segment min/max independent of null values** — i.e. compute and store min/max over non-null values only (or track them separately), instead of letting the null-default sentinel pollute the range. That's a generally useful improvement (it also helps the existing min/max ORDER BY pruning) and I think it should be called out as a near-prerequisite for getting good performance here, rather than discovered after the fact.
   
   **Two more correctness notes for the operators:**
   - The merge comparator must consult the **null vector**, not the stored default, or nulls mis-sort; and equi-join is `NULL ≠ NULL`. Hash join gets this for free; a merge join has to implement NULLS ordering + non-matching explicitly.
   - Multi-column keys are constrained to leading-column physical sortedness — worth stating as an explicit limitation (single-column keys like the funnel's `correlation_id` are the clean case).
   
   **How do you plan to enable this at the query level?**
   The join path opts in via a new `join_strategy='sorted'` hint (a proposed value — today the `JoinStrategy` enum only has `HASH`/`LOOKUP`/`AS_OF`), but for the aggregation you describe it as "activated when the leaf stage advertises sorted collation on the group-by keys" — and `is_partitioned_by_group_by_keys` only asserts *partitioning*, not *sortedness*. Could you clarify the intended enablement model? Specifically: is it purely **hint-driven opt-in** (user asserts sortedness, engine trusts + runtime-verifies), or do you envision **auto-enabling when the table config declares a sorted column**? Given that Pinot can't prove cross-segment/offline sortedness, I'd lean toward explicit opt-in backed by the runtime monotonicity assertion as the safety net — but curious how you're thinking about it, since it determines the correctness contract.
   
   Happy to help on the leaf-combine piece — I think that plus the min/max-vs-null fix are the two things that determine whether this lands as a real win or a narrow one.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "rohityadav1993 (via GitHub)" <gi...@apache.org>.
rohityadav1993 commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-4726887758

   Thanks @gortiz for the pointers. 
   > Pinot doesn't enforce or even track order across segments.
   
   My assessment is same about cross segments sort order and the limitation of multi-column sort. 
   **Cross segment sorted stream**
   The idea is as you mentioned, k-way heap merge across segments and utilise sorted index whenever available and sort in memory otherwise as per the query requirement. Implementation wise, the leaf executor should switch between the new combine node vs the existing `MinMaxValueBasedSelectionOrderByCombineOperator`
   
   **Multi-column sort**
   As part of this work I also wanted to propose multi-column sort leveraged by Minions and storing segment metadata to detect the same. While the realtime segments can commit based on the sort order for uploaded segments or segments with older table config we can rely on minions to re-sort the segments.
   A runtime monotonicity assertion on the leaf output (fail the query if a key regresses) is a good safety measure rather than trusting the hint alone.
   
   >This same leaf combine directly accelerates plain ORDER BY too.
   
   Yes, this was a motivation as well. A few engines like ClickHouse already do this. I haven't done the analysis yet, but sorting on important columns should also help reduce the heap overhead of indexes.
   
   I'm in progress of implementing a `StreamingMinMaxValueBasedSelectionOrderByCombineOperator` based on a k-way heap merge, plus the `SortedMailboxReceiveOperator` TODO you pointed at. Currently I'm ironing out lazily reading from segments in the combine operator, to avoid materializing complete segments in memory beyond what's needed upstream.
   
   >The min/max + null interaction will quietly hurt this feature's effectiveness.
   
   Thanks for the catch — this will be needed. The merge itself stays correct on `isSorted` alone, so this degrades the pruning speedup rather than correctness. In practice the majority of target query shapes key on non-null columns (e.g. `correlation_id`), so I'd like to make the streaming-merge POC work first and address null-independent min/max as a fast follow. For multi-column sorts, restricting pruning to the leading column keeps this tractable.
   
   Aligned on tracking min/max over non-null values (or storing a separate non-null min/max).
   
   >Two more correctness notes for the operators:
   
   **Null handling.** At the MSE join layer nulls arrive as real Java `null`s, so the operator filters them before any key comparison and implements `NULL ≠ NULL` directly (null left rows are null-padded on LEFT, null right rows are dropped) — `compareValue` never sees a null. I think we'll need to make `SelectionPlanNode` null-aware here — it currently skips `isSorted` entirely when null handling is enabled, so I'll look at supporting a null-aware sorted path instead.
   
   **Multi-column / leading-column sortedness.** Agreed this is limited to the leading column today. I'd like to propose first-class multi-column segment sort (tied to the proposal in the first point) — is there historical context for Pinot supporting only a single sorted column? I've been drawing on ClickHouse's [primary key](https://clickhouse.com/docs/best-practices/choosing-a-primary-key) / sort-key design.
   
   >How do you plan to enable this at the query level?
   
   Opt-in via hints, for exactly the reason you noted — segment sortedness isn't known at planning time. For the join that's the new `join_strategy='sorted'` enum value; the planner only checks equi-keys + INNER/LEFT shape and trusts the asserted sortedness, backed by the runtime monotonicity assertion from the first point.
   For aggregation, `is_partitioned_by_group_by_keys` only asserts partitioning (it emits a DIRECT aggregate and skips the exchange, but stays hash-based), so streaming sorted aggregation would need a separate opt-in asserting sortedness on the group-by keys.
   
   ## Next step:
   I'm putting up a POC with the leaf-stage implementation; I'll apply the pointers above and share findings so we can go over the code in the next discussion.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "xiangfu0 (via GitHub)" <gi...@apache.org>.
xiangfu0 commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-4636020050

   @siddharthteotia 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "gortiz (via GitHub)" <gi...@apache.org>.
gortiz commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-4750846595

   > As part of this work I also wanted to propose multi-column sort leveraged by Minions and storing segment metadata to detect the same. While the realtime segments can commit based on the sort order for uploaded segments or segments with older table config we can rely on minions to re-sort the segments.
   
   That is cool. But is it actually necessary? I can see 2 scenarios when ORDER BY A, B is executed on a segment sorted by A:
   - A is already selective enough. All rows with equal A will be stored in order. Inside this partition, they won't be sorted by B, but there shouldn't be that many elements, so sorting inside the partition should be pretty fast.
   - A is not very selective (imagine 2 values for 1M rows). Then sorting by A is not useful, we could be sorting by B instead.
   
   I'm not saying the multi-col sorter isn't useful, but it's probably not a high priority.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "rohityadav1993 (via GitHub)" <gi...@apache.org>.
rohityadav1993 commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-4614282652

   cc: @ankitsultana, @darioliberman 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org


Re: [I] [MSE] Leverage pre-sorted pre-partitioned data layout: streaming merge join and sorted group-by aggregation [pinot]

Posted by "github-actions[bot] (via GitHub) via commits" <co...@pinot.apache.org>.
github-actions[bot] commented on issue #18667:
URL: https://github.com/apache/pinot/issues/18667#issuecomment-6933516436

   This issue has been automatically marked as stale because it has not had any activity in the last 150 days. It will be closed in 30 days if no further activity occurs. If this issue is still relevant, please leave a comment to keep it open. Thank you for your contributions!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org