alamb opened a new issue, #8777: URL: https://github.com/apache/arrow-datafusion/issues/8777
### Is your feature request related to a problem or challenge?
The core usecase is:
```sql
with x as (
<expensive full join of two large tables producing small-ish result>
)
select * from x where ...
union all
select * from x where ...
union all
select * from x where ...
```
DataFusion will effectively run the subquery `x` three times (it will
basically copy the `LogicalPlan` for `x` wherever it is used.
```
┌─────────────────────┐
│ UNION ALL │
│ │
└─────────────────────┘
▲ ▲ ▲
│ │ │
┌─────────────────────────────┘ │
└─────────────────────────────┐
│ │
│
│ │
│
┌────────────────┐ ┌────────────────┐
┌────────────────┐
│ Filter 1 │ │ Filter 2 │ │
Filter 3 │
└────────────────┘ └────────────────┘
└────────────────┘
▲ ▲
▲
│ │
│
│ │
│
┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐ ┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐ ┌ ─ ─
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐
┌────────────────┐ ┌────────────────┐
┌────────────────┐
│ │ Expensive Join │ │ │ │ Expensive Join │ │ │ │
Expensive Join │ │
└────────────────┘ └────────────────┘
└────────────────┘
│ ▲ │ │ ▲ │ │
▲ │
┌──────┴──────┐ ┌──────┴──────┐
┌──────┴──────┐
│ │ │ │ │ │ │ │ │
│ │ │
.───────. .───────. .───────. .───────.
.───────. .───────.
│ ╱ ╲ ╱ ╲ │ │ ╱ ╲ ╱ ╲ │ │ ╱
╲ ╱ ╲ │
( Input 1 ) ( Input 2 ) ( Input 1 ) ( Input 2 ) (
Input 1 ) ( Input 2 )
│ `. ,' `. ,' │ │ `. ,' `. ,' │ │ `.
,' `. ,' │
`─────' `─────' `─────' `─────'
`─────' `─────'
│"x" │ │"x" │ │"x"
│
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─
```
This design has certain benefits:
1. It is straightforward to implement (probably why DataFusion is like this)
3. If the different `UNION ALL` arms have different predicates, they could
potentially be pushed down in one branch but not the others.
### Describe the solution you'd like
However, in many cases it would likely be better to do to the expensive join
only once and reuse the results like this:
```text
┌─────────────────────┐
│ UNION ALL │
│ │
└─────────────────────┘
▲ ▲ ▲
│ │ │
┌─────────────────┘ │ └───────────────────┐
│ │ │
│ │ │
│ │ │
┌────────────────┐ ┌────────────────┐ ┌────────────────┐
│ Filter 1 │ │ Filter 2 │ │ Filter 3 │
└────────────────┘ └────────────────┘ └────────────────┘
▲ ▲ ▲
│ │ │
│ │ │
└────────────────────┐ │ ┌─────────────────────┘
│ │ │
│ │ │
│ │ │
┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐
┌────────────────┐
│ │ Expensive Join │ │
└────────────────┘
│ ▲ │
┌──────┴──────┐
│ │ │ │
.───────. .───────.
│ ╱ ╲ ╱ ╲ │
( Input 1 ) ( Input 2 )
│ `. ,' `. ,' │
`─────' `─────'
│"x" │
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─
```
### Describe alternatives you've considered
I think there are several considerations for this design, the biggest is
that it is a 'diamond' plan where the same stream can be consumed at different
rates potentially needing to buffer the entire intermediate result or else the
plan will deadlock
For example
```
┌─────────────────────┐
│ Hash Join │
│ │
└─────────────────────┘
▲ ▲
│ │
┌─────────────┘ └────────────┐
│ │
│ │
│ │
Build Side Probe Side
(read completely (not read at all until Build
before probe side) Side is completely read)
▲ ▲
│ │
│ │
└────────────────────┬────────────────┘
│
│
│
┌ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ┐
┌────────────────┐
│ │ Expensive Join │ │
└────────────────┘
│ ▲ │
┌──────┴──────┐
│ │ │ │
.───────. .───────.
│ ╱ ╲ ╱ ╲ │
( Input 1 ) ( Input 2 )
│ `. ,' `. ,' │
`─────' `─────'
│"x" │
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─
```
### Additional context
This came from a [discord
thread](https://discord.com/channels/885562378132000778/1166447479609376850/1193369889323356221)
from @sergiimk
--
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]
