The Last Gedi

2022 COSCUP

Transcript

2. • SQL 為什麼可以優化 ? • SQL 為什麼需要優化？ • 優化 SQL 有多困難？ • Postgres 怎麼優化 SQL ? • 下個世代可能的優化技術是什麼？ 2
3. Postgres Query Optimizer 1. Tom Lane’s Code 2. Tom Lane’s Talk 3. Tom Lane’s Mails From Postgresql.org “ Involved in all aspects of PostgreSQL, including bug evaluation and fixes, performance improvements, and major new features, such as schemas. He is also responsible for the optimizer. ” 3 Source Oleg bartunov
4. Tom Lane at PGCon 2011 “ Hacking the Query Planner” 4
5. What is Join Searching/Ordering? 5
6. Source Querify labs 6
7. Source Querify labs 7
8. Source Querify labs 8
9. Source Querify labs 9
10. What is Query Optimization? 10
13. 8 × 2 = ? 13
14. 80 × 2 = ? 14
15. (2 × 2 × 2 × 2 × 5) × 2 = ? 15
16. (2 × 2 × 2 × 2) (5 × 2) 16
17. 800 × 27 = ? 17
18. 8 × 27 × 100 = 21600 18
19. 自然數的乘法滿足 • 交換律 (Commutative Law) 3 × 5 = 5 × 3 • 結合律 (Associative Law) (7 × 3) × 5 = 7 × (3 × 5) 19
20. Just reorder what you want !!! If there has some 5 and 2 , make them together. If there has some 0 , then the final answer must be 0. 20
21. HEURISTIC-BASED OPTIMIZATION • Predicate Pushdown • Projection Pushdown • Constant Folding • Function inlining • … 95% will work, just do it. Like always make 5 × 2 21
22. COST-BASED JOIN SEARCH 有時候事情不是 ”經驗法則(Heuristic)” 就能解決的。 需要更精確的計算成本與收益 (Cost Based) 22
23. 23 Source
26. Matrix Chain Multiplication Problem Ma × Mb × Mc × Md × Me Bottom up Dynamic Programming Level 2 (Ma × Mb ),(Mb × Mc ),(Mc × Md ),(Md × Me ) Level 3 (Ma × Mb × Mc ),(Mb × Mc × Md ),(Mc × Md × Me ) Level 4 (Ma × Mb × Mc × Md ),(Mb × Mc × Md × Me ) Level 5 (Ma × Mb × Mc × Md × Me ) 26
27. Matrix Chain Multiplication Problem Ma × Mb × Mc × Md × Me Level 4 的其中一個狀態 (Ma × Mb × Mc × Md ) 1. ((Ma × Mb × Mc ) × Md ) 2. (Ma × (Mb × Mc × Md )) 3. ((Ma × Mb ) × (Mc × Md ) ) 27
28. 矩陣的乘法滿足 • 結合律 (Associative Law) (Ma × Mb ) × Mc = Ma × (Mb × Mc ) 28
29. “ 先求不傷身體，再講求效果。 “ — 五州製藥 “ For a given query , find a correct execution plan that has the lowest cost . “ — CMU 15-721 29
30. Postgres /backend/optimizer/path/allp aths.c/ standard_join_search 30
31. Postgres /backend/optimizer/path/allp aths.c/ standard_join_search 31
32. Tom Lane at PGCon 2011 “ Hacking the Query Planner” 32
33. How Hard is it ? 33
34. How Hard is it ? NP-Hard 34
35. 遇到 NP-Hard 的問題該怎麼辦？ N (geqo_threshold) < 12 (大致上) 1. N 很小的的時候硬算(standard_join_search) N (geqo_threshold) >= 12 (大致上) 2. 只解部分子問題 3. 近似演算法(保證跟最佳解最多差多少) 4. 隨機演算法(Randomized Algorithm) 35
36. 遇到 NP-Hard 的問題該怎麼辦？ N (geqo_threshold) < 12 (大致上) 1. N 很小的的時候硬算 N (geqo_threshold) >= 12 (大致上) 2. 只解部分子問題 3. 近似演算法(保證跟最佳解最多差多少) 4. 隨機演算法(Randomized Algorithm) 36
38. Postgres 一開始是學術專案 38
39. Travelling salesman problem, TSP 旅行推銷員問題 39
40. Travelling salesman problem, TSP 旅行推銷員問題 40
41. Travelling salesman problem, TSP 旅行推銷員問題 41
42. Travelling salesman problem, TSP 旅行推銷員問題 42
43. Travelling salesman problem, TSP 旅行推銷員問題 43
44. ”Все счастливые семьи похожи друг надруга, каждая несчастливая семьянесчастлива по-своему.” – Анна Каренина 44 ”幸福的家庭都是相似的， 不幸的家庭各有各的不幸” Genetic algorithm 的精神
47. 初始化族群 評估族群 交配、突變 挑選優勢族群 停止 結束 47
48. 初始化族群 評估族群 交配、突變 挑選優勢族群 停止 結束 48
49. 初始化族群 評估族群 交配、突變 挑選優勢族群 停止 結束 49
50. Source Wiki 50
51. Linear Order Crossover Parent1 Parent2 51
52. Linear Order Crossover Parent1 Offspring 52
53. Parent1 Parent2 Offspring 53
54. 初始化族群 評估族群 交配、突變 挑選優勢族群 停止 結束 Parent1 1300 Parent2 1240 Offspring 400 54
55. 初始化族群 評估族群 交配、突變 挑選優勢族群 停止 結束 Parent2 Offspring 55
60. 60 Recommended Reading List • An Introduction to Join Ordering – Justin Jaffray (my favorite database writer 🤩) • Introduction to the Join Ordering Problem – Alexey Goncharuk • What is the Postgres' SQL GAME plan (I) ? – DouEnergy