work mem

work_mem - PowerPoint PPT Presentation

work_mem work_mem


  1. work_mem

  2. ● ● ●

  3. ● ● ○ ● ●

  4. ● ○ ○ ○ ●

  5. ● ○ ○ ● ○ ●

  6. work_mem ● ● ●

  7. ● ● ○ ● ○ ● ○ ○ ○

  8. ● ● ● ● ●

  9. ● ● ● ● ●

  10. ● ● ● ○ ● ● ●

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

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

  13. ● ● ● ● ● ● ● ●

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

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

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

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

  18. ● ○ ●

  19. 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

  20. ● ○ ●

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

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

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

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

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

  26. bigger_t smaller_t bigger_t smaller_t 38

  27. 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

  28. 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

  29. 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

  30. 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

  31. bigger_t smaller_t bigger_t smaller_t 43

  32. bigger_t smaller_t bigger_t smaller_t 44

  33. PHJ_BUILD_HASHING_OUTER

  34. smaller_t batch 0 smaller_t bigger_t smaller_t batch 1 bigger_t smaller_t 46

  35. 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

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

  37. batch 0 smaller_t batch 1 1 batch 3 11 49

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

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

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

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

  42. PHJ_BUILD_HASHING_INNER smaller_t batch 0 bigger_t smaller_t bigger_t smaller_t smaller_t batch 1 54

  43. 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

  44. 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

  45. 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

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

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

  48. ● ○ ○ ○ 60

  49. ● ○ ○ ○ 61

  50. ● ○ ○ 62

  51. ● ○ work_mem ○ ○ 63

  52. 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

  53. 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

  54. 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

  55. 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

  56. 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

  57. bigger_t smaller_t batch 1 batch 1 bigger_t smaller_t stripe 0 stripe 1 69

  58. 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

  59. 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

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

  61. 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

  62. 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

  63. bigger_t outer match statuses batch 1 1001 bigger_t smaller_t 75

  64. SharedTupleStore MinimalTuple TupleIds MinimalTuple SharedBitStore - EXPLAIN 76

  65. 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

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

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

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

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

Recommend


More recommend