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

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

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

Transcript

1. SQLνϡʔχϯά
   =ཧ࿦ͱվળͷ࣮ྫ=
   2019-11-15
   PostgreSQL Conference Japan 2019
   ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํࢧ෦௕
   ߴڮɹҰٍ
   1
   

   View Slide

2. ஫ҙࣄ߲
   • εϥΠυ͸ެ։͍ͯ͠·͢ɻ
   • ࣭໰ͱ͔͝ࢦఠͱ͔ॾʑͷϑΟʔυόοΫ͕͋Ε͹
   #pgcon19j ΁͓ئ͍க͠·͢ɻ
   • αʔόʔͷߏ੒΍ύϥϝʔλνϡʔχϯάͷ࿩Ͱ͸ແͯ͘
   DDLʹΑͬͯνϡʔχϯάΛ͢ΔSQLͷԠ౴଎౓Λվળ͢Δ
   ͓࿩Ͱ͢ɻ
   2
   

   View Slide

3. ͓͠ͳ͕͖
   1. ࣗݾ঺հ
   2. SQL͕࣮ߦ͞ΕΔ࢓૊Έ
   3. ࣮ߦܭըͷݟํ
   4. SQLνϡʔχϯάͷ଎౓վળͷ࣮ྫ
   5. ·ͱΊ
   3
   

   View Slide

4. 1. ࣗݾ঺հ
   • ߴڮ Ұٍ
   • Ԭࢁࡏॅ
   • גࣜձࣾΦϛΧϨ
   WebΞϓϦέʔγϣϯΤϯδχΞ
   • ೔ຊPostgreSQLϢʔβʔձ
   தࠃࢧ෦௕
   4
   

   View Slide

5. גࣜձࣾΦϛΧϨ
   • શࠃͷࠗ׆ύʔςΟʔ
   ໿30,000݅Λܝࡌͯ͠Δ
   ϙʔλϧαΠτ.
   • ग़ձ͍͕0ΛZeroʹ͢Δ
   ΛVisionʹ೔ʑ׆ಈͯ͠·͢
   5
   

   View Slide

6. 2. SQL͕࣮ߦ͞ΕΔ࢓૊Έ
   6
   

   View Slide

7. SQL͕࣮ߦ͞ΕΔ࢓૊Έ
   7
   

   View Slide

8. SQLͷ࣮ߦͰ཈͓͑ͯ͘΂͖ϙΠϯτ
   • ϓϥϯφ͕ ࣮ߦܭը Λ࡞Γ
   ΤΫθΩϡʔλ͕ͦͷ࣮ߦܭըʹԊͬͯ
   σʔλϕʔε͔ΒσʔλΛ໰͍߹Θͤͯ݁ՌΛฦ͢
   => Ͱ͸ɺͦͷ࣮ߦܭը͸ͲͷΑ͏ʹܾ·Δͷ͔ɻ
   8
   

   View Slide

9. ࣮ߦܭը͸ͲͷΑ͏ʹܾ·Δͷ͔
   • લఏͱͯ͠ ϓϥϯφ͸ σʔλϕʔεʹͲΜͳ஋͕ೖͬͯΔ͔
   Λ஌Βͳ͍ɻ
   • ϓϥϯφ͸ σʔλϕʔε͕؅ཧ͍ͯ͠Δ ౷ܭ৘ใ Λݟͯ
   ʮ͜ͷςʔϒϧʹ͸͜Μͳσʔλ͕ೖͬͯΔ܏޲ʹ͋Δ͔Β
   ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷଎ͩΖ͏ʯ
   ͱ࣮ߦܭըΛܾΊΔɻ
   9
   

   View Slide

10. SQLνϡʔχϯάͷ֩
    • ࣮ߦܭըΛʰݟͯʱ஗ͦ͏ͳ΋ͷ͸ແ͍͔ʁ
    => ࣮ߦܭըΛݟ͍ͨSQLͷઌ಄ʹ͋ΔΩʔϫʔυΛ͚ͭΔࣄͰ
    ɹɹ࣮ߦܭըΛਓʹ෼͔ΔܗࣜͰݟΔࣄ͕ग़དྷΔɻ
    10
    

    View Slide

