kongfanshen-0801 commented on PR #1799: URL: https://github.com/apache/cloudberry/pull/1799#issuecomment-4767451416
### Larger-scale performance comparison (SF≈300, GPORCA) Loaded a larger TPC-H data set (lineitem 1.8B rows, orders 450M rows) to show the right-semi win at scale and the spill it avoids. **Note on Q21 itself at this scale:** with 1.8B-row `lineitem`, GPORCA does *not* pick a right-semi join for Q21's `EXISTS (lineitem l2 …)` — both sides of that semijoin are billion-row tables, so the cost model correctly keeps the regular Hash Semi Join (building the hash on the smaller LHS only pays off when the LHS is actually small). That is the cost model behaving sensibly, not a regression. To isolate the operator where right-semi *is* the right choice — a small probe-side driving table against a large, unique build key — I used `orders.o_orderkey` (450M unique rows) probed by a 10K-row table: ```sql select count(*) from skeys s where s.k in (select o_orderkey from orders); -- 10K vs 450M ``` | `optimizer_enable_right_semi_join` | plan | build side | spill | exec time (2 runs) | |---|---|---|---|---| | **on** | `Hash Right Semi Join` | 3,385-row LHS, **1 batch, no spill**, 4 MB | none | **62 s / 66 s** | | **off** | `HashAggregate` (dedup) + `Hash Join` | dedup **150M rows, 1281 batches**, disk 4.1 GB, work_mem wanted 16.9 GB | **4.1 GB** | **285 s / 297 s** | → **~4.6× faster** (≈62 s vs ≈285 s). The off plan must de-duplicate the 450M-row side, spilling **4.1 GB** across 1281 batches and asking for ~16.9 GB of work_mem; the right-semi plan builds the hash on the 3,385-row LHS, stays in memory (1 batch, 4 MB) and just streams `orders`. The win grows with scale (≈3.9× at SF=100 → ≈4.6× here) and, more importantly, eliminates the large-side dedup spill entirely — which is exactly the case this backport targets. (SF=500 was attempted but the loaded heap tables exceed the 1 TB test disk before the load completes — physical size is ~2× the logical size — so this SF≈300 run is the largest that fits here.) -- 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]
