SQL最佳化 —— 讀懂執行計劃

Bucket and Batch

I saw that functions which use hash join, like as ExecHashGetBucketAndBatch and ExecScanHashBucket, have bucket and batch concepts.
So, I would like to know the differences between bucket and batch. I dont understand tables partitioning mechanism in hash join.

A bucket is, well, one bucket of a hash table --- it holds all the tuples that have the same hash code (for as many bits of the hash code as we are choosing to use).  We try to size the hash table with enough buckets so there's not more than 10 tuples per bucket on average.

A batch is a range of buckets that we process at the same time.  Tuples (from either side of the join) whose hash codes show they fall into batches other than the first one get dumped into temporary holding files, and then (after finishing joining the first batch) we pull each successive batch back into memory and join that portion of the tuples.
The batch size is chosen to make the amount of memory needed be approximately work_mem.

IOW, there are really nbuckets * nbatches "virtual" buckets in the hash table, but only nbuckets worth of them are kept in memory at any one time.

Note that we don't currently do batches for hash aggregates, only joins
currently.