Pushdown top level UNION/UNION ALL queries
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Citus currently supports pushing down some `UNION` and `UNION ALL` queries. The requirements are
(i) distribution columns should align in the target list
(ii) Set operation should be inside a subquery
An example query where `user_id` is the distribution column:
```SQL
SELECT user_id, counter
FROM (
SELECT user_id, value_2 % 10 AS counter FROM events_table WHERE event_type IN (1, 2)
UNION
SELECT user_id, value_2 % 10 AS counter FROM events_table WHERE event_type IN (5, 6)
) user_id
ORDER BY 2 DESC,1
LIMIT 5;
```
We should be able to relax the second item `(ii) Set operation should be inside a subquery` and pushdown the following:
```SQL
SELECT user_id, value_2 % 10 AS counter FROM events_table WHERE event_type IN (1, 2)
UNION
SELECT user_id, value_2 % 10 AS counter FROM events_table WHERE event_type IN (5, 6)
```
Note that with 7.2, Citus is able to recursively plan this query, which might be **much more expensive operation** than query pushdown depending on the data size.
Contributor guide
Assessment
This issue has not been assessed yet.