work_mem - - PowerPoint PPT Presentation

▶
work mem
SMART_READER_LITE
LIVE PREVIEW

work_mem - - PowerPoint PPT Presentation

Jun 12, 2023 351 likes •1.2k views

work_mem work_mem

slide-1
SLIDE 1

work_mem

slide-2
SLIDE 2
slide-3
SLIDE 3
  • ○
slide-4
SLIDE 4
  • ○

○ ○

slide-5
SLIDE 5
  • ○

○

  • ○
slide-6
SLIDE 6

work_mem

slide-7
SLIDE 7
  • ○
  • ○
  • ○

○ ○

slide-8
SLIDE 8
slide-9
SLIDE 9
slide-10
SLIDE 10
slide-11
SLIDE 11
slide-12
SLIDE 12
slide-13
SLIDE 13
  • ○
slide-14
SLIDE 14

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

slide-15
SLIDE 15

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

slide-16
SLIDE 16
slide-17
SLIDE 17

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

slide-18
SLIDE 18

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

slide-19
SLIDE 19

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

slide-20
SLIDE 20

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

slide-21
SLIDE 21
  • ○
slide-22
SLIDE 22

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

slide-23
SLIDE 23
  • ○
slide-24
SLIDE 24
slide-25
SLIDE 25

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 [ ]

slide-26
SLIDE 26
slide-27
SLIDE 27
slide-28
SLIDE 28
slide-29
SLIDE 29
slide-30
SLIDE 30
slide-31
SLIDE 31
slide-32
SLIDE 32
slide-33
SLIDE 33
slide-34
SLIDE 34

bigger_t smaller_t

bigger_t smaller_t

SELECT * FROM bigger_t JOIN smaller_t USING (i);

34

slide-35
SLIDE 35

bigger_t smaller_t

bigger_t smaller_t

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

35

slide-36
SLIDE 36

bigger_t smaller_t

bigger_t smaller_t

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

36

slide-37
SLIDE 37

bigger_t smaller_t

bigger_t smaller_t

i INT hash(i) -> 13 13 & (4 - 1) -> 1

37

bucketno = hash(i) & (nbuckets - 1)

slide-38
SLIDE 38

bigger_t smaller_t

bigger_t smaller_t

38

slide-39
SLIDE 39

bigger_t smaller_t

bigger_t smaller_t

batchno = (hash(i) >> (nbuckets - 1)) & (*nbatches - 1) i INT

hash(i) -> 2 (2 >> 3) & 2 -> 0 2 & 3 -> 2

39

hash(i) = 00000010

1 2 3

smaller_t

batch 0

slide-40
SLIDE 40

bigger_t smaller_t

bigger_t smaller_t

batchno = (hash(i) >> (nbuckets - 1)) & (*nbatches - 1) i INT

hash(i) -> 10 (10 >> 3) & 3 -> 1 10 & 3 -> 2

40 smaller_t

batch 0

2 3 1

slide-41
SLIDE 41

bigger_t smaller_t

batchno = (hash(i) >> (nbuckets - 1)) & (nbatches - 1) i INT

41

hash(i) -> 10 (10 >> 3) & 3 -> 1 10 & 3 -> 2

smaller_t

batch 1

bigger_t

2 3 1 2 3 1

slide-42
SLIDE 42

bigger_t smaller_t

batchno = (hash(i) >> (nbuckets - 1)) & (nbatches - 1) i INT

42

hash(i) -> 56 (56 >> 3) & 3 -> 3 56 & 3 -> 0

smaller_t

batch 3

slide-43
SLIDE 43

bigger_t smaller_t

bigger_t smaller_t

43

slide-44
SLIDE 44

bigger_t smaller_t

bigger_t smaller_t

44

slide-45
SLIDE 45

PHJ_BUILD_HASHING_OUTER

slide-46
SLIDE 46

bigger_t

bigger_t smaller_t

smaller_t

batch 1 smaller_t batch 0

smaller_t

46

slide-47
SLIDE 47

bigger_t smaller_t

smaller_t

batch 1

smaller_t smaller_t

batch 5 smaller_t batch 1 2 3 2 3

bigger_t

batch 1

47

slide-48
SLIDE 48

batch 0

smaller_t

batch 1

1

48

batchno = (hash(i) >> (nbuckets - 1)) & (nbatches - 1)

slide-49
SLIDE 49

batch 0

smaller_t

batch 1 batch 3

1 11

49

slide-50
SLIDE 50

batch 0

smaller_t

batch 1 batch 3

01 11

batch 7

111

50

slide-51
SLIDE 51

batch 3

01 11

51

batch 7

111

batch 1 batch 0

smaller_t

slide-52
SLIDE 52

batch 3

01 11

batch 7

111

52 batch 1 batch 0

smaller_t

slide-53
SLIDE 53

batch 3

11

batch 7

111

53

1011

batch 11

01

batch 1 batch 0

smaller_t

slide-54
SLIDE 54

bigger_t smaller_t

bigger_t smaller_t

smaller_t

batch 0

smaller_t

batch 1

PHJ_BUILD_HASHING_INNER

54

slide-55
SLIDE 55

bigger_t

bigger_t smaller_t

smaller_t

batch 0

smaller_t

batch 1

smaller_t

batch 0

smaller_t

batch 1

smaller_t

batch 2

smaller_t

batch 3

PHJ_BUILD_HASHING_INNER

55

slide-56
SLIDE 56

bigger_t smaller_t

smaller_t

batch 0

smaller_t

batch 1

smaller_t