11. 2. SQL͕࣮ߦ͞ΕΔ࢓૊Έ
    • SQLͷ࣮ߦʹ͓͍ͯ
    ύʔαʔɾϦϥΠλɾϓϥϯφɾΤΫθΩϡʔλ
    ͱ͍͏໾ׂ͕͋Δɻ
    • ϓϥϯφ͸౷ܭ৘ใΛݩʹ࣮ߦܭըΛ࡞Γ
    ΤΫθΩϡʔλ͕࣮ߦܭը௨ΓʹSQLΛ࣮ߦ͢Δ
    • ࣮ߦܭըΛಡΜͰ஗͘ͳΓͦ͏ͳՕॴΛݟ͍ͯ͘ࣄ͕
    SQLνϡʔχϯάͷ֩ͱͳΔɻ
    11
    

    View Slide

12. 3. ࣮ߦܭըͷݟํ
    12
    

    View Slide

13. • ࣮ߦܭըΛಡΜͰ ஗͘ͳΓͦ͏ͳ෦෼Λݟͯ
    ͦ͜ʹखΛ͍Ε͍ͯ͘ɺͱ͍͏ͷ͕ߴ଎ԽͷΩϞͱ͍͏࿩Λ
    ͠·ͨ͠ɻ
    • ࣮ߦܭըΛݟΔʹ͸
    Explain ͱ͍͏ΩʔϫʔυΛ ࣮ߦܭըΛݟ͍ͨSQLͷઌ಄
    ʹ͚ͭΔɻ
    13
    

    View Slide

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
    

    View Slide

15. ͱ͸͍͑ɺ ࣮ߦ"ܭը"
    • EXPLAIN ANALYZE ͱ͢ΔࣄͰ
    ࣮ߦܭըͱ SQLΛ࣮ࡍʹ࣮ߦ࣮ͨ͠ߦ࣌ؒΛ
    දࣔͤ͞Δࣄ͕ग़དྷΔɻ
    15
    

    View Slide

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
    

    View Slide

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
    

    View Slide

18. Explain݁Ռͷݟํ
    18
    

    View Slide

19. View Slide

20. View Slide

21. ओͳॲཧϊʔυ
    ॲཧϊʔυ ෼ྨ
    Seq Scan εΩϟϯ Nested Loop ݁߹
    Index Scan εΩϟϯ Hash Join ݁߹
    Index Only Scan εΩϟϯ Marge Join ݁߹
    Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ
    Sort ιʔτ Aggregate άϧʔϐϯάܥ
    21
    

    View Slide

22. ओͳॲཧϊʔυ
    ॲཧϊʔυ ෼ྨ
    Seq Scan εΩϟϯ Nested Loop ݁߹
    Index Scan εΩϟϯ Hash Join ݁߹
    Index Only Scan εΩϟϯ Marge Join ݁߹
    Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ
    Sort ιʔτ Aggregate άϧʔϐϯάܥ
    • εΩϟϯܥϊʔυ΍݁߹ܥϊʔυ͸ෳ਺छྨ͋Δɻ
    ݁Ռ͸ಉ͚ͩ͡ͲΞϧΰϦζϜ͕ҟͳΔɻ
    22
    

    View Slide

23. ύϥϨϧॲཧʹ͍ͭͯ
    • Parallel ͱ͍͏Ωʔϫʔυ͕ॲཧ
    ϊʔυͷલʹͭ͘ɻ
    • ύϥϨϧΫΤϦ ͱ͍͏PostgreSQLͷ
    ػೳɻ
    • 9.6 ʙ αϙʔτ͕ঃʑʹ૿͖͑ͯͯ
    ͍Δɻ
    23
    

    View Slide

