kongfanshen-0801 commented on PR #1799: URL: https://github.com/apache/cloudberry/pull/1799#issuecomment-4729091553
> Thanks, if have a chance, we could do some comparison of before and after for TPC-H Q 21 Done — ran TPC-H Q21 before/after on the `optimizer_enable_right_semi_join` GUC. **Setup:** SF=100 (lineitem 600,037,902 rows), GPORCA, 3-segment single-host demo cluster, 3 timed runs each. | `optimizer_enable_right_semi_join` | semijoin plan | runs (s) | best | |---|---|---|---| | **on** | **Hash Right Semi Join** | 355.8 / 340.1 / 339.2 | **339.2** | | **off** | Hash Semi Join | 353.0 / 349.3 / 352.2 | 349.3 | Q21's `EXISTS (l2 ...)` semijoin is where the two plans diverge: - **on** — `Hash Right Semi Join`: builds the hash on the small LHS (**448,837 rows, 2 batches, ~17 MB**) and streams `lineitem l2` (200M rows). - **off** — `Hash Semi Join`: builds the hash on `lineitem l2` (**200,042,924 rows, 512 batches** → heavy spill), `Work_mem wanted: 8.6 GB`. So the right-semi shape shrinks the build side from ~200M to ~449K rows, cuts the semijoin hash spill from **512 → 2 batches**, and drops requested `work_mem` from **~8.6 GB → ~14 MB**. End-to-end wall-clock is ~3% better here because on this small cluster the total is dominated by the three `lineitem` scans (l1/l2/l3) and the `l3` anti-join spill that are common to both plans; the build-side / memory win should grow on larger, properly-sized clusters and with tighter memory settings. I also added an ORCA regression test (`src/test/regress/sql/rightsemijoin.sql`, with both planner and `_optimizer` answer files) asserting the GUC flips the plan between right-semi/anti and the regular semi/anti shapes with identical results, and wrote the Q21 numbers into the commit message. PTAL, thanks! -- 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]