batch 2

smaller_t

batch 3

PHJ_BUILD_HASHING_INNER PHJ_BUILD_HASHING_OUTER

bigger_t

batch 0

bigger_t

batch 1

bigger_t

batch 2

bigger_t

batch 3

56

slide-57
SLIDE 57

bigger_t

bigger_t smaller_t

smaller_t

batch 0

smaller_t

batch 1

smaller_t

batch 0

smaller_t

batch 1

smaller_t

batch 2

smaller_t

batch 3

PHJ_BUILD_HASHING_INNER

57

slide-58
SLIDE 58
  • work_mem

batch 3

11

batch 7

111 1011

batch 3 batch 3

batch 11

011 0011

58

slide-59
SLIDE 59
  • BufFile

batch 3

11

batch 7

111 1011

batch 11

011 0011

batch 3 batch 3

00011 10011

batch 19

batch 3

59

slide-60
SLIDE 60
  • ○

○ ○

60

slide-61
SLIDE 61
  • ○

○ ○

61

slide-62
SLIDE 62
  • ○

○

62

slide-63
SLIDE 63
  • ○

work_mem ○ ○

63

slide-64
SLIDE 64

batch 3

11

batch 7

111 1011

batch 11

011 0011

batch 3

00011 10011

batch 19

batch 3

batch 3

11

batch 7

111 1011

batch 11

011 0011

batch 3 batch 3

  • 64

batch 3

slide-65
SLIDE 65

bigger_t

batch 5

bigger_t smaller_t

bigger_t smaller_t

stripe 0 stripe 1

smaller_t

2 3 2 3

bigger_t

batch 1

smaller_t

batch 1

65

slide-66
SLIDE 66

bigger_t smaller_t

bigger_t smaller_t

smaller_t

2 3 2 3

bigger_t

batch 1

smaller_t

batch 1 stripe 0 stripe 0 stripe 1 stripe 0

66 bigger_t

batch 5

slide-67
SLIDE 67

bigger_t smaller_t

bigger_t smaller_t

smaller_t

2 3 2 3

bigger_t

batch 1

smaller_t

batch 1 stripe 1 stripe 0 stripe 1

67

slide-68
SLIDE 68

bigger_t smaller_t

PHJ_BUILD_HASHING_INNER bigger_t

smaller_t

batch 1

smaller_t

batch 0

smaller_t

batch 1

smaller_t

batch 2

smaller_t

batch 3

smaller_t

batch 0

68

slide-69
SLIDE 69

bigger_t smaller_t

smaller_t

batch 1

bigger_t

batch 1

stripe 0 stripe 1

69

slide-70
SLIDE 70

bigger_t smaller_t

bigger_t

batch 1

smaller_t

batch 1 stripe 0 stripe 0 stripe 1 stripe 0

smaller_t

batch 2

smaller_t

batch 3

bigger_t

batch 0

bigger_t

batch 2

bigger_t

batch 3

smaller_t

batch 0

70

slide-71
SLIDE 71

bigger_t smaller_t

bigger_t

batch 1

smaller_t

batch 1 stripe 1

smaller_t

batch 2

smaller_t

batch 3

bigger_t

batch 0

bigger_t

batch 2

bigger_t

batch 3

smaller_t

batch 0 stripe 0 stripe 1

71

slide-72
SLIDE 72
  • bigger_t

smaller_t

bigger_t

batch 1

smaller_t

batch 1 stripe 0

72

stripe 1 smaller_t batch 1 stripe 0

slide-73
SLIDE 73

bigger_t smaller_t

bigger_t

batch 1

smaller_t

batch 1 stripe 0

  • uter match statuses

0001

bigger_t.batch_1.tuple_1 matches a tuple in smaller_t.batch_1.stripe_0 bigger_t tuples 2 - 4 have no matches in smaller_t.batch_1.stripe_0

73

stripe 1 smaller_t batch 1 stripe 0

slide-74
SLIDE 74

bigger_t smaller_t

bigger_t

batch 1

smaller_t

batch 1 stripe 1

  • uter match statuses

1001

bigger_t.batch_1.tuple_4 matches a tuple in smaller_t.batch_1.stripe_1

74

stripe 1 smaller_t batch 1 stripe 0

slide-75
SLIDE 75

bigger_t smaller_t

bigger_t

batch 1

  • uter match statuses

1001 75

slide-76
SLIDE 76

SharedTupleStore MinimalTuple TupleIds MinimalTuple

  • SharedBitStore

EXPLAIN

76

slide-77
SLIDE 77

write_file

ParallelHashJoinState

ParallelHashJoinBatch 0 ParallelHashJoinBatch 1

Outer SharedTuplestore batch 1 Inner SharedTuplestore batch 1

STS Participant 1 STS Participant n STS Participant 2 STS Participant 1 STS Participant n STS Participant 2

P1 Inner STS accessor P2 Inner STS accessor write_file

hashtable

77

slide-78
SLIDE 78

SharedTupleStore

Inner STS

P2 Write file P1 Write file STS Chunk STS Chunk

78

slide-79
SLIDE 79

SharedTupleStore

P2 Write file P1 Write file STS Chunk STS Chunk

Stripe 1 Stripe 1 Stripe 2

Stripe 2

79

Inner STS

slide-80
SLIDE 80
slide-81
SLIDE 81
slide-82
SLIDE 82
slide-83
SLIDE 83
  • ○

○

  • 83

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

slide-84
SLIDE 84
  • ○

■ ■

  • ○

work_mem** ■ ■

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