[CALCITE-5559] Improve RepeatUnion by discarding duplicates at TableSpool level

• Type: Improvement
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 06/Mar/23 11:47
• Updated: 21/Mar/23 09:41

Description

Currently, RepeatUnion operator with all=false keeps track of the elements that it has returned in order to discard duplicates. However, the TableSpool operators that are right below it do not have such control. In certain scenarios, duplicates are returned by the TableSpool current iteration, discarded by the RepeatUnion, but have been already "fed back" by the TableSpool into the next iteration, causing unnecessary processing.
We can optimize this scenario by keeping track of the duplicates inside/before the TableSpool too (note: we still need to keep track of duplicates at RepeatUnion level, because that is the only place where we can detect a potential "global duplicate" of an element: returned by the LHS and then also by the RHS, or by two different iterations of the RHS).

A PoC testing this improvement on a downstream project showed that certain queries can go from ~40s down to ~1s.

Issue Links

• is related to: CALCITE-3542 Implement RepeatUnion All=false
• links to: GitHub Pull Request #3101