citusdata / citusdata/citus

Pushdown top level UNION/UNION ALL queries

Open
#1,913 0 comments 1 reaction 0 assignees View on GitHub
sql
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.