24. ओͳεΩϟϯܥॲཧϊʔυ
    ॲཧϊʔυ ෼ྨ
    Seq Scan εΩϟϯ
    Index Scan εΩϟϯ
    Index Only Scan εΩϟϯ
    Bitmap Index Scan εΩϟϯ
    24
    

    View Slide

25. εΩϟϯॲཧϊʔυͷҧ͍
    Seq Scan
    • ςʔϒϧͷશ݅Λॱ൪ʹݺͼग़͍ͯ͘͠ɻ
    • IndexΛ࢖͏·Ͱ΋ແ͍গ਺ͷϨίʔυͷ৔߹͸଎͍͕ɺϨίʔυ͕૿͑ΔʹͭΕ
    ͯ஗͘ͳΔՄೳੑ͕͋Δɻ
    Index Scan
    • IndexͰର৅σʔλʹ͋ͨΓΛ෇͚ͨޙͰର৅ͷσʔλΛݺͼग़͍ͯ͘͠ɻ
    • IndexͰे෼ʹߜΓࠐΊΔ৔߹͸଎͍ɻͨͩ͠ɺҋӢʹIndexΛషΔͱΠϯσοΫ
    εɾςʔϒϧͷߏஙʹίετֻ͕͔Δɻ
    25
    

    View Slide

26. εΩϟϯॲཧϊʔυͷҧ͍
    Index Only Scan
    • IndexͷσʔλͷΈΛݺͼग़͍ͯ͘͠ɻ
    • Indexͷσʔλ͔͠ඞཁ͡Όͳ͍৔߹͸଎͍ɻ
    Bitmap Index Scan
    • IndexΛ࢖ͬͯ৚݅ʹ߹க͢ΔߦΛϏοτϚοϓͱͯ͠ϝϞϦ಺ʹੜ੒͠ɺͦͷ಺
    ϏοτONͷ΋ͷ͚ͩΛॱ൪ʹݺͼग़͍ͯ͘͠ɻ
    • Ϗοτԋࢉ౳ʹ΋Ԡ༻ग़དྷΔ͠ɺ2஋ͷ಺ɺยํͷΈΛൺֱ͢Ε͹͍͍͔Β଎͍ɻ
    ͨͩ͠ɺBitmapΛߏங͢Δॳظίετֻ͕͔Δɻ
    26
    

    View Slide

27. ओͳ݁߹ॲཧϊʔυ
    ॲཧϊʔυ ෼ྨ
    Nested Loop ݁߹
    Hash Join ݁߹
    Marge Join ݁߹
    27
    

    View Slide

28. ݁߹ϊʔυͷҧ͍
    Nested Loop Join
    • ۦಈද(JOINݩ)Λ1ߦຖʹ
    ಺෦ද(JOINઌ)ͷશϨίʔυͱ
    ಥ͖߹Θͤͯ֘౰ͷ΋ͷΛ݁߹͍ͯ͠
    ͘ɻ
    • େ͖͍ςʔϒϧͱେ͖͍ςʔϒϧΛ݁߹
    ͢Δͱn × m ճॲཧΛ͠ͳ͚Ε͹͍͚ͳ
    ͍ͷͰ஗͍ɻ
    • ۦಈද͕খ͘͞಺෦දͷ݁߹ΩʔʹIndex
    ͕͋Δ৔߹଎͍ɻ
    28
    

    View Slide

29. ݁߹ϊʔυͷҧ͍
    Hash Join
    • ಺෦දͷ݁߹Ωʔͷ
    ϋογϡϦετΛ࡞੒ͯ͠
    ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ
    • ϋογϡϦετ͕ϝϞϦʹऩ·Δ৔
    ߹͸ඇৗʹ଎͍ɻ
    • ༧ΊϋογϡϦετΛ࡞੒͢Δඞཁ
    ͕͋ΔͷͰͦͷ෼ॳظίετֻ͕͔
    Δɻ
    29
    

    View Slide

