Window functions partitioned on distribution column with LIMIT does not push down limit
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
During some experimentation with window functions I came up with a query that took longer than I would have expected. Given the following table:
```sql
CREATE TABLE github_commits (
event_id bigint,
repo_id bigint,
repo_name text,
pusher_login text,
branch text,
created_at timestamp with time zone,
author_name text,
sha text,
message text,
comment text
);
SELECT create_distributed_table('github_commits', 'repo_id');
```
And the following query:
```sql
EXPLAIN
SELECT repo_id,
repo_name,
pusher_login,
count(*) user_commits,
sum(count(*)) OVER (PARTITION BY repo_id, repo_name) AS repo_commits
FROM github_commits
GROUP BY repo_id, repo_name, pusher_login
ORDER BY repo_commits DESC, user_commits DESC
LIMIT 10;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=1007.82..1007.85 rows=10 width=112) │
│ -> Sort (cost=1007.82..1008.32 rows=200 width=112) │
│ Sort Key: remote_scan.repo_commits DESC, (COALESCE((pg_catalog.sum(remote_scan.user_commits))::bigint, '0'::bigint)) DESC │
│ -> HashAggregate (cost=1000.00..1003.50 rows=200 width=112) │
│ Group Key: remote_scan.repo_id, remote_scan.repo_name, remote_scan.pusher_login │
│ -> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=112) │
│ Task Count: 32 │
│ Tasks Shown: One of 32 │
│ -> Task │
│ Node: host=localhost port=9701 dbname=postgres │
│ -> WindowAgg (cost=3685.77..3851.33 rows=8278 width=84) │
│ -> Sort (cost=3685.77..3706.47 rows=8278 width=52) │
│ Sort Key: github_commits.repo_id, github_commits.repo_name │
│ -> HashAggregate (cost=3064.30..3147.08 rows=8278 width=52) │
│ Group Key: github_commits.repo_id, github_commits.repo_name, github_commits.pusher_login │
│ -> Seq Scan on github_commits_102616 github_commits (cost=0.00..2350.80 rows=57080 width=44) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
You will notice the `LIMIT` clause is not pushed down. When running this on a big installation with 3 months of github data, on 160 shards (github-realtime-demo was used to produce this), it caused 7.5M rows to be fetched from all shards combined. With a `LIMIT 10` pushed down to the workers this would have been reduced to 1600 rows (10 per shard).
From my understanding of Window Functions that are partitioned on the distribution column it would be safe to push down the limit in the same way we would do for `GROUP BY` on the distribution columns. When the distribution column is in the list of partition columns the tuple calculated on the workers is the complete result for the partition, anything above the limit the user provided will not have any significance on the result of the query.
Contributor guide
Assessment
This issue has not been assessed yet.