Router (single shard) INSERT INTO SELECT with CTEs goes via coordinator
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
**Schema:**
```
create table test_table(id int,tenant_id int);
select create_distributed_table('test_table','tenant_id');
create table test_dest(tenant_id int, s int);
select create_distributed_table('test_dest','tenant_id');
```
**Explain Plans (goes via coordinator):**
Note it is not doing recursive planning.
```
EXPLAIN WITH src AS (SELECT tenant_id, count(*) from test_table where tenant_id=1 group by tenant_id)
INSERT INTO test_dest SELECT * from src;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Custom Scan (Citus INSERT ... SELECT via coordinator) (cost=0.00..0.00 rows=0 width=0)
-> Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0)
Task Count: 1
Tasks Shown: All
-> Task
Node: host=ec2-35-168-239-7.compute-1.amazonaws.com port=5432 dbname=citus
-> CTE Scan on src (cost=38.42..38.64 rows=11 width=12)
CTE src
-> GroupAggregate (cost=0.00..38.42 rows=11 width=12)
Group Key: test_table.tenant_id
-> Seq Scan on test_table_102075 test_table (cost=0.00..38.25 rows=11 width=4)
Filter: (tenant_id = 1)
(12 rows)
citus=> EXPLAIN WITH src AS (SELECT tenant_id, count(*) from test_table where tenant_id=1 group by tenant_id)
INSERT INTO test_dest SELECT * from src where tenant_id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Custom Scan (Citus INSERT ... SELECT via coordinator) (cost=0.00..0.00 rows=0 width=0)
-> Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0)
Task Count: 1
Tasks Shown: All
-> Task
Node: host=ec2-35-168-239-7.compute-1.amazonaws.com port=5432 dbname=citus
-> CTE Scan on src (cost=38.42..38.66 rows=1 width=12)
Filter: (tenant_id = 1)
CTE src
-> GroupAggregate (cost=0.00..38.42 rows=11 width=12)
Group Key: test_table.tenant_id
-> Seq Scan on test_table_102075 test_table (cost=0.00..38.25 rows=11 width=4)
Filter: (tenant_id = 1)
```
There is no need to go via coordinator. The query can be directly pushed down.
Contributor guide
Assessment
This issue has not been assessed yet.