30. ݁߹ϊʔυͷҧ͍
    Merge Join
    • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ
    ιʔτͨ͠ޙͰ
    ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͘͠ɻ
    • େ͖͍ςʔϒϧ × େ͖͍ςʔϒϧͰ
    ΋ߴ଎Ͱ݁߹ग़དྷΔɻ
    • ͨͩ͠ࣄલʹͲͪΒͷςʔϒϧ΋
    ιʔτ͢Δඞཁ͕͋ΔͷͰ
    ॳظίετֻ͕͔Δɻ
    30
    

    View Slide

31. νϡʔχϯάͷΩϞ
    • ֤ํࣜͷτϨʔυΦϑϙΠϯτΛҙࣝͯ͠ࢼߦࡨޡΛ͍ͯ͠
    ͘ࣄͰௐ੔Λ͍ͯ͘͠ɻ
    • ςʔϒϧ͕େ͖͍ͷʹର৅ߦ͕গͳ͍͔ΒIndexషͬͨํ
    ͕ྑ͍͔΋ʁ
    • ෆཁͳΧϥϜ·ͰऔͬͯΔ͔ΒΧϥϜݮΒͨ͠Β
    IndexOnlyScanͰ͍͚Δʁ
    31
    

    View Slide

32. 4. SQLνϡʔχϯά
    ଎౓վળͷ࣮ྫ
    32
    

    View Slide

33. SQLνϡʔχϯά ଎౓վળͷ࣮ྫ
    ※ αʔόʔͷߏ੒΍ύϥϝʔλνϡʔχϯάͷ࿩Ͱ͸ແͯ͘
    DDLͷཻ౓Ͱ SQLͷԠ౴଎౓Λ଎͘͢ΔվળྫͰ͢ɻ
    • IndexΛఆٛͯ͠ߴ଎Խ
    • ϚςϏϡʔΛ࡞੒ͯ͠ߴ଎Խ
    33
    

    View Slide

34. IndexΛఆٛͯ͠ߴ଎Խ
    • σʔλϕʔεʹ͓͍ͯSQLͷύϑΥʔϚϯεΛ޲্ͤ͞Δํ
    ๏ͷҰͭɻ
    • σʔλϕʔε্Ͱͷ࣮ݱͷ࢓૊Έͱͯ͠͸ɺ
    ݕࡧର৅ͱͳΔσʔλΛ֨ೲͨ͠ϑΟʔϧυͱ
    ͦͷσʔλ͕࣮ࡍͷςʔϒϧͰͲͷҐஔʹ͋Δ͔Λࣔ͢஋Λ
    ֨ೲͨ͠ΠϯσοΫεɾςʔϒϧ͕ςʔϒϧͱ͸ผʹ࡞ΒΕ
    ͦ͜Λࢀর͢ΔΑ͏ͳ࢓૊ΈʹͳͬͯΔɻ
    34
    

    View Slide

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
    

    View Slide

36. Index࡞੒ͷ੒ޭཁҼ
    • customersςʔϒϧͷσʔλྔ͸αʔϏεͷ੒௕ͱڞʹ૿͑
    ͍ͯ͘ɻ
    => Seq Scanͷ଎౓͕αʔϏεͷ੒௕ʹ࿈Ε஗͘ͳΓͦ͏ɻ
    • Ϣʔβʔ໊͸ϢχʔΫͱ͍͏റΓΛαʔϏεͰઃ͚͍ͯΔɻ
    => σʔλͷภΓ͕গͳ͍ͱ൑அग़དྷΔɻ
    ʮԿނIndexΛషΔ͔ ΊͬͪΌେࣄ!! ʯ
    36
    

    View Slide

37. Indexͷఆٛ
    CREATE INDEX customers_name_idx ON customers (name);
    • customers ςʔϒϧͷ name ΧϥϜʹ
    customers_name_idx ͱ͍͏໊લʢศ্ٓʣͷ
    IndexΛఆٛ͢Δ
    37
    

    View Slide

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
    

    View Slide

39. վળͷલޙͷൺֱ
    ߲໨ Indexߏஙલ Indexߏஙޙ
    εΩϟϯํ๏ Seq Scan Bitmap Heap Scan /
    Bitmap Index Scan
    ࣮ߦ࣌ؒ 60.905 ms 0.226 ms
    39
    

    View Slide

40. ༗ޮͳIndexΛషΕͨࣄͷ෼ੳ
    • IndexͷޮՌ͕ൃش͞ΕΔ͚ͩͷ
    Ϩίʔυ਺͕͋Δɻ
    • ֘౰σʔλͷΧʔσΟφϦςΟ͕ߴ͘ɺ
    ߜΓࠐΈͷޮՌ͕ߴ͍ɻ(1/40ສ)
    40
    

    View Slide

41. PostgrteSQLͰͷIndexͷαϙʔτͷ͝঺հ
    • ࣜʹର͢ΔIndexͷఆٛ
    ྫɿ LOWER(user_name) ͱ͍͏ࣜʹରͯ͠IndexΛఆٛ
    • ෦෼తʹIndexΛఆٛ
    ྫɿ delete_flag=0ͷ৚݅ͷ΋ͱͰ
    ɹɹ email ʹ Unique IndexΛఆٛ
    41
    

    View Slide

42. ϚςϏϡʔΛ࡞੒ͯ͠ߴ଎Խ
    • Materialized View Λ࡞੒ͯ͠ߴ଎Խ͢Δख๏
    • ViewͷΑ͏Ͱ͋Γͳ͕Βɺ͔͋ͨ΋ςʔϒϧ͔ͷΑ͏ʹ
    ݁ՌΛอ࣋͢Δ
    42
    

    View Slide

43. View ΍ Materialized View
    • View
    • Ϗϡʔͷ࣮ମ͸σʔλΛ࣋ͨͳ͍SQLจͰ͋Γɺ
    ෺ཧతͳςʔϒϧͱ͸ҟͳΔɻ
    43
    

    View Slide

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
    

    View Slide

45. Viewʹ͍ͭͯ
    • male_sales_twenty_2019 ͸σʔλͷ࣮ମΛ࣋ͨͳ͍ɻ
    • View ʹରͯ͠ SELECTจΛ࣮ߦ͢Δͱ
    ৗʹSQL໰͍߹Θ͕࣮ͤߦ͞ΕΔɻ
    ↓
    ͜͜Ͱ Materialized View
    45
    

    View Slide

46. Materialized View ʹ͍ͭͯ
    • Materialized View ͸SQLͷ݁ՌΛอ࣋͢Δɻ
    • Materialized View ʹࢀরͷΫΤϦΛྲྀ͢ͱ
    อ͍࣋ͯ͠ΔSQLͷ݁ՌΛฦ٫͢Δɻ
    • ௨ৗͷ View ͱҟͳΓৗʹSQL͕ൃߦ͞ΕΔࣄ͕ͳ͘ͳΔɻ
    • ·ͨɺอ࣋৘ใʹରͯ͠IndexΛఆٛ͢Δࣄ΋Մೳɻ
    46
    

    View Slide

47. Materialized View ʹ͍ͭͯ
    • REFRESH Λ͢ΔࣄͰอ͍࣋ͯ͠ΔSQLͷ݁ՌΛ
    ࠶౓ߋ৽͢Δ͜ͱ͕ग़དྷΔɻ
    • ݱঢ়Ͱ͸ ࠩ෼ߋ৽͸ରԠ͍ͯ͠ͳͯ͘
    ϦϑϨογϡͷλΠϛϯάͰSQL͕ϑϧͰ࠶౓࣮ߦ͞ΕΔɻ
    ※ ViewͱҟͳΓ ࢀরͷ౓ʹSQL͕ൃੜ͢Δ͜ͱ͸ͳ͍͕
    ϦϑϨογϡ͢Δ·Ͱ͸࠷৽ͷσʔλ͕൓ө͞Εͳ͍ɻ
    47
    

    View Slide

