kongfanshen-0801 commented on PR #1799: URL: https://github.com/apache/cloudberry/pull/1799#issuecomment-4737519630
Re-ran Q21 (SF=100) at a **production-level `statement_mem` (7 GB)** instead of the small default, so the build-side difference shows up as a spill that the right-semi plan avoids: | `optimizer_enable_right_semi_join` | semijoin build hash | l3 anti-join (common) | time (2 runs) | Memory wanted | |---|---|---|---|---| | **on** | 449K rows — **1 batch, no spill** | 126M rows, 4 batches | 354.0 / 355.6 s | 38 GB | | **off** | lineitem l2 200M rows — **8 batches, spills 1.14 GB** | 126M rows, 4 batches | 388.3 / 397.7 s | 60 GB | At realistic memory the **off** plan spills the 200M-row semijoin build (8 batches), while **on** keeps it fully in memory (1 batch) → ~10% end-to-end and total *Memory wanted* 60 GB → 38 GB. (At the tiny default `statement_mem` both plans spill heavily, which compresses the gap to ~3%.) The end-to-end gain is bounded because Q21 is **scan-bound** in the row executor: the three `lineitem` scans (l1/l2/l3, ~452M rows) and the common `l3` anti-join spill dominate runtime regardless of the GUC. Isolating the semijoin (small LHS vs a unique 150M-row RHS, so de-dup doesn't help the off plan) shows the operator-level win directly: **~20 s (on) vs ~77 s (off) ≈ 3.9×**. On bumping the scale further: SF=500 isn't feasible on this box — the loaded SF=100 set is already ~216 GB, so SF=500 would be ~1 TB+ of table data alone and exceeds the disk. More importantly, in the **row** executor a larger SF mostly scales both plans together (still scan-bound), so it wouldn't move the ratio much; the spill-avoidance is best demonstrated by the build-side-vs-`statement_mem` relationship above. The relative win grows in a **vectorized** executor where the common scan / tuple-deform cost is far cheaper. -- 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]
