SQLνϡʔχϯά ཧ࿦ͱ࣮ફ 2019-10-05 OSC 2019 ৽ׁ

஫ҙࣄ߲ 4 εϥΠυ͸ެ։͍ͯ͠·͢ɻ 4 ࣭໰ͱ͔͕͋Ε͹ ೔ຊPostgreSQLϢʔβձ ϒʔεͰʂ 4 ௚઀ͩͱஏ͔͍ͣ͠ɻɻͬͯํ͸ #osc19ni ʹʂ 4 ֶੜͷํͱ͔DBʹ͍ͭͯ͋·Γৄ͘͠ແ͍ํΛର৅ʹͨ͠ جૅతͳॴͷ͓࿩Ͱ͢ɻ

ಥવͰ͕͢ԿͷάϥϑͰ͠ΐ͏ʁ

Կͷάϥϑʁ 4 Ҏલ ฐࣾͷӡӦαΠτʹͯ TV๒ʹΑΓDB΁ͷΞΫηε͕ ٸ૿ͨ࣌͠ͷάϥϑ 4 ͜ͷؒαΠτ͸΄΅ΞΫηεग़དྷͳ͔ͬ ͨɻ ཧ༝͸DBɻ 4 ͦͷଞʹ΋ αΠτͷදࣔ଎౓͕3ඵΛ௒͑Δͱ 3ਓʹ1ਓ͕ݟΔͷΛࣙΊͯ͠·͏ɻ ͔ͤͬ͘ྑ͍΋ͷΛ࡞ͬͯ΋ DB͕ࢮ͵=ച্্͕͛ΒΕͳ͍ɻ

ࠓ೔ͷ࿩ͷ໨ඪ 4 σʔλϕʔεͷSQLͷԠ౴଎౓͕஗͍৔߹ʹ Ͳ͕͜஗͍ͷ͔ௐࠪ͢Δํ๏ͱ վળํ๏ͷجຊతͳॴΛ஌ͬͯ΋Β͏ɻ

͓͠ͳ͕͖ 4 ࣗݾ঺հ 4 SQL࣮ߦͷ࢓૊Έ 4 SQLνϡʔχϯάͷجૅ 4 ·ͱΊ

1.ࣗݾ঺հ 4 ߴڮɹҰٍ 4 Ԭࢁࡏॅ ʢ৽ׁདྷΔͷॳʂʣ 4 גࣜձࣾΦϛΧϨ WebΤϯδχΞ 4 ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํ ࢧ෦௕

̎ɽSQL࣮ߦͷ࢓૊Έ

SQLͱ͸ DBʹ͓͍ͯɺσʔλͷૢ࡞΍ఆٛΛߦ͏ͨΊͷ σʔλϕʔεݴޠʢ໰͍߹ΘͤݴޠʣͷҰͭɻ By. ʰ https://ja.wikipedia.org/wiki/SQL ʱ

SQLͱ͸ ྫ SELECT ϝʔϧΞυϨε, ஀ੜ೔, ੑผ FROM ސ٬৘ใ WHERE ໊લ = 'ߴڮ'; 4 ߏจͷҙຯ ʰސ٬৘ใʱ ͱ͍͏ςʔϒϧ͔Β ʰ໊લ͕ 'ߴڮ'ʱʹ߹க͍ͯ͠ΔσʔλΛର৅ʹ ʰϝʔϧΞυϨεɺ஀ੜ೔ɺੑผʱͷσʔλΛ͍ͩ͘͞ɻ

SQLจ͕ॲཧ͞Ε͍ͯ͘ྲྀΕ

SQLจ͕ॲཧ͞Ε͍ͯ͘ྲྀΕ ౉͞ΕͨSQL͕ Ұ൪ߴ଎ʹ࣮ߦ͞ΕΔͰ͋Ζ͏࣮ߦܭըΛ ϓϥϯφ͕ܾΊͯɺ ͦͷ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦͯ݁͠ՌΛฦ͢

࣮ߦܭը͸Ͳ͏΍ܾͬͯ·Δͷ͔ʁ 4 DBͰ͸ ʮ͜ͷςʔϒϧʹ͸͜Μͳσʔλ͕ೖͬͯΔʯͱ͍ ͏౷ܭ৘ใΛ࣋ͬͯΔɻ 4 ϓϥϯφ͸ ౷ܭ৘ใΛಡΜͰ ʰ͜ͷςʔϒϧ͸͜ͷΑ͏ͳσʔλ͕ೖͬͯΔ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷଎ͩΖ͏ʱ ͱ࣮ߦܭըΛܾΊΔɻ

SQLνϡʔχϯάͷجૅ 4 ΑΓʮείΞ͕ग़Δʯ࣮ߦܭըΛ ϓϥϯφ͕૊ΈཱͯΔࣄ͕ग़དྷΔΑ͏ʹௐ੔͢Δɻ => ͦͷҝʹ͸ ࣮ࡍʹϓϥϯφ͕ ͲΜͳ࣮ߦܭըΛཱ͔ͯͨݟͯνϡʔχϯά͢Δඞཁ͕͋Δ

