matanper opened a new issue, #70117: URL: https://github.com/apache/doris/issues/70117
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 4.1.4. `PhysicalPlanTranslator.checkPushSort` on master has the same check. ### What's Wrong? A Top-N on a DUPLICATE KEY table is only pushed to the scan's read-in-key-order path (`SORT INFO` / `SORT LIMIT` on the OlapScanNode, `read_orderby_key` in the BE) when the ORDER BY columns are a prefix of the table's sort key. The common multi-tenant shape never qualifies: ```sql CREATE TABLE logs (tenant VARCHAR(64) NOT NULL, ts DATETIME NOT NULL, ...) DUPLICATE KEY(tenant, ts) ...; SELECT * FROM logs WHERE tenant = 't1' ORDER BY ts DESC LIMIT 100; ``` Inside each segment, the rows for `tenant = 't1'` are already sorted by `ts`, so each tablet could stop after its newest 100 rows. But `checkPushSort` compares `ORDER BY ts` with the sort key `(tenant, ts)`, sees `ts ≠ tenant`, and refuses. Writing `ORDER BY tenant DESC, ts DESC` doesn't help either: the planner removes `tenant` from the ORDER BY because the WHERE clause fixes it to one value, and the result is the same plan. So every tablet reads the sort column of every row for that tenant. The Top-N runtime filter only cuts the scan once a threshold exists, and the cost grows with the time range. Synthetic measurement: one FE + one BE, 1 billion rows over 31 daily partitions, `DISTRIBUTED BY RANDOM BUCKETS 16` (496 tablets, 10 rowsets each), half the rows in one tenant. Query: `WHERE tenant = 't1' AND ts >= <start> ORDER BY ts DESC LIMIT 100`. | Range | Today | With the sort pushed to the scan (patched, see below) | |---|---|---| | 31 days | 290 ms, 526M rows scanned, 19.1 s CPU | 66 ms, 496K rows scanned, 0.7 s CPU | | 7 days | 94 ms, 119M rows, 4.3 s CPU | 42 ms, 112K rows, 0.17 s CPU | | 1 day | 60 ms, 17M rows, 0.5 s CPU | 30 ms, 16K rows, 0.03 s CPU | The returned `ts` sequence was identical in every case. The gap widens with data volume per tablet. ### What You Expected? When the scan's predicates fix the leading sort key columns to single values (`k = literal`), `checkPushSort` should match the ORDER BY keys against the sort key columns that follow them. ### How to Reproduce? ```sql CREATE TABLE logs (tenant VARCHAR(64) NOT NULL, ts DATETIME NOT NULL, v VARCHAR(64)) DUPLICATE KEY(tenant, ts) DISTRIBUTED BY RANDOM BUCKETS 3 PROPERTIES ('replication_num' = '1'); -- no SORT INFO / SORT LIMIT on the OlapScanNode: EXPLAIN SELECT * FROM logs WHERE tenant = 't1' ORDER BY ts DESC LIMIT 100; -- for comparison, this one is pushed: EXPLAIN SELECT * FROM logs ORDER BY tenant DESC, ts DESC LIMIT 100; ``` ### Anything Else? A fix needs two parts. We have both working locally, with tests: 1. **FE (`PhysicalPlanTranslator.checkPushSort`):** collect the leading sort key columns that a scan conjunct fixes to a non-null literal, and try matching the ORDER BY keys after 0, 1, … of them. When a match needs pinned columns, give the scan its own `SortInfo` with those columns prefixed, in the direction of the first ORDER BY key. The storage layer reads the first N sort key columns in order, where N is the length of the scan's sort info. The SortNode keeps its own sort info. 2. **BE (`VCollectIterator::_topn_next`):** while reading in key order, the scanner publishes the Top-N runtime predicate value from `read_orderby_key_columns[0]`. With a prefixed sort info, that column is the pinned one (e.g. a string), not the ORDER BY key the predicate targets (e.g. a datetime). Every such query then fails with `Field::get type mismatch: requested DATETIMEV2, actual STRING`. The value should come from the column the runtime predicate targets (its column id among the reader's return columns), falling back to the first sort key column as today. Part 2 is only reachable through part 1, so both need to land together. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
