Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

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

Takahashi Ikki

October 14, 2019
Tweet

More Decks by Takahashi Ikki

Other Decks in Programming

Transcript

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size slide

  18. Explain݁Ռͷݟํ
    18

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  33. վળͷ࣮ྫ(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 full-size slide

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

    View full-size slide

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

    View full-size slide

  36. վળͷ࣮ྫ(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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  42. 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 full-size slide

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

    ͜͜Ͱ Materialized View
    45

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  47. վળલͷ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 full-size slide

  48. վળલͷ࣮ߦܭը (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 full-size slide

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

    View full-size slide

  50. ϚςϏϡʔͷ࣮૷
    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 full-size slide

  51. վળޙͷ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 full-size slide

  52. վળޙͷ࣮ߦܭը (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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide