citusdata / citusdata/citus

Duplicate aggregates in target list of worker queries

Open
#3,576 0 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

While reviewing #3537 it occurred to me we would send duplicate aggregates to the worker nodes which causes redundant columns to be send over the network. After some testing it turned out to not be only related to that PR, but something that is already in citus.

For example:
```sql
EXPLAIN VERBOSE
SELECT b,
count(*)
FROM t1
GROUP BY b
HAVING count(*) > 5
ORDER BY count(*) ASC;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=756.87..757.03 rows=67 width=12) │
│ Output: remote_scan.b, (COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint)) │
│ Sort Key: (COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint)) │
│ -> HashAggregate (cost=750.00..754.84 rows=67 width=12) │
│ Output: remote_scan.b, COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint) │
│ Group Key: remote_scan.b │
│ Filter: (COALESCE((pg_catalog.sum(remote_scan.worker_column_3))::bigint, '0'::bigint) > 5) │
│ -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=20) │
│ Output: remote_scan.b, remote_scan.count, remote_scan.worker_column_3 │
│ Task Count: 4 │
│ Tasks Shown: One of 4 │
│ -> Task │
│ Node: host=localhost port=9701 dbname=postgres │
│ -> HashAggregate (cost=6.32..6.47 rows=15 width=20) │
│ Output: b, count(*), count(*) │
│ Group Key: t1.b │
│ -> Seq Scan on public.t1_102008 t1 (cost=0.00..4.47 rows=247 width=4) │
│ Output: a, b │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(18 rows)

Time: 1.966 ms
```

Looking at the output columns the `Custom Scan (Citus Adaptive)` node returns we see `Output: remote_scan.b, remote_scan.count, remote_scan.worker_column_3`. Looking closely at the values that are calculated for `remote_scan.count` and `remote_scan.worker_column_3` we see they both contain `count(*)`.

Looking at the same query without the `HAVING` clause we see:
```sql
EXPLAIN VERBOSE
SELECT b,
count(*)
FROM t1
GROUP BY b
ORDER BY count(*) ASC;
┌───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=511.14..511.64 rows=200 width=12) │
│ Output: remote_scan.b, (COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint)) │
│ Sort Key: (COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint)) │
│ -> HashAggregate (cost=500.00..503.50 rows=200 width=12) │
│ Output: remote_scan.b, COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint) │
│ Group Key: remote_scan.b │
│ -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=12) │
│ Output: remote_scan.b, remote_scan.count │
│ Task Count: 4 │
│ Tasks Shown: One of 4 │
│ -> Task │
│ Node: host=localhost port=9701 dbname=postgres │
│ -> HashAggregate (cost=5.71..5.86 rows=15 width=12) │
│ Output: b, count(*) │
│ Group Key: t1.b │
│ -> Seq Scan on public.t1_102008 t1 (cost=0.00..4.47 rows=247 width=4) │
│ Output: a, b │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘
(17 rows)

Time: 2.401 ms
```

The output of our custom scan here is only 2 columns `Output: remote_scan.b, remote_scan.count`.

Turns out we do reuse our `remote_scan.count` for both the aggregate and the `GROUP BY`, but do not reuse it for the `HAVING` clause. Ideally we would always only send every aggregate only once to reduce the network transfer of duplicate columns.

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.