morningman opened a new pull request, #66435:
URL: https://github.com/apache/doris/pull/66435
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
An Iceberg `MERGE INTO` that is semantically equivalent to a 14s `UPDATE`
ran for **18 minutes** on TPC-DS 10TB. `explain verbose` showed the target-side
scan reading the whole table with **no predicates and no runtime filters**,
caused by two planner defects:
**1. A mark join blocks runtime filters (general optimizer issue, not
MERGE-specific).**
When an `IN`/`EXISTS` subquery is written inside a join `ON` clause,
subquery unnesting produces a **mark join**. Predicate push-down later moves
the mark conjunct down to the target branch as a bare filter conjunct — but the
join itself stays a mark join, and `RuntimeFilterGenerator` refuses to generate
runtime filters on mark joins (`RuntimeFilterGenerator.java`, the
`isMarkJoin()` check). The decisive evidence: the `UPDATE` plan and the `MERGE`
plan contain the *same* `LEFT SEMI JOIN(BROADCAST)` on `ss_sold_date_sk =
d_date_sk` over the same tables — the only difference is `isMarkJoin: false` vs
`true`, and only the former produces RFs, so only the `UPDATE` prunes the
target scan from ~6 years of data down to 1 year.
The same condition written in `WHERE` is fine; written in `ON` it degrades —
plain `SELECT`s are affected too.
**2. Both MERGE paths build `source LEFT OUTER JOIN target`.**
Doris builds the hash table on the right child, so the structurally wide
side — the target must carry *all* columns plus the row identity (for Iceberg:
`struct<file_path, row_position, ...>` with a full S3 URI per row) — always
became the build side. Worse, `LEFT_OUTER_JOIN` is in `DENIED_JOIN_TYPES`, so
the merge join could never produce runtime filters at all. The internal OLAP
path (`MergeIntoCommand`) used `LEFT_OUTER_JOIN` unconditionally, even without
`WHEN NOT MATCHED` clauses (the external path already had the INNER
optimization). For reference, Trino plans MERGE as `target RIGHT JOIN source`
and only allows dynamic filters on `INNER || RIGHT` — exactly complementary to
Doris's denied list.
**Changes:**
1. **Add the `ELIMINATE_MARK_JOIN` rewrite rule** (the `RuleType` enum entry
existed but was never implemented). When a `LEFT SEMI` mark join's mark slot is
consumed only as a bare conjunct of the filter directly above it, requiring
`TRUE` discards `NULL` exactly like `FALSE`, so the pair degenerates to a plain
`LEFT SEMI JOIN` — which may produce runtime filters. A literal-`TRUE` alias
keeps the mark slot's `ExprId` alive for any upper references (after the filter
the mark can only be `TRUE`); column pruning removes it when unused. Registered
in `PUSH_DOWN_FILTERS` next to `EliminateOuterJoin`; can be disabled with `set
disable_nereids_rules='ELIMINATE_MARK_JOIN'`. Out of scope by design: mark
slots consumed inside `OR`/projections, and `NOT IN` (anti mark joins).
2. **Extract `MergeUtils.buildMergeJoin()`**, shared by `MergeIntoCommand`
(internal OLAP MOW) and `ExternalRowLevelMergePlanBuilder` (external): the
target now stays on the **left (probe)** side. Without `WHEN NOT MATCHED` the
join is `INNER`; with them it is `target RIGHT OUTER JOIN source`, which
preserves exactly the same unmatched source rows as the previous shape while
allowing runtime filters built from the (narrow) source side to prune the
target scan — `RIGHT_OUTER_JOIN` is not in `DENIED_JOIN_TYPES`, and BE fully
supports it. The internal path also gains the `INNER` optimization it
previously lacked. `JoinCommute` remains in the search space, so the CBO can
still swap sides when statistics justify it.
After this PR, the pathological MERGE plan gets: a non-mark semi join
producing RFs onto the target scan (partition pruning), the merge join eligible
for RFs, and the narrow source side as the hash-table build side.
### Release note
Improve MERGE INTO (and queries with IN/EXISTS subqueries in join ON
clauses) performance: eliminate filter-consumed mark joins to unlock runtime
filters, and put the MERGE target on the probe side of the join.
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [x] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [x] Yes. Query results are unchanged; plan shapes change (`explain`
output): filter-consumed mark joins become plain semi joins, and the MERGE base
join becomes `INNER` / `target RIGHT OUTER JOIN source` instead of `source LEFT
OUTER JOIN target`.
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR should
merge into -->
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
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]