̏ɽSQLνϡʔχϯάͷجૅ

SQLνϡʔχϯάͷجૅ લষʹͯɺϓϥϯφཱ͕࣮ͯͨߦܭըΛ஌Γ ͦͷ࣮ߦܭըΛΑΓ଎౓ͷग़Δ࣮ߦܭըʹ͢ΔΑ͏ʹ վળ͢Δͷ͕SQL଎౓νϡʔχϯάͷجૅɺͱ͍͏࿩Λͨ͠

࣮ߦܭըͷݟํ 4 EXPLAIN ͱ͍͏ΩʔϫʔυΛSQLจͷઌ಄ʹ෇͚Δɻ 4 ࣮ߦܭը͕ʮਓʹ෼͔ΔΑ͏ͳܗͰʯฦͬͯ͘Δɻ ྫɿ EXPLAIN SELECT ϝʔϧΞυϨε FROM ސ٬৘ใ WHERE ໊લ = 'ߴڮ';

ͱ͸͍͑ɺݴͬͯ΋ܭը

࣮ߦܭըͷݟํ 4 EXPLAIN ANALYZE ͱ͍͏ΩʔϫʔυΛ SQLจͷઌ಄ʹ෇͚Δɻ 4 ࣮ߦܭըͱ࣮ߦʹ͔͔͕ͬͨ࣌ؒฦͬͯ͘Δɻ ྫɿ EXPLAIN ANALYZE SELECT ϝʔϧΞυϨε FROM ސ٬৘ใ WHERE ໊લ = 'ߴڮ';

σϞ Part1

࣮ߦܭըͷྫ osc_demo=# EXPLAIN ANALYZE SELECT * FROM customers WHERE name = 'percy.wolff'; QUERY PLAN ---------------------------------------------------------------------- Gather (cost=1000.00..10172.63 rows=3 width=110) (actual time=170.646..174.786 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on customers (cost=0.00..9172.33 rows=1 width=110) (actual time=134.860..136.997 rows=0 loops=3) Filter: ((name)::text = 'percy.wolff'::text) Rows Removed by Filter: 133333 Planning Time: 2.530 ms Execution Time: 174.869 ms (8 rows)

