SQLチューニング_理論と改善の実例_/pgcon19j_t4

# SQLチューニング 理論と改善の実例

- 2019-11-15 開催のPostgreSQL Conference Japan 2019 の発表スライドです

Transcript

1. SQLνϡʔχϯά =ཧ࿦ͱվળͷ࣮ྫ= 2019-11-15 PostgreSQL Conference Japan 2019 ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํࢧ෦௕ ߴڮɹҰٍ 1
2. ஫ҙࣄ߲ • εϥΠυ͸ެ։͍ͯ͠·͢ɻ • ࣭໰ͱ͔͝ࢦఠͱ͔ॾʑͷϑΟʔυόοΫ͕͋Ε͹ #pgcon19j ΁͓ئ͍க͠·͢ɻ • αʔόʔͷߏ੒΍ύϥϝʔλνϡʔχϯάͷ࿩Ͱ͸ແͯ͘ DDLʹΑͬͯνϡʔχϯάΛ͢ΔSQLͷԠ౴଎౓Λվળ͢Δ ͓࿩Ͱ͢ɻ 2
3. ͓͠ͳ͕͖ 1. ࣗݾ঺հ 2. SQL͕࣮ߦ͞ΕΔ࢓૊Έ 3. ࣮ߦܭըͷݟํ 4. SQLνϡʔχϯάͷ଎౓վળͷ࣮ྫ 5. ·ͱΊ 3
4. 1. ࣗݾ঺հ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΞϓϦέʔγϣϯΤϯδχΞ • ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕ 4
5. גࣜձࣾΦϛΧϨ • શࠃͷࠗ׆ύʔςΟʔ ໿30,000݅Λܝࡌͯ͠Δ ϙʔλϧαΠτ. • ग़ձ͍͕0ΛZeroʹ͢Δ ΛVisionʹ೔ʑ׆ಈͯ͠·͢ 5
6. 2. SQL͕࣮ߦ͞ΕΔ࢓૊Έ 6
7. SQL͕࣮ߦ͞ΕΔ࢓૊Έ 7
8. SQLͷ࣮ߦͰ཈͓͑ͯ͘΂͖ϙΠϯτ • ϓϥϯφ͕ ࣮ߦܭը Λ࡞Γ ΤΫθΩϡʔλ͕ͦͷ࣮ߦܭըʹԊͬͯ σʔλϕʔε͔ΒσʔλΛ໰͍߹Θͤͯ݁ՌΛฦ͢ => Ͱ͸ɺͦͷ࣮ߦܭը͸ͲͷΑ͏ʹܾ·Δͷ͔ɻ 8
9. ࣮ߦܭը͸ͲͷΑ͏ʹܾ·Δͷ͔ • લఏͱͯ͠ ϓϥϯφ͸ σʔλϕʔεʹͲΜͳ஋͕ೖͬͯΔ͔ Λ஌Βͳ͍ɻ • ϓϥϯφ͸ σʔλϕʔε͕؅ཧ͍ͯ͠Δ ౷ܭ৘ใ Λݟͯ ʮ͜ͷςʔϒϧʹ͸͜Μͳσʔλ͕ೖͬͯΔ܏޲ʹ͋Δ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷଎ͩΖ͏ʯ ͱ࣮ߦܭըΛܾΊΔɻ 9
10. SQLνϡʔχϯάͷ֩ • ࣮ߦܭըΛʰݟͯʱ஗ͦ͏ͳ΋ͷ͸ແ͍͔ʁ => ࣮ߦܭըΛݟ͍ͨSQLͷઌ಄ʹ͋ΔΩʔϫʔυΛ͚ͭΔࣄͰ ɹɹ࣮ߦܭըΛਓʹ෼͔ΔܗࣜͰݟΔࣄ͕ग़དྷΔɻ 10
11. 2. SQL͕࣮ߦ͞ΕΔ࢓૊Έ • SQLͷ࣮ߦʹ͓͍ͯ ύʔαʔɾϦϥΠλɾϓϥϯφɾΤΫθΩϡʔλ ͱ͍͏໾ׂ͕͋Δɻ • ϓϥϯφ͸౷ܭ৘ใΛݩʹ࣮ߦܭըΛ࡞Γ ΤΫθΩϡʔλ͕࣮ߦܭը௨ΓʹSQLΛ࣮ߦ͢Δ • ࣮ߦܭըΛಡΜͰ஗͘ͳΓͦ͏ͳՕॴΛݟ͍ͯ͘ࣄ͕ SQLνϡʔχϯάͷ֩ͱͳΔɻ 11
12. 3. ࣮ߦܭըͷݟํ 12
13. • ࣮ߦܭըΛಡΜͰ ஗͘ͳΓͦ͏ͳ෦෼Λݟͯ ͦ͜ʹखΛ͍Ε͍ͯ͘ɺͱ͍͏ͷ͕ߴ଎ԽͷΩϞͱ͍͏࿩Λ ͠·ͨ͠ɻ • ࣮ߦܭըΛݟΔʹ͸ Explain ͱ͍͏ΩʔϫʔυΛ ࣮ߦܭըΛݟ͍ͨSQLͷઌ಄ ʹ͚ͭΔɻ 13
14. ࣮ߦܭըͷྫ EXPLAIN SELECT * FROM sales INNER JOIN customers ON sales.customer_id = customers.id WHERE sales.customer_id = 15; QUERY PLAN ---------------------------------------------------------------------- Nested Loop (cost=0.42..502666.88 rows=59 width=147) -> Index Scan using customers_pkey on customers (cost=0.42..8.44 rows=1 width=110) Index Cond: (id = 15) -> Seq Scan on sales (cost=0.00..502657.85 rows=59 width=37) Filter: (customer_id = 15) 14
15. ͱ͸͍͑ɺ ࣮ߦ"ܭը" • EXPLAIN ANALYZE ͱ͢ΔࣄͰ ࣮ߦܭըͱ SQLΛ࣮ࡍʹ࣮ߦ࣮ͨ͠ߦ࣌ؒΛ දࣔͤ͞Δࣄ͕ग़དྷΔɻ 15
16. ࣮ߦܭըͷྫ EXPLAIN ANALYZE SELECT * FROM sales INNER JOIN customers ON sales.customer_id = customers.id WHERE sales.customer_id = 15; QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop (cost=0.42..502666.88 rows=59 width=147) (actual time=9.838..2883.704 rows=60 loops=1) -> Index Scan using customers_pkey on customers (cost=0.42..8.44 rows=1 width=110) (actual time=1.205..1.221 rows=1 loops=1) Index Cond: (id = 15) -> Seq Scan on sales (cost=0.00..502657.85 rows=59 width=37) (actual time=8.603..2881.422 rows=60 loops=1) Filter: (customer_id = 15) Rows Removed by Filter: 23999940 Planning Time: 3.930 ms Execution Time: 2885.418 ms 16
17. ࣮ߦܭըͷྫ EXPLAIN ANALYZE SELECT * FROM sales INNER JOIN customers ON sales.customer_id = customers.id WHERE sales.customer_id = 15; QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop (cost=0.42..502666.88 rows=59 width=147) ʲ(actual time=9.838..2883.704 rows=60 loops=1)ʳ -> Index Scan using customers_pkey on customers (cost=0.42..8.44 rows=1 width=110) ʲ(actual time=1.205..1.221 rows=1 loops=1)ʳ Index Cond: (id = 15) -> Seq Scan on sales (cost=0.00..502657.85 rows=59 width=37) ʲ(actual time=8.603..2881.422 rows=60 loops=1)ʳ Filter: (customer_id = 15) Rows Removed by Filter: 23999940 ʲPlanning Time: 3.930 msʳ ʲExecution Time: 2885.418 msʳ 17
18. Explain݁Ռͷݟํ 18
21. ओͳॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ 21
22. ओͳॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ • εΩϟϯܥϊʔυ΍݁߹ܥϊʔυ͸ෳ਺छྨ͋Δɻ ݁Ռ͸ಉ͚ͩ͡ͲΞϧΰϦζϜ͕ҟͳΔɻ 22
23. ύϥϨϧॲཧʹ͍ͭͯ • Parallel ͱ͍͏Ωʔϫʔυ͕ॲཧ ϊʔυͷલʹͭ͘ɻ • ύϥϨϧΫΤϦ ͱ͍͏PostgreSQLͷ ػೳɻ • 9.6 ʙ αϙʔτ͕ঃʑʹ૿͖͑ͯͯ ͍Δɻ 23
24. ओͳεΩϟϯܥॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Index Scan εΩϟϯ Index Only Scan εΩϟϯ Bitmap Index Scan εΩϟϯ 24
25. εΩϟϯॲཧϊʔυͷҧ͍ Seq Scan • ςʔϒϧͷશ݅Λॱ൪ʹݺͼग़͍ͯ͘͠ɻ • IndexΛ࢖͏·Ͱ΋ແ͍গ਺ͷϨίʔυͷ৔߹͸଎͍͕ɺϨίʔυ͕૿͑ΔʹͭΕ ͯ஗͘ͳΔՄೳੑ͕͋Δɻ Index Scan • IndexͰର৅σʔλʹ͋ͨΓΛ෇͚ͨޙͰର৅ͷσʔλΛݺͼग़͍ͯ͘͠ɻ • IndexͰे෼ʹߜΓࠐΊΔ৔߹͸଎͍ɻͨͩ͠ɺҋӢʹIndexΛషΔͱΠϯσοΫ εɾςʔϒϧͷߏஙʹίετֻ͕͔Δɻ 25
26. εΩϟϯॲཧϊʔυͷҧ͍ Index Only Scan • IndexͷσʔλͷΈΛݺͼग़͍ͯ͘͠ɻ • Indexͷσʔλ͔͠ඞཁ͡Όͳ͍৔߹͸଎͍ɻ Bitmap Index Scan • IndexΛ࢖ͬͯ৚݅ʹ߹க͢ΔߦΛϏοτϚοϓͱͯ͠ϝϞϦ಺ʹੜ੒͠ɺͦͷ಺ ϏοτONͷ΋ͷ͚ͩΛॱ൪ʹݺͼग़͍ͯ͘͠ɻ • Ϗοτԋࢉ౳ʹ΋Ԡ༻ग़དྷΔ͠ɺ2஋ͷ಺ɺยํͷΈΛൺֱ͢Ε͹͍͍͔Β଎͍ɻ ͨͩ͠ɺBitmapΛߏங͢Δॳظίετֻ͕͔Δɻ 26
27. ओͳ݁߹ॲཧϊʔυ ॲཧϊʔυ ෼ྨ Nested Loop ݁߹ Hash Join ݁߹ Marge Join ݁߹ 27
28. ݁߹ϊʔυͷҧ͍ Nested Loop Join • ۦಈද(JOINݩ)Λ1ߦຖʹ ಺෦ද(JOINઌ)ͷશϨίʔυͱ ಥ͖߹Θͤͯ֘౰ͷ΋ͷΛ݁߹͍ͯ͠ ͘ɻ • େ͖͍ςʔϒϧͱେ͖͍ςʔϒϧΛ݁߹ ͢Δͱn × m ճॲཧΛ͠ͳ͚Ε͹͍͚ͳ ͍ͷͰ஗͍ɻ • ۦಈද͕খ͘͞಺෦දͷ݁߹ΩʔʹIndex ͕͋Δ৔߹଎͍ɻ 28
29. ݁߹ϊʔυͷҧ͍ Hash Join • ಺෦දͷ݁߹Ωʔͷ ϋογϡϦετΛ࡞੒ͯ͠ ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ • ϋογϡϦετ͕ϝϞϦʹऩ·Δ৔ ߹͸ඇৗʹ଎͍ɻ • ༧ΊϋογϡϦετΛ࡞੒͢Δඞཁ ͕͋ΔͷͰͦͷ෼ॳظίετֻ͕͔ Δɻ 29
30. ݁߹ϊʔυͷҧ͍ Merge Join • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ ιʔτͨ͠ޙͰ ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͘͠ɻ • େ͖͍ςʔϒϧ × େ͖͍ςʔϒϧͰ ΋ߴ଎Ͱ݁߹ग़དྷΔɻ • ͨͩ͠ࣄલʹͲͪΒͷςʔϒϧ΋ ιʔτ͢Δඞཁ͕͋ΔͷͰ ॳظίετֻ͕͔Δɻ 30
31. νϡʔχϯάͷΩϞ • ֤ํࣜͷτϨʔυΦϑϙΠϯτΛҙࣝͯ͠ࢼߦࡨޡΛ͍ͯ͠ ͘ࣄͰௐ੔Λ͍ͯ͘͠ɻ • ςʔϒϧ͕େ͖͍ͷʹର৅ߦ͕গͳ͍͔ΒIndexషͬͨํ ͕ྑ͍͔΋ʁ • ෆཁͳΧϥϜ·ͰऔͬͯΔ͔ΒΧϥϜݮΒͨ͠Β IndexOnlyScanͰ͍͚Δʁ 31
32. 4. SQLνϡʔχϯά ଎౓վળͷ࣮ྫ 32
33. SQLνϡʔχϯά ଎౓վળͷ࣮ྫ ※ αʔόʔͷߏ੒΍ύϥϝʔλνϡʔχϯάͷ࿩Ͱ͸ແͯ͘ DDLͷཻ౓Ͱ SQLͷԠ౴଎౓Λ଎͘͢ΔվળྫͰ͢ɻ • IndexΛఆٛͯ͠ߴ଎Խ • ϚςϏϡʔΛ࡞੒ͯ͠ߴ଎Խ 33
34. IndexΛఆٛͯ͠ߴ଎Խ • σʔλϕʔεʹ͓͍ͯSQLͷύϑΥʔϚϯεΛ޲্ͤ͞Δํ ๏ͷҰͭɻ • σʔλϕʔε্Ͱͷ࣮ݱͷ࢓૊Έͱͯ͠͸ɺ ݕࡧର৅ͱͳΔσʔλΛ֨ೲͨ͠ϑΟʔϧυͱ ͦͷσʔλ͕࣮ࡍͷςʔϒϧͰͲͷҐஔʹ͋Δ͔Λࣔ͢஋Λ ֨ೲͨ͠ΠϯσοΫεɾςʔϒϧ͕ςʔϒϧͱ͸ผʹ࡞ΒΕ ͦ͜Λࢀর͢ΔΑ͏ͳ࢓૊ΈʹͳͬͯΔɻ 34
35. վળͷ࣮ྫ(Indexߏஙલ) EXPLAIN ANALYZE SELECT * FROM customers WHERE name = 'johnson51'; QUERY PLAN ----------------------------------------------------------------------- Seq Scan on customers (cost=0.00..12089.00 rows=3 width=110) (actual time=1.066..60.822 rows=2 loops=1) Filter: ((name)::text = 'johnson51'::text) Rows Removed by Filter: 399998 Planning Time: 0.649 ms Execution Time: 60.905 ms 35
36. Index࡞੒ͷ੒ޭཁҼ • customersςʔϒϧͷσʔλྔ͸αʔϏεͷ੒௕ͱڞʹ૿͑ ͍ͯ͘ɻ => Seq Scanͷ଎౓͕αʔϏεͷ੒௕ʹ࿈Ε஗͘ͳΓͦ͏ɻ • Ϣʔβʔ໊͸ϢχʔΫͱ͍͏റΓΛαʔϏεͰઃ͚͍ͯΔɻ => σʔλͷภΓ͕গͳ͍ͱ൑அग़དྷΔɻ ʮԿނIndexΛషΔ͔ ΊͬͪΌେࣄ!! ʯ 36
37. Indexͷఆٛ CREATE INDEX customers_name_idx ON customers (name); • customers ςʔϒϧͷ name ΧϥϜʹ customers_name_idx ͱ͍͏໊લʢศ্ٓʣͷ IndexΛఆٛ͢Δ 37
38. վળͷ࣮ྫ(Indexߏஙޙ) EXPLAIN ANALYZE SELECT * FROM customers WHERE name = 'johnson51'; QUERY PLAN ---------------------------------------------------------------------------------- Bitmap Heap Scan on customers (cost=4.45..16.30 rows=3 width=110) (actual time=0.084..0.120 rows=2 loops=1) Recheck Cond: ((name)::text = 'johnson51'::text) Heap Blocks: exact=2 -> Bitmap Index Scan on customers_name_idx (cost=0.00..4.45 rows=3 width=0) (actual time=0.056..0.065 rows=2 loops=1) Index Cond: ((name)::text = 'johnson51'::text) Planning Time: 0.345 ms Execution Time: 0.226 ms 38
39. վળͷલޙͷൺֱ ߲໨ Indexߏஙલ Indexߏஙޙ εΩϟϯํ๏ Seq Scan Bitmap Heap Scan / Bitmap Index Scan ࣮ߦ࣌ؒ 60.905 ms 0.226 ms 39
40. ༗ޮͳIndexΛషΕͨࣄͷ෼ੳ • IndexͷޮՌ͕ൃش͞ΕΔ͚ͩͷ Ϩίʔυ਺͕͋Δɻ • ֘౰σʔλͷΧʔσΟφϦςΟ͕ߴ͘ɺ ߜΓࠐΈͷޮՌ͕ߴ͍ɻ(1/40ສ) 40
41. PostgrteSQLͰͷIndexͷαϙʔτͷ͝঺հ • ࣜʹର͢ΔIndexͷఆٛ ྫɿ LOWER(user_name) ͱ͍͏ࣜʹରͯ͠IndexΛఆٛ • ෦෼తʹIndexΛఆٛ ྫɿ delete_flag=0ͷ৚݅ͷ΋ͱͰ ɹɹ email ʹ Unique IndexΛఆٛ 41
42. ϚςϏϡʔΛ࡞੒ͯ͠ߴ଎Խ • Materialized View Λ࡞੒ͯ͠ߴ଎Խ͢Δख๏ • ViewͷΑ͏Ͱ͋Γͳ͕Βɺ͔͋ͨ΋ςʔϒϧ͔ͷΑ͏ʹ ݁ՌΛอ࣋͢Δ 42
43. View ΍ Materialized View • View • Ϗϡʔͷ࣮ମ͸σʔλΛ࣋ͨͳ͍SQLจͰ͋Γɺ ෺ཧతͳςʔϒϧͱ͸ҟͳΔɻ 43
44. Viewͷྫ • ʮ2019೥ͷ20୅உੑͷച্σʔλΛऔಘ͢Δʯ SELECT * FROM sales WHERE created_at BETWEEN '2019-01-01' AND '2019-12-31' AND gender = 1 AND age BETWEEN 20 AND 29; ্هΛ male_sales_twenty_2019 ͱ͍͏Viewͱͯ͠ఆٛ͢ΔࣄͰ SELECT * FROM male_sales_twenty_2019; ͜ͷΑ͏ʹγϯϓϧͳSQLͱͯ͠ද͢ࣄ͕Ͱ͖Δɻ 44
45. Viewʹ͍ͭͯ • male_sales_twenty_2019 ͸σʔλͷ࣮ମΛ࣋ͨͳ͍ɻ • View ʹରͯ͠ SELECTจΛ࣮ߦ͢Δͱ ৗʹSQL໰͍߹Θ͕࣮ͤߦ͞ΕΔɻ ↓ ͜͜Ͱ Materialized View 45
46. Materialized View ʹ͍ͭͯ • Materialized View ͸SQLͷ݁ՌΛอ࣋͢Δɻ • Materialized View ʹࢀরͷΫΤϦΛྲྀ͢ͱ อ͍࣋ͯ͠ΔSQLͷ݁ՌΛฦ٫͢Δɻ • ௨ৗͷ View ͱҟͳΓৗʹSQL͕ൃߦ͞ΕΔࣄ͕ͳ͘ͳΔɻ • ·ͨɺอ࣋৘ใʹରͯ͠IndexΛఆٛ͢Δࣄ΋Մೳɻ 46
47. Materialized View ʹ͍ͭͯ • REFRESH Λ͢ΔࣄͰอ͍࣋ͯ͠ΔSQLͷ݁ՌΛ ࠶౓ߋ৽͢Δ͜ͱ͕ग़དྷΔɻ • ݱঢ়Ͱ͸ ࠩ෼ߋ৽͸ରԠ͍ͯ͠ͳͯ͘ ϦϑϨογϡͷλΠϛϯάͰSQL͕ϑϧͰ࠶౓࣮ߦ͞ΕΔɻ ※ ViewͱҟͳΓ ࢀরͷ౓ʹSQL͕ൃੜ͢Δ͜ͱ͸ͳ͍͕ ϦϑϨογϡ͢Δ·Ͱ͸࠷৽ͷσʔλ͕൓ө͞Εͳ͍ɻ 47
48. վળલͷSQL • ঎඼ʹରͯ͠ಛूΛ૊Ήɻ • feature ςʔϒϧͷதʹ ݕࡧจࣈྻ ͕֨ೲ͞Ε͍ͯͯ ͦΕΛ࢖༻ͯ͠঎඼σʔλΛߜΓࠐΈΛߦ͏ɻ • Πϝʔδͱͯ͠͸ /item/feature/1 ͷURLΛ։͘ͱ ݕࡧจࣈྻΛݕࡧAPIʹPostͯ݁͠ՌΛऔಘ͠ ͦͷಛू঎඼Ұཡ͕දࣔ͞ΕΔɻ 48
49. վળલͷSQL • ྫ͑͹ɺ id = 1 ʢ PostgreSQL ʣͷಛूͷσʔλΛऔಘ͢Δʹ͸ ҎԼͷΑ͏ͳSQL͕ྲྀΕΔ. SELECT * FROM available_item INNER JOIN item_detail ON available_item.item_id = item_detail.item_id WHERE ( available_item.item_name LIKE '%PostgreSQL%' OR item_detail.article LIKE '%PostgreSQL%' OR item_detail.requirement_text LIKE '%PostgreSQL%' ); ※ available_item ɿ ར༻Մೳͳ঎඼Ϧετ ※ item_detail : ঎඼ͷৄࡉ৘ใ 49
50. վળલͷ࣮ߦܭը (cost, actual_time ল͍ͨ) QUERY PLAN -------------------------------------------------------------------------------- Merge Join Merge Cond: (available_item.item_id = item_detail.item_id) Join Filter: ( ((available_item.title)::text ~~ '%PostgreSQL%'::text) OR (item_detail.article ~~ '%PostgreSQL%'::text) OR ((item_detail.requirement_man_text)::text ~~ '%PostgreSQL%'::text) ) Rows Removed by Join Filter: 121547 -> Index Scan using available_item_item_id_key on available_item -> Index Scan using item_detail_item_id_key on item_detail Planning time: 13.394 ms Execution time: 6635.537 ms 50
51. SQLͰٻΊΒΕΔཁ݅ • LIKE͕தؒҰகɺarticle͸શจݕࡧͳͷͰ Index͕࡞Γʹ͍͘ • ࡏݿ΍ྉۚঢ়ଶ͸ৗʹ࠷৽Λදࣔ͢Δඞཁ͕͋Δ => HTMLΩϟογϡͷ࣮૷ͱ͸૬ੑ͕ѱ͍ • ͱ͸͍͑ɺ঎඼໊΍঎඼આ໌͕มΘΔࣄ͸͋·Γແ͍ͷͰ ʮର৅঎඼ʯ͕มΘΔࣄ͸ͦ͜·Ͱଟ͘ͳ͍ɻ ͜͜Ͱ ಛूͷର৅঎඼ ͷϚςϏϡʔΛ࡞ͬͯߴ଎Խɻ 51
52. ϚςϏϡʔͷ࣮૷ CREATE MATERIALIZED VIEW view_feature_available_item AS WITH target_item AS ( SELECT available_item.item_id, available_item.title, item_detail.requirement_text, item_detail.article FROM available_item INNER JOIN item_detail ON available_item.item_id = item_detail.item_id ) SELECT target_item.item_id , feature.id AS feature_id FROM feature INNER JOIN target_item ON ( target_item.title LIKE CONCAT('%', display_condition_query ,'%') OR target_item.requirement_text LIKE CONCAT('%', display_condition_query ,'%') OR target_item.article LIKE CONCAT('%', display_condition_query ,'%') ); CREATE INDEX view_feature_available_item_item_id_key ON view_feature_available_item (item_id); CREATE INDEX view_feature_available_item_feature_id_key ON view_feature_available_item (feature_id) 52
53. վળޙͷSQL EXPLAIN ANALYZE SELECT * FROM available_item INNER JOIN view_feature_available_item ON available_item.item_id = view_feature_available_item.item_id WHERE view_feature_available_item.feature_id = 1; • feature.id = 1 ͕ PostgreSQL ͷಛू. 53
54. վળޙͷ࣮ߦܭը (cost, actual_time ল͍ͨ) QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop -> Hash Join Hash Cond: (available_item.item_id = view_feature_available_item.item_id) -> Seq Scan on available_item -> Hash Buckets: 16384 Batches: 1 Memory Usage: 592kB -> Bitmap Heap Scan on view_feature_available_item Recheck Cond: (feature_id = 1) Heap Blocks: exact=628 -> Bitmap Index Scan on view_feature_available_item_feature_id_key Index Cond: (feature_id = 1) -> Index Scan using item_detail_item_id_key on item_detail Index Cond: (item_id = available_item.item_id) Planning time: 9.376 ms Execution time: 3424.800 ms 54
55. վળͷલޙͷൺֱ ߲໨ ϚςϏϡʔߏஙલ ϚςϏϡʔߏஙޙ ࣮ߦ࣌ؒ 6635.537 ms 3424.800 ms 55
56. ϚςϏϡʔΛ࡞ͬͨ෼ੳ • Index͕࢖͑ͳ͔ͬͨঢ়ଶ͔Β feature_id=1ͱ͍͏ܗࣜͰIndexΛޮ͔ͤΔࣄ͕ ग़དྷΔΑ͏ʹͳͬͨɻ • ϦΞϧλΠϜͷࢀর͕ఘΊΔࣄ͕ग़དྷΔ ର৅঎඼ ͱ ϦΞϧλΠϜͷࢀরΛఘΊΔࣄ͕ग़དྷͳ͍ ࡏݿ΍ྉۚ৘ใΛ ෼͚ͯߏங͢Δࣄ͕ग़དྷͨɻ 56
57. 5. ·ͱΊ 57
58. ·ͱΊ • ࠓճ͸ Explainจͷجૅͷ෦෼ͱฐࣾͰ࣮ࡍʹߦͬͨվળͷ ࣮ྫΛ͝঺հ͠·ͨ͠ɻ • SQLνϡʔχϯάʹ͓͍ͯ͸ਖ਼ղ͸ͳͯͦ͘ͷαʔϏεɺ ͦͷςʔϒϧຖʹ΍Γํ͕͋Δɺͱࢥ͍·͢ɻ • ਖ਼͍͠SQLͷ࢖͍ํΛ஌ͬͯ ྑ͍αʔϏεΛ࡞͍͖ͬͯ· ͠ΐ͏ʂ 58
59. ࢀߟจݙ • Explaining EXPLAIN ୈ̎ճ • PostgreSQLΫΤϦ࣮ߦͷجૅ஌ࣝ ʙExplainΛಡΈղ͜͏ʙ • ࠷୹͔ͭ࠷଎ʹΞΫηε͢ΔʮDBߴ଎Խٕज़ʯʢલฤʣ • PostgreSQL Conference Japan 2018 ʲT3ʳSQL νϡʔχϯ άͷجૅ • PostgreSQL 11.5 จॻ 59
60. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ 60