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

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

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

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

F891bc57aad1b6bdbf344358e7fec3cc?s=128

Takahashi Ikki

October 14, 2019
Tweet

Transcript

  1. 9.

    ࣮ߦܭը͸ͲͷΑ͏ʹܾ·Δͷ͔ • લఏͱͯ͠ ϓϥϯφ͸ σʔλϕʔεʹͲΜͳ஋͕ೖͬͯΔ͔ Λ஌Βͳ͍ɻ • ϓϥϯφ͸ σʔλϕʔε͕؅ཧ͍ͯ͠Δ ౷ܭ৘ใ

    Λݟͯ ʮ͜ͷςʔϒϧʹ͸͜Μͳσʔλ͕ೖͬͯΔ܏޲ʹ͋Δ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷଎ͩΖ͏ʯ ͱ࣮ߦܭըΛܾΊΔɻ 9
  2. 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
  3. 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
  4. 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
  5. 19.

    19

  6. 20.

    20

  7. 21.

    ओͳॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index

    Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ 21
  8. 22.

    ओͳॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index

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

    εΩϟϯॲཧϊʔυͷҧ͍ Seq Scan • ςʔϒϧͷશ݅Λॱ൪ʹݺͼग़͍ͯ͘͠ɻ • IndexΛ࢖͏·Ͱ΋ແ͍গ਺ͷϨίʔυͷ৔߹͸଎͍͕ɺϨίʔυ͕૿͑ΔʹͭΕ ͯ஗͘ͳΔՄೳੑ͕͋Δɻ Index Scan

    • IndexͰର৅σʔλʹ͋ͨΓΛ෇͚ͨޙͰର৅ͷσʔλΛݺͼग़͍ͯ͘͠ɻ • IndexͰे෼ʹߜΓࠐΊΔ৔߹͸଎͍ɻͨͩ͠ɺҋӢʹIndexΛషΔͱΠϯσοΫ εɾςʔϒϧͷߏஙʹίετֻ͕͔Δɻ 25
  10. 26.

    εΩϟϯॲཧϊʔυͷҧ͍ Index Only Scan • IndexͷσʔλͷΈΛݺͼग़͍ͯ͘͠ɻ • Indexͷσʔλ͔͠ඞཁ͡Όͳ͍৔߹͸଎͍ɻ Bitmap Index

    Scan • IndexΛ࢖ͬͯ৚݅ʹ߹க͢ΔߦΛϏοτϚοϓͱͯ͠ϝϞϦ಺ʹੜ੒͠ɺͦͷ಺ ϏοτONͷ΋ͷ͚ͩΛॱ൪ʹݺͼग़͍ͯ͘͠ɻ • Ϗοτԋࢉ౳ʹ΋Ԡ༻ग़དྷΔ͠ɺ2஋ͷ಺ɺยํͷΈΛൺֱ͢Ε͹͍͍͔Β଎͍ɻ ͨͩ͠ɺBitmapΛߏங͢Δॳظίετֻ͕͔Δɻ 26
  11. 28.

    ݁߹ϊʔυͷҧ͍ Nested Loop Join • ۦಈද(JOINݩ)Λ1ߦຖʹ ಺෦ද(JOINઌ)ͷશϨίʔυͱ ಥ͖߹Θͤͯ֘౰ͷ΋ͷΛ݁߹͍ͯ͠ ͘ɻ •

    େ͖͍ςʔϒϧͱେ͖͍ςʔϒϧΛ݁߹ ͢Δͱn × m ճॲཧΛ͠ͳ͚Ε͹͍͚ͳ ͍ͷͰ஗͍ɻ • ۦಈද͕খ͘͞಺෦දͷ݁߹ΩʔʹIndex ͕͋Δ৔߹଎͍ɻ 28
  12. 30.

    ݁߹ϊʔυͷҧ͍ Merge Join • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ ιʔτͨ͠ޙͰ ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͘͠ɻ • େ͖͍ςʔϒϧ ×

    େ͖͍ςʔϒϧͰ ΋ߴ଎Ͱ݁߹ग़དྷΔɻ • ͨͩ͠ࣄલʹͲͪΒͷςʔϒϧ΋ ιʔτ͢Δඞཁ͕͋ΔͷͰ ॳظίετֻ͕͔Δɻ 30
  13. 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
  14. 37.

    Indexͷఆٛ CREATE INDEX customers_name_idx ON customers (name); • customers ςʔϒϧͷ

    name ΧϥϜʹ customers_name_idx ͱ͍͏໊લʢศ্ٓʣͷ IndexΛఆٛ͢Δ 37
  15. 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
  16. 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
  17. 46.

    Materialized View ʹ͍ͭͯ • Materialized View ͸SQLͷ݁ՌΛอ࣋͢Δɻ • Materialized View

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

    Materialized View ʹ͍ͭͯ • REFRESH Λ͢ΔࣄͰอ͍࣋ͯ͠ΔSQLͷ݁ՌΛ ࠶౓ߋ৽͢Δ͜ͱ͕ग़དྷΔɻ • ݱঢ়Ͱ͸ ࠩ෼ߋ৽͸ରԠ͍ͯ͠ͳͯ͘

    ϦϑϨογϡͷλΠϛϯάͰSQL͕ϑϧͰ࠶౓࣮ߦ͞ΕΔɻ ※ ViewͱҟͳΓ ࢀরͷ౓ʹSQL͕ൃੜ͢Δ͜ͱ͸ͳ͍͕ ϦϑϨογϡ͢Δ·Ͱ͸࠷৽ͷσʔλ͕൓ө͞Εͳ͍ɻ 47
  19. 48.

    վળલͷSQL • ঎඼ʹରͯ͠ಛूΛ૊Ήɻ • feature ςʔϒϧͷதʹ ݕࡧจࣈྻ ͕֨ೲ͞Ε͍ͯͯ ͦΕΛ࢖༻ͯ͠঎඼σʔλΛߜΓࠐΈΛߦ͏ɻ •

    Πϝʔδͱͯ͠͸ /item/feature/1 ͷURLΛ։͘ͱ ݕࡧจࣈྻΛݕࡧAPIʹPostͯ݁͠ՌΛऔಘ͠ ͦͷಛू঎඼Ұཡ͕දࣔ͞ΕΔɻ 48
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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