citusdata / citusdata/citus

Router (single shard) INSERT INTO SELECT with CTEs goes via coordinator

Open
#2,416 1 comment 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

**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

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.