matanper opened a new issue, #70227: URL: https://github.com/apache/doris/issues/70227
### Search before asking - [x] I searched the issues and found no similar issue. Related: #70135 (Top-N scans open every rowset and segment before checking the threshold), #66652 (Top-N filters generated from aggregation). ### Description A Top-N over a table RANGE-partitioned on the sort column, for example `SELECT ... WHERE tenant = ? AND ts >= now() - INTERVAL 120 DAY ORDER BY ts DESC LIMIT 100` on a table partitioned by day on `ts`, plans a scanner for every tablet of every partition in range. Once the first partitions have produced `n` rows, the Top-N threshold excludes every older partition entirely, but their scanners are still submitted to the scan pool, wait for a worker, open their tablets and only then have every row filtered by the runtime predicate. On master: - `OlapScanNode` (FE) sends no partition bounds and `TPaloScanRange` carries no partition id, so the BE cannot tell which partition a scanner reads or what range of the sort column that partition can hold. - Scan ranges are assigned in no particular partition order, so each scan instance may start with old partitions; the threshold forms late and excludes little. - `ScannerContext::_pull_next_scan_task` (`be/src/exec/scan/scanner_context.cpp`) submits every pending scanner; nothing consults the Top-N runtime predicate before submission or before `prepare()`. With many partitions in range the scan-pool wait dominates. On a production log table (daily partitions, 4-month range, one tenant) a single query created 8,110 scanners for 6,565 tablets; their summed `ScannerWorkerWaitTime` was about 7,460 s, roughly 0.9 s per scanner, while each scanner then did about 0.5 ms of work because almost all of its data lay beyond the threshold. ### Solution Skip a whole partition once the threshold rules it out, before its scanners reach the pool: 1. FE: when a Top-N runtime filter of the scan targets the table's single RANGE partition column (a plain slot of an integer or date/datetime type) and at least two partitions are selected, send each selected partition's `[lower, upper)` bound typed as the column (a new `TOlapScanNode` field, with the target slot id), and the partition id on each `TPaloScanRange`. Leave a side unset for MINVALUE/MAXVALUE or a key that is not exactly a value of the column, so it can never rule the partition out. 2. FE: order the scan ranges by partition, highest upper bound first for a descending sort and lowest lower bound first for an ascending one, keeping the tablet order within a partition. Every instance then starts with the partitions the threshold forms from. 3. BE: tag each scanner with its tablet's partition, keep the scanners in that order, and before submitting a pending scanner ask whether the runtime predicate at its current threshold rules out the whole partition bound (the same inclusive comparison the zone-map check uses; a partition with no lower bound is kept under NULLS FIRST). If so, finish the scanner without submitting it. Repeat the check before `prepare()` for a scanner submitted before the threshold tightened. Remember a ruled-out partition per instance, since the threshold only narrows. 4. Report it: `ScannersSkippedByTopNPartitionBound`, `PartitionsSkippedByTopNPartitionBound`. The threshold always comes from `n` rows the query has read, so a partition ruled out this way holds no row the result needs. The new thrift fields are optional, so mixed FE/BE versions simply behave as today. On a 1-billion-row table with 31 daily partitions (16 random buckets, `ORDER BY event_time DESC LIMIT 100` for one tenant over 30 days), 468 of 496 scanners were skipped before submission; the cold-cache query went from 422 ms to 286 ms with the summed scan-pool wait falling from 2,495 ms to 4 ms, and eight concurrent queries finished in 126 ms instead of 361 ms, with identical results on 12 Top-N query shapes (ASC/DESC, OFFSET, extra filters, IN lists). We are happy to contribute a PR for master if this direction is welcome. ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! -- 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]
