matanper opened a new issue, #70414: URL: https://github.com/apache/doris/issues/70414
### 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 master (`5684b974c2b`), also 4.1.x. ### Description When a query ANDs several indexed predicates, `SegmentIterator` evaluates every index-capable predicate against the index over the whole segment. The only early exit is an empty row bitmap. So adding a broad numeric equality to a query whose narrow term filters already leave only a handful of rows (for example, an event-type code that matches ~60% of rows) adds the following work to every segment: - **A full BKD lookup.** `InvertedIndexIterator::read_from_index` (`be/src/storage/index/inverted/inverted_index_iterator.cpp:94-106`) runs the lookup. With `inverted_index_skip_threshold` it only counts the hits, returns `INVERTED_INDEX_BYPASS`, and the predicate is then evaluated on the rows anyway. The bypass result is not cached, so every query on every segment repeats the count. - **Order by hash map, not selectivity.** Predicates run in the iteration order of `phmap::flat_hash_map _slot_id_to_predicates` (`be/src/exec/operator/scan_operator.h:351`, consumed at `be/src/exec/scan/olap_scanner.cpp:439`). Broad lookups can run before narrow ones. - **Column predicates before pushed-down conjuncts.** Column predicates are applied first (`segment_iterator.cpp:1045`, loop at `:2027`, empty-bitmap exit at `:1942`), and every pushed-down conjunct comes after them (`:1054`). An expensive MATCH_PHRASE conjunct therefore runs before nothing that could empty the bitmap. A narrow term conjunct (`ARRAY_CONTAINS`) runs after the broad BKD predicates. - **VARIANT casts pay twice.** Casts of VARIANT subcolumns can reach the iterator both as a column predicate and as a pushed-down conjunct, so the broad BKD lookup is paid twice per segment. ### Solution 1. Apply term lookups (EQ/IN on string or array indexes) and conjuncts over such columns first. Then BKD, range and null-test predicates, and conjuncts that read only BKD-indexed columns. Then MATCH/SEARCH conjuncts. 2. Once the candidate bitmap is small (at most an absolute row threshold and at most a fraction of the segment, both configurable), leave the remaining BKD lookups to the row path. Term lookups, MATCH, SEARCH and COUNT_ON_INDEX keep using the index. With both changes, on a 96M-row table with 326 segments, the narrow + broad query below no longer performs any of its 1,276 BKD downgrades and index query-cache misses. Its index filter time fell from 38.5 ms to 21 ms (narrow-only: 20 ms). Results were identical. ### How to Reproduce? - A table with a VARIANT column with typed bigint subpaths `a` and `b`, each with a `field_pattern` inverted index (BKD); text subpaths `h`, `u`, `p` with inverted indexes; and an `array<text>` column `hs` with an inverted index. - 96M rows with auto compaction off, ~250K rows per segment. `a = 1007` matches ~60% of rows and `b = 1` ~90%. One `hs` value occurs in 200 rows, and those rows share one `h`/`u`/`p` value. - N: `WHERE array_contains(hs,'x') AND cast(v['h'] as string)='h1' AND cast(v['u'] as string)='u1' AND cast(v['p'] as string)='p1'` - B: N `AND cast(v['a'] as bigint)=1007 AND cast(v['b'] as bigint)=1` Warm, B shows `InvertedIndexDowngradeCount` ≈ 4 × segments, as many `InvertedIndexQueryCacheMiss`, about 1.7× the `InvertedIndexFilterTime` of N, and +24% query CPU for the same narrow result. ### Anything Else? #67171 (the empty-bitmap exit for pushed-down conjuncts) is on master but not in 4.1.x. We have a patch and can open a PR. ### Are you willing to submit PR? - [x] 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]