࣮ߦܭըͷݟํ ಛʹॏཁͳՕॴ͸ೋ఺ Gather (cost=1000.00..10172.63 rows=3 width=110) (actual time=170.646..174.786 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on customers (cost=0.00..9172.33 rows=1 width=110) (actual time=134.860..136.997 rows=0 loops=3) Filter: ((name)::text = 'percy.wolff'::text) Rows Removed by Filter: 133333 Planning Time: 2.530 ms Execution Time: 174.869 ms (8 rows)

࣮ߦܭըͷݟํ ಛʹॏཁͳՕॴ͸ೋ఺ Gather (cost=1000.00..10172.63 rows=3 width=110) (actual time=170.646..174.786 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> ʲParallel Seq Scan on customersʳ (cost=0.00..9172.33 rows=1 width=110) (actual time=134.860..136.997 rows=0 loops=3) Filter: ((name)::text = 'percy.wolff'::text) Rows Removed by Filter: 133333 Planning Time: 2.530 ms Execution Time: ʲ174.869 msʳ (8 rows)

࣮ߦܭըͷݟํ 4 Parallel Seq Scan on customers 4 Execution Time: 174.869 ms

࣮ߦܭըͷݟํ Parallel Seq Scan on customers 4 ʮParallelʯ + ʮSeq Scanʯ + ʮon customersʯ

Seq Scan ͱ͸ 4 ೔ຊޠͩͱʮॱ࣍ݕࡧʯ 4 ߟ͑ํ͸ඇৗʹγϯϓϧͰDBʹ֨ೲ͞Εͨ ʮ͢΂ͯͷσʔλΛॱʹݕࡧ͢Δʯݕࡧํ๏ ʮSeq Scanʯ + ʮon customersʯ ͱॻ͔Ε͍ͯΔࣄ͔Β customers ςʔϒϧͷத਎Λ ॱ൪ʹશͯݟ͍ͯͬͨ ͱ͍͏ࣄ͕෼͔Δɻ

Parallel ͱ͸ 4 PostgreSQLʹ͋ΔػೳͷҰͭɻ 4 σʔλϕʔεͷ໰͍߹ΘͤΛෳ਺ͷϫʔΧʔʹΑͬͯ ฒྻʹݕࡧ͢Δػೳɻ ʮParallelʯ ͱॻ͔Ε͍ͯΔࣄ͔Β ͜ͷSQL͸ ςʔϒϧͷσʔλΛฒྻʹݕࡧͨ͠ࣄ͕෼͔Δɻ

࣮ߦܭըͷݟํ Execution Time: 174.869 ms 4 ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦ͔͔ͯͬͨ࣌ؒ͠ɻ

Ҏ্Λ౿·͑ͯɺ࠶౓ ࣮ߦܭըΛݟͯΈΑ͏ɻ ---------------------------------------------------- Gather (cost=1000.00..10172.63 rows=3 width=110) (actual time=170.646..174.786 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on customers (cost=0.00..9172.33 rows=1 width=110) (actual time=134.860..136.997 rows=0 loops=3) Filter: ((name)::text = 'percy.wolff'::text) Rows Removed by Filter: 133333 Planning Time: 2.530 ms Execution Time: 174.869 ms (8 rows)

࣮ߦܭը͔ΒνϡʔχϯάϙΠϯτΛ୳͢ 4 ஗ͦ͏ͳॴແ͍͔ɻ 4 Seq Scan ʹண໨ => ސ٬σʔλ͸αʔϏε͕੒௕͢Ε͹͢Δ΄Ͳ૿͑Δɻ => Seq Scan͸σʔλΛશͯݕࡧ͢Δݕࡧํ๏ɻ => αʔϏεͷ੒௕ʹ൐͍ɺ͜ͷSQL͸ͲΜͲΜ஗͘ͳΔɻ => ͜͜ʹߴ଎Խͷ؊͕͋Γͦ͏ɻ

σϞ Part2

IndexͰߴ଎Խ͍ͯ͘͠ CREATE INDEX customers_name_idx ON customers (name); 4 customers ͷ name ʹ customemrs_name_idxͱ͍͏໊લͷ ΠϯσοΫεΛ࡞Δ

4 ࣮ߦ݁Ռ ------------------------------------------------------- Bitmap Heap Scan on customers (cost=4.45..16.30 rows=3 width=110) (actual time=2.301..2.319 rows=1 loops=1) Recheck Cond: ((name)::text = 'percy.wolff'::text) Heap Blocks: exact=1 -> Bitmap Index Scan on customers_name_idx (cost=0.00..4.45 rows=3 width=0) (actual time=2.275..2.284 rows=1 loops=1) Index Cond: ((name)::text = 'percy.wolff'::text) Planning Time: 6.774 ms Execution Time: 2.918 ms (7 rows)

Indexߏஙલͱߏஙޙ ߲໨ Indexߏஙલ Indexߏஙޙ εΩϟϯํ๏ Parallel Seq Scan Bitmap Heap Scan ࣮ߦ࣌ؒ 174.869 ms 2.918 ms

------------------------------------------------------- Bitmap Heap Scan on customers (cost=4.45..16.30 rows=3 width=110) (actual time=2.301..2.319 rows=1 loops=1) Recheck Cond: ((name)::text = 'percy.wolff'::text) Heap Blocks: exact=1 -> Bitmap Index Scan on customers_name_idx (cost=0.00..4.45 rows=3 width=0) (actual time=2.275..2.284 rows=1 loops=1) Index Cond: ((name)::text = 'percy.wolff'::text) Planning Time: 6.774 ms Execution Time: 2.918 ms (7 rows)

̐ɽ·ͱΊ

̐ɽ·ͱΊ 4 ࠓճ͸SQLͷ࣮ߦͷ࢓૊Έͱ࣮ߦܭըͷݟํɺ ߴ଎Խʹ͋ͨͬͯߟ͑Δॴͱ࣮ࡍͷߴ଎Խख๏ʹ͍ͭͯ ͓࿩ͤͯ͞௖͖·ͨ͠ɻ 4 ࠓճͷ࿩͸ඇৗʹ؆୯ͳσϞͰIndex͕͙͢ʹޮՌ͕͋Δ Α͏ͳܗͰͨ͠ɻ ࣮຿Ͱ͍͏ͱ׳Ε͍ͯͳ͍ͱ୯७ʹߴ଎Խग़དྷΔΑ͏ͳ IndexΛషΔͷ͸೉͔ͬͨ͠Γ͠·͢ɻ

̐ɽ·ͱΊ 4 ͱ͸͍͑ɺࢼߦࡨޡ͸ඇৗʹେࣄͰ͢ɻ 4 ಛघͳ৔߹Λআ͍ͯɺIndex͸࡞ͬͯμϝͩͬͨΒݩʹ໭ͤ ͹ྑ͍ͷͰ͢ɻ 4 جૅΛԡ͑ͯ͞ΑΓ༗ޮͳIndexΛ࡞ΔΑ͏ʹͳΓͭͭɺ ࣌ʹ͸ΞάϨογϒʹSQLͱ޲͖߹͍͖ͬͯ·͠ΐ͏ʂ

͝ਗ਼੅͋Γ͕ͱ͏͍͟͝ ·ͨ͠ɻ