alexch2000 commented on issue #19339:
URL: https://github.com/apache/pinot/issues/19339#issuecomment-5407558574
We hit the same cliff, from a different direction. Instead of a subquery
inside the OR, the OR is on two time ranges (day-over-day comparison) and the
scan appears only on segments that lack the configured range index.
**Query shape** (v1 engine, realtime table, ~134K segments):
```sql
SELECT <10-min bucket>, FUNNELCOUNT(...)
FROM events
WHERE (
(name = 'A' AND flow_id = 'F' AND action_id = 'X')
OR (name = 'B' AND flow_id = 'F')
)
AND device_os IN ('android', 'ios')
AND (
(ts >= :a0 AND ts < :a1)
OR (ts >= :b0 AND ts < :b1) -- b = a - 24h
)
GROUP BY 1
```
`name`, `flow_id`, `action_id`, `device_os` have inverted indexes. `ts` is
the time column and has `rangeIndexColumns` configured, but ~3% of segments are
missing the index on disk (separate issue, being fixed by reload).
**Numbers**
| | window A only | A OR B |
|---|---|---|
| `numEntriesScannedInFilter` | 101,187 | **441,757,659** |
| `numDocsScanned` | 66,800 | 154,975 |
| `numSegmentsProcessed` | 1,384 | 4,541 |
| `realtimeThreadMemAllocatedBytes` | 684 MB | 1.83 GB |
~4,400× more filter work for ~2.3× more matched rows. Window B alone behaves
like window A alone.
**Verbose EXPLAIN on the OR query** (all cohorts, 4,588 segments after
pruning):
| plan on the time predicate | segments |
|---|---|
| single `FILTER_RANGE_INDEX` under `FILTER_AND` | 3,449 |
| `FILTER_OR` → 2× `FILTER_RANGE_INDEX` | 1,004 |
| single `FILTER_FULL_SCAN` under `FILTER_AND` | 44 |
| **`FILTER_OR` → 2× `FILTER_FULL_SCAN`** | **83** |
| `FILTER_EMPTY` | 8 |
The 83 segments account for essentially all of the 441M: ~2.7M docs × 2
predicates each, i.e. a full pass over the column per range predicate. The 44
unindexed segments whose scan sits directly under the AND cost almost nothing,
because `AndDocIdSet` hands them the intersected inverted-index bitmap via
`ScanBasedDocIdIterator.applyAnd`. The 83 get no candidate set because
`OrDocIdSet` has no equivalent, and `AndDocIdSet#iterator()` calls `iterator()`
on the OR child before it has computed anything.
Two details that made this particularly sharp for us:
- Per segment, a range predicate that can't match the segment's min/max
becomes `EmptyFilterOperator` and is dropped from the OR, so the OR only
survives on segments that overlap *both* windows.
- Each window on its own never exposes the missing index (~100K entries), so
this was invisible until both windows were combined in one query.
**On the two proposed fixes**
+1 to option 1 (push `applyAnd` down into composite doc-id sets). For our
shape, #19350 as currently scoped would help only marginally: the only conjunct
it can distribute is `device__os IN ('android','ios')`, which matches most
rows. The selective part of the outer AND is the `OR(AND(name, flow_id,
action_id), AND(name, flow_id))` block, which isn't a single-column EQ/IN and
so wouldn't be pushed into the time-range branches.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