48. վળલͷSQL
    • ঎඼ʹରͯ͠ಛूΛ૊Ήɻ
    • feature ςʔϒϧͷதʹ ݕࡧจࣈྻ ͕֨ೲ͞Ε͍ͯͯ
    ͦΕΛ࢖༻ͯ͠঎඼σʔλΛߜΓࠐΈΛߦ͏ɻ
    • Πϝʔδͱͯ͠͸ /item/feature/1 ͷURLΛ։͘ͱ
    ݕࡧจࣈྻΛݕࡧAPIʹPostͯ݁͠ՌΛऔಘ͠
    ͦͷಛू঎඼Ұཡ͕දࣔ͞ΕΔɻ
    48
    

    View Slide

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
    

    View Slide

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
    

    View Slide

51. SQLͰٻΊΒΕΔཁ݅
    • LIKE͕தؒҰகɺarticle͸શจݕࡧͳͷͰ
    Index͕࡞Γʹ͍͘
    • ࡏݿ΍ྉۚঢ়ଶ͸ৗʹ࠷৽Λදࣔ͢Δඞཁ͕͋Δ
    => HTMLΩϟογϡͷ࣮૷ͱ͸૬ੑ͕ѱ͍
    • ͱ͸͍͑ɺ঎඼໊΍঎඼આ໌͕มΘΔࣄ͸͋·Γແ͍ͷͰ
    ʮର৅঎඼ʯ͕มΘΔࣄ͸ͦ͜·Ͱଟ͘ͳ͍ɻ
    ͜͜Ͱ ಛूͷର৅঎඼ ͷϚςϏϡʔΛ࡞ͬͯߴ଎Խɻ
    51
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

55. վળͷલޙͷൺֱ
    ߲໨ ϚςϏϡʔߏஙલ ϚςϏϡʔߏஙޙ
    ࣮ߦ࣌ؒ 6635.537 ms 3424.800 ms
    55
    

    View Slide

56. ϚςϏϡʔΛ࡞ͬͨ෼ੳ
    • Index͕࢖͑ͳ͔ͬͨঢ়ଶ͔Β
    feature_id=1ͱ͍͏ܗࣜͰIndexΛޮ͔ͤΔࣄ͕
    ग़དྷΔΑ͏ʹͳͬͨɻ
    • ϦΞϧλΠϜͷࢀর͕ఘΊΔࣄ͕ग़དྷΔ ର৅঎඼ ͱ
    ϦΞϧλΠϜͷࢀরΛఘΊΔࣄ͕ग़དྷͳ͍ ࡏݿ΍ྉۚ৘ใΛ
    ෼͚ͯߏங͢Δࣄ͕ग़དྷͨɻ
    56
    

    View Slide

57. 5. ·ͱΊ
    57
    

    View Slide

58. ·ͱΊ
    • ࠓճ͸ Explainจͷجૅͷ෦෼ͱฐࣾͰ࣮ࡍʹߦͬͨվળͷ
    ࣮ྫΛ͝঺հ͠·ͨ͠ɻ
    • SQLνϡʔχϯάʹ͓͍ͯ͸ਖ਼ղ͸ͳͯͦ͘ͷαʔϏεɺ
    ͦͷςʔϒϧຖʹ΍Γํ͕͋Δɺͱࢥ͍·͢ɻ
    • ਖ਼͍͠SQLͷ࢖͍ํΛ஌ͬͯ ྑ͍αʔϏεΛ࡞͍͖ͬͯ·
    ͠ΐ͏ʂ
    58
    

    View Slide

59. ࢀߟจݙ
    • Explaining EXPLAIN ୈ̎ճ
    • PostgreSQLΫΤϦ࣮ߦͷجૅ஌ࣝ ʙExplainΛಡΈղ͜͏ʙ
    • ࠷୹͔ͭ࠷଎ʹΞΫηε͢ΔʮDBߴ଎Խٕज़ʯʢલฤʣ
    • PostgreSQL Conference Japan 2018 ʲT3ʳSQL νϡʔχϯ
    άͷجૅ
    • PostgreSQL 11.5 จॻ
    59
    

    View Slide

60. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ
    60
    

    View Slide