citusdata / citusdata/citus

INSERT .. SELECT planner might fail to pushdown when the query references an intermediate_result

Open
#6,544 2 comments 0 reactions 0 assignees View on GitHub
performance
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

Since an inner join between a distributed table and an intermediate result is co-located with the same distributed table, theoretically we could push down the following INSERT command:

```sql
CREATE TABLE dist_1 (a int, b int);
SELECT create_distributed_table('dist_1', 'a');

INSERT INTO dist_1
SELECT t1.*
FROM dist_1 t1
JOIN
(SELECT * FROM dist_1 OFFSET 0) t4
ON (t1.a = t4.a);
```

```sql
EXPLAIN INSERT INTO dist_1
SELECT t1.*
FROM dist_1 t1
JOIN
(SELECT * FROM dist_1 OFFSET 0) t4 -- becomes an intermediate result
ON (t1.a = t4.a);
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Custom Scan (Citus INSERT ... SELECT) (cost=0.00..0.00 rows=0 width=0) │
│ INSERT/SELECT method: repartition │
│ -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8) │
│ -> Distributed Subplan 19_1 │
│ -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=8) │
│ Task Count: 3 │
│ Tasks Shown: One of 3 │
│ -> Task │
│ Node: host=localhost port=10700 dbname=postgres │
│ -> Seq Scan on dist_1_102008 dist_1 (cost=0.00..32.60 rows=2260 width=8) │
│ Task Count: 3 │
│ Tasks Shown: One of 3 │
│ -> Task │
│ Node: host=localhost port=10700 dbname=postgres │
│ -> Merge Join (cost=218.34..392.84 rows=11300 width=8) │
│ Merge Cond: (intermediate_result.a = t1.a) │
│ -> Sort (cost=59.83..62.33 rows=1000 width=4) │
│ Sort Key: intermediate_result.a │
│ -> Function Scan on read_intermediate_result intermediate_result (cost=0.00..10.00 rows=1000 width=4) │
│ -> Sort (cost=158.51..164.16 rows=2260 width=8) │
│ Sort Key: t1.a │
│ -> Seq Scan on dist_1_102008 t1 (cost=0.00..32.60 rows=2260 width=8) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(22 rows)
```

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.