pingcap/tidb

Support spilling for distinct aggregate function

Open

#27,092 opened on Aug 11, 2021

View on GitHub
 (4 comments) (0 reactions) (1 assignee)Go (40,090 stars) (6,186 forks)batch import
help wantedsig/executiontype/enhancement

Description

Enhancement

Although we support spilling for unparallel HashAgg now, the memory usage of distinct is also out of control. The reason is that we need a IntSet/FloatSet/StringSet to check whether a value is distinct, and the Set maybe take up huge memory.

We need some spilling strategy for distinct function, to control the memory usage of SQL included distinct function.

Fortunately, only few aggregate function need to support distinct, so we only need consider the following distinct function: Count, Sum, Avg, GroupConcat

TiDB seems also support distinct for STDDEV_POP,STDDEV_SAMP, etc. But Mysql doesn't support the syntax

Contributor guide

Tech stack
gosql
Domain
backenddatabase
Issue type
feature
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
5
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
over 1 week
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
stale
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
mostly clear
Prerequisites
Understanding of SQL aggregate functionsKnowledge of spilling techniquesFamiliarity with TiDB executor
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
20
Research direction
Investigate existing spilling implementation for HashAgg in TiDB (likely in executor package). Determine how distinct aggregate functions (Count, Sum, Avg, GroupConcat) can spill their sets (IntSet, FloatSet, StringSet) to disk. Consider using a similar approach as HashAgg spilling, possibly leveraging temporary files or disk based hash tables. Review any linked comments or maintainer discussions for specific design constraints.