work_mem - PowerPoint PPT Presentation

work_mem

● ● ●

● ● ○ ● ●

● ○ ○ ○ ●

● ○ ○ ● ○ ●

work_mem ● ● ●

● ● ○ ● ○ ● ○ ○ ○

● ● ● ● ●

● ● ● ● ●

● ● ● ○ ● ● ●

Group 0 Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7

Group 0 Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7

● ● ● ● ● ● ● ●

Group 0 Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7 Partitions

Group 0 Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7

Group 0 Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7

Group 0 Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7

● ○ ●

Group 0 Group 0 free Group 1 Group 1 Group 2 Group 2 free Group 3 Group 3 free Group 4 Group 4 Group 5 Group 5 Group 6 Group 6 Group 7 Group 7

● ○ ●

By providing a knob for users to set memory consumption limits and then respecting those limits, resource consumption will be stable. Queries that might be faster were memory to be unlimited might be slower, but the memory consumption will be predictable. Users can decide to change their workload based on this in order to get the performance they are looking for [ ]

SELECT * FROM bigger_t JOIN smaller_t USING (i); bigger_t smaller_t bigger_t smaller_t 34

bucketno = hash(i) & (*nbuckets - 1) hash(i) -> 5 5 & (4 - 1) -> 1 i INT bigger_t smaller_t bigger_t smaller_t 35

bucketno = hash(i) & (nbuckets - 1) hash(i) -> 10 10 & (4 - 1) -> 2 i INT bigger_t smaller_t bigger_t smaller_t 36

bucketno = hash(i) & (nbuckets - 1) hash(i) -> 13 13 & (4 - 1) -> 1 i INT bigger_t smaller_t bigger_t smaller_t 37

bigger_t smaller_t bigger_t smaller_t 38

hash(i) = 00000010 batchno = (hash(i) >> (nbuckets - 1)) & (*nbatches - 1) smaller_t batch 0 1 2 3 hash(i) -> 2 i INT (2 >> 3) & 2 -> 0 bigger_t smaller_t bigger_t smaller_t 2 & 3 -> 2 39

batchno = (hash(i) >> (nbuckets - 1)) & (*nbatches - 1) smaller_t batch 0 2 3 1 hash(i) -> 10 i INT (10 >> 3) & 3 -> 1 bigger_t smaller_t bigger_t smaller_t 10 & 3 -> 2 40

batchno = (hash(i) >> (nbuckets - 1)) & (nbatches - 1) smaller_t batch 1 2 3 2 3 1 1 hash(i) -> 10 i INT bigger_t (10 >> 3) & 3 -> 1 bigger_t smaller_t 10 & 3 -> 2 41

batchno = (hash(i) >> (nbuckets - 1)) & (nbatches - 1) smaller_t batch 3 hash(i) -> 56 i INT (56 >> 3) & 3 -> 3 bigger_t smaller_t 56 & 3 -> 0 42

bigger_t smaller_t bigger_t smaller_t 43

bigger_t smaller_t bigger_t smaller_t 44

PHJ_BUILD_HASHING_OUTER

smaller_t batch 0 smaller_t bigger_t smaller_t batch 1 bigger_t smaller_t 46

smaller_t smaller_t 2 3 2 3 batch 1 bigger_t batch 1 smaller_t batch 1 bigger_t smaller_t smaller_t batch 5 47

batchno = (hash(i) >> (nbuckets - 1)) & (nbatches - 1) batch 0 smaller_t batch 1 1 48

batch 0 smaller_t batch 1 1 batch 3 11 49

batch 0 smaller_t batch 1 01 batch 3 batch 7 11 111 50

batch 0 smaller_t batch 1 01 batch 3 batch 7 11 111 51

batch 0 smaller_t batch 1 01 batch 3 batch 7 11 111 52

batch 0 smaller_t batch 1 batch 11 1011 01 batch 3 batch 7 11 111 53

PHJ_BUILD_HASHING_INNER smaller_t batch 0 bigger_t smaller_t bigger_t smaller_t smaller_t batch 1 54

PHJ_BUILD_HASHING_INNER smaller_t batch 0 smaller_t bigger_t smaller_t bigger_t batch 0 smaller_t batch 1 smaller_t batch 1 smaller_t batch 2 smaller_t 55 batch 3

PHJ_BUILD_HASHING_INNER PHJ_BUILD_HASHING_OUTER bigger_t smaller_t batch 0 batch 0 bigger_t smaller_t bigger_t smaller_t batch 1 batch 1 bigger_t smaller_t batch 2 batch 2 bigger_t smaller_t batch 3 56 batch 3

