Citus may use less parallelization than it can use (2)
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Looks similar to #3455.
On #3455 one, the structure was the following:
```
SELECT ..
(dist_table JOIN ref_table) as m
JOIN
dist_table
```
The subquery `m` is executed first. While executing the subquery, Citus associates all connections with access of `ref_table`.
Later, while the top level query is executed, whenever executor asks for which connection to use, Citus always gives the first connection that is associated with the reference table access. So, this means that the execution of the top level query is now bottlenecked with one connection per worker, even if it could use many more.
In this new issue, the structure is as follows:
```
SELECT ..
(ref_table_1 JOIN ref_table_2) as m
JOIN
dist_table
JOIN
ref_table_1
```
The subquery `m` is executed first. While executing the subquery, Citus associates all connections with access of `ref_table_1`.
Later, while the top level query is executed, whenever executor asks for which connection to use, Citus always gives the first connection that is associated with the reference table access. So, this means that the execution of the top level query is now bottlenecked with one connection per worker, even if it could use many more.
In fact, the explanation is almost the same with #3455. In the fix of it, we considered that a JOIN between distributed and reference table happens first, and hence have `hasSecondaryConnections` flag in the respective [check](https://github.com/citusdata/citus/pull/3456/files#diff-d876ba5a2a127211fd1758f470621e5cR596). In this one, the first query doesn't have any distributed tables, so `hasSecondaryConnections` is false.
Note that, one another question here is that why the subquery that consists of reference tables are recursively planned? One reason could be this bug (#4032), but in general that could happen in other scenarios as well. So, we should fix this issue irrespective of that.
Steps to repro, where we can see that session `152` sends too many queries over a single connection, which is the worker where the subquery is executed in the first place:
```
CREATE TABLE ref_table_1 (a int);
SELECT create_reference_table('ref_table_1');
CREATE TABLE ref_table_2 (a int);
SELECT create_reference_table('ref_table_2');
CREATE TABLE dist (a int, b text);
SELECT create_distributed_table('dist', 'a');
INSERT INTO ref_table_1 SELECT i FROM generate_series(0,1000)i;
INSERT INTO ref_table_2 SELECT * FROM ref_table_1;
INSERT INTO dist SELECT a, a::text FROM ref_table_1;
SELECT count(*), pg_sleep(0.1) FROM
(SELECT ref_table_1.a as a FROM ref_table_1 JOIN ref_table_2 ON (ref_table_1.a = ref_table_2.a) OFFSET 0) as foo
JOIN
dist
ON(dist.a = foo.a)
JOIN
ref_table_1
ON (true);
...
DEBUG: generating subplan 24_1 for subquery SELECT ref_table_1.a FROM (public.ref_table_1 JOIN public.ref_table_2 ON ((ref_table_1.a OPERATOR(pg_catalog.=) ref_table_2.a))) OFFSET 0
...
DEBUG: established connection to localhost:9702 for session 151
DEBUG: Total number of commands sent over the session 151: 1
....
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
DEBUG: Session 152 (localhost:9702) has an assigned task
....
DEBUG: Total number of commands sent over the session 152: 10
DEBUG: Total number of commands sent over the session 153: 1
DEBUG: Total number of commands sent over the session 154: 1
DEBUG: Total number of commands sent over the session 155: 1
DEBUG: Total number of commands sent over the session 156: 1
DEBUG: Total number of commands sent over the session 157: 1
DEBUG: Total number of commands sent over the session 158: 1
DEBUG: Total number of commands sent over the session 159: 1
DEBUG: Total number of commands sent over the session 160: 1
DEBUG: Total number of commands sent over the session 161: 1
DEBUG: Total number of commands sent over the session 162: 1
DEBUG: Total number of commands sent over the session 163: 1
DEBUG: Total number of commands sent over the session 164: 1
DEBUG: Total number of commands sent over the session 165: 1
DEBUG: Total number of commands sent over the session 166: 1
DEBUG: Total number of commands sent over the session 167: 1
DEBUG: Total number of commands sent over the session 168: 1
DEBUG: Total number of commands sent over the session 169: 1
DEBUG: Total number of commands sent over the session 170: 1
DEBUG: Total number of commands sent over the session 171: 1
DEBUG: Total number of commands sent over the session 172: 1
DEBUG: Total number of commands sent over the session 173: 1
DEBUG: Total number of commands sent over the session 174: 1
...
count | pg_sleep
---------+----------
1002001 |
(1 row)
```
Contributor guide
Assessment
This issue has not been assessed yet.