kongfanshen-0801 commented on PR #1799:
URL: https://github.com/apache/cloudberry/pull/1799#issuecomment-4737174706
Follow-up on the Q21 numbers above — clarifying *why* the end-to-end gain is
only ~3%, and showing the isolated win.
### Where Q21's time actually goes (row executor)
Q21 scans `lineitem` three times and the cost is dominated by parts that are
**identical** with the GUC on or off:
```
Hash Right Semi Join (l1 ⋈ l2) <- the ONLY node right-semi changes
├─ Seq Scan lineitem l2 = 200,042,924
└─ Hash Anti Join (l1 ⋈ l3)
├─ Hash Join (l1 ⋈ supplier)
│ └─ Seq Scan lineitem l1 = 126,464,414
└─ Hash(build l3) = 126,464,414, Batches: 256 (spills)
└─ Seq Scan lineitem l3 = 126,464,414
```
1. **Three `lineitem` scans + per-row tuple deform** ≈ 452M rows (~3 × 74 GB
I/O plus deforming 16 columns/row in the row executor) — the single biggest
chunk.
2. **The `l3` anti-join builds a 126M-row hash that spills to 256 batches**
— a separate large hash join that right-semi does not touch; identical on both
sides.
3. The two big joins' **probe** work (hashing/comparing 200M + 126M rows +
the `l_suppkey <> l_suppkey` join filter).
Right-semi only changes the build side of the **outer** semijoin (`l1 ⋈ l2`):
| | semijoin build side | spill | work_mem wanted |
|---|---|---|---|
| **on** | small LHS, 448,837 rows, 2 batches, in-mem | none | 14 MB |
| **off** | `lineitem l2`, 200,042,924 rows, 512 batches | ~heavy | 8.6 GB |
The 200M `l2` rows are scanned either way (as probe when on, as build when
off), so the **only net delta** is the cost of building + spilling that
200M-row hash. Relative to the 452M-row scans and the `l3` 256-batch spill,
that delta is small → ~3% wall-clock, even though total "Memory wanted" drops
**60 GB → 38 GB**.
### Isolated semijoin: ~4× when it's the dominant cost
Removing the 3× full scans (small 10k-row LHS vs a unique 150M-row RHS,
`orders.o_orderkey`, GPORCA), so the semijoin *is* the cost:
| `optimizer_enable_right_semi_join` | plan | exec time (2 runs) |
|---|---|---|
| **on** | `Hash Right Semi Join` — build on 3,385-row LHS, no spill |
**19.7 / 19.7 s** |
| **off** | fallback `HashAggregate(dedup 50M) + Hash Join` — 641 batches,
1.5 GB spill, 4.6 GB wanted | **77.1 / 78.4 s** |
→ **~3.9× faster** (≈20 s vs ≈77 s) once the build-side choice is the
bottleneck.
### Row vs vectorized
In the **row executor**, steps 1 + 3 (scanning/deforming ~452M rows and
row-at-a-time hash/probe) dominate and dilute the win. In a
**vectorized/columnar** engine those steps get much cheaper (column-pruned,
batched), so the relative weight of avoiding the 200M-row build+spill grows and
the Q21 wall-clock gain becomes more pronounced — consistent with the larger
Q21 improvement seen on the vectorized build this was ported from.
**Summary:** the right-semi plan is genuinely chosen and the per-operator
win is large (~4× isolated, 200M→449K build, 512→2 batches, 8.6 GB→14 MB); it
just sits behind scan/deform-bound work in Q21 on the row executor, hence ~3%
end-to-end here. `optimizer_enable_right_semi_join` defaults to **on**.
--
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]