PHJ_BUILD_HASHING_INNER smaller_t batch 0 smaller_t bigger_t smaller_t bigger_t batch 0 smaller_t batch 1 smaller_t batch 1 smaller_t batch 2 smaller_t 57 batch 3

batch 3 batch 3 0011 ● batch 11 011 batch 3 1011 ● work_mem batch 7 11 111 58

● batch 3 batch 3 00011 batch 3 0011 ● batch batch 011 11 19 batch 3 10011 1011 ● BufFile 11 batch 7 111 59

● ○ ○ ○ 60

● ○ ○ ○ 61

● ○ ○ 62

● ○ work_mem ○ ○ 63

batch batch 3 3 0011 00011 batch 3 011 batch batch ● 11 19 batch 3 10011 1011 11 batch 7 111 ● ● batch 3 0011 batch 3 011 batch 11 batch 3 1011 11 batch 7 111 64

smaller_t 2 3 2 3 bigger_t batch 1 smaller_t batch 1 bigger_t batch 5 bigger_t smaller_t bigger_t smaller_t stripe 0 stripe 1 65

smaller_t smaller_t batch 1 2 3 2 3 stripe 0 bigger_t batch 1 bigger_t batch 5 bigger_t smaller_t bigger_t smaller_t stripe 0 stripe 0 stripe 1 66

smaller_t smaller_t batch 1 2 3 2 3 stripe 1 bigger_t batch 1 bigger_t smaller_t bigger_t smaller_t stripe 0 stripe 1 67

PHJ_BUILD_HASHING_INNER smaller_t smaller_t batch 0 bigger_t smaller_t bigger_t batch 0 smaller_t batch 1 smaller_t batch 1 smaller_t batch 2 smaller_t 68 batch 3

bigger_t smaller_t batch 1 batch 1 bigger_t smaller_t stripe 0 stripe 1 69

smaller_t batch 1 stripe 0 bigger_t batch 1 bigger_t smaller_t batch 0 batch 0 bigger_t smaller_t stripe 0 stripe 0 stripe 1 bigger_t smaller_t batch 2 batch 2 bigger_t smaller_t batch 3 batch 3 70

smaller_t batch 1 stripe 1 bigger_t batch 1 bigger_t smaller_t batch 0 batch 0 bigger_t smaller_t stripe 0 stripe 1 bigger_t smaller_t batch 2 batch 2 bigger_t smaller_t batch 3 batch 3 71

smaller_t ● batch 1 stripe 0 bigger_t batch 1 ● bigger_t smaller_t smaller_t batch 1 stripe 0 stripe 1 72

smaller_t batch 1 stripe 0 bigger_t outer match statuses batch 1 0001 bigger_t smaller_t bigger_t.batch_1.tuple_1 matches a tuple in smaller_t batch 1 smaller_t.batch_1.stripe_0 stripe 0 stripe 1 bigger_t tuples 2 - 4 have no matches in smaller_t.batch_1.stripe_0 73

smaller_t batch 1 stripe 1 bigger_t outer match statuses batch 1 1001 bigger_t smaller_t bigger_t.batch_1.tuple_4 matches a tuple in smaller_t batch 1 smaller_t.batch_1.stripe_1 stripe 0 stripe 1 74

bigger_t outer match statuses batch 1 1001 bigger_t smaller_t 75

SharedTupleStore MinimalTuple TupleIds MinimalTuple SharedBitStore - EXPLAIN 76

ParallelHashJoinState ParallelHashJoinBatch 0 hashtable ParallelHashJoinBatch 1 Inner SharedTuplestore batch 1 P1 Inner STS P2 Inner STS accessor accessor STS Participant 1 STS Participant 2 write_file write_file STS Participant n Outer SharedTuplestore batch 1 STS Participant 1 STS Participant 2 STS Participant n 77

SharedTupleStore STS Chunk P1 Write file STS Chunk Inner STS P2 Write file 78

SharedTupleStore STS Chunk Stripe 1 P1 Write file Stripe STS Chunk 2 Inner STS Stripe P2 1 Write file 79 Stripe 2

● ○ ○ ● ● *https://www.postgresql.org/message-id/flat/CA%2BhUKGKWWmf%3DWELLG%3DaUGbcugRaSQbtm0tKYiBut-B2rVKX63g%40mail.gmail.com **https://github.com/melanieplageman/postgres/commits/alternative_AHJ 83

● ○ ■ ■ ● ○ work_mem** ■ ■ *Tomas Vondra’s patch https://www.postgresql.org/message-id/flat/20190504003414.bulcbnge3rhwhcsh%40development ** mentioned in the same thread 84