Tweet
Tweet

Slide 1

Slide 1 text

ΞϓϦͷ࣮૷ʹ໾ཱͭ PostgreSQL 2019-01-20 ୈ30ճϞόΠϧΞϓϦ։ൃ΋͘΋͘ձ

Slide 2

Slide 2 text

͓͠ͳ͕͖ 1.͋͹͏ͱΈʔ 2.PostgreSQLͷ഑ྻܕ 3.·ͱΊ

Slide 3

Slide 3 text

1. ͋͹͏ͱΈʔ

Slide 4

Slide 4 text

1. ͋͹͏ͱΈʔ • ߴڮ Ұٍ • ΦϛΧϨ WebΤϯδχΞ • ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕

Slide 5

Slide 5 text

גࣜձࣾΦϛΧϨ શࠃ35,000݅ͷࠗ׆ύʔςΟΛ ܝࡌͨ͠ϙʔλϧαΠτ 30ສਓڧͷձһͷํʑʹ͝ར༻௖͍͍ͯ· ͢ɻ ࠗ׆ͱݴ͑͹ΦϛΧϨʂ \\݁ࠗ͸͍͍ͧʂʂ//

Slide 6

Slide 6 text

2. PostgreSQLͷ഑ྻܕ

Slide 7

Slide 7 text

ׂͱ͋Γ͕ͪͳΞϓϦ ͷཁ݅ ಥવͰ͕͢ɺօ͞Μ ͜ΕɺͲ͏΍࣮ͬͯ૷͠·͢ʁ

Slide 8

Slide 8 text

λά࣮૷ׂͱਏ͗͢໰୊ • ొ࿥͸؆୯ɻ • ໰୊͸ฤूͱݕࡧ͕஍ࠈɻ

Slide 9

Slide 9 text

1ΧϥϜͷςΩετܕʹcsv۠੾ΓͰೖΕΔ ߋ৽ਏ͍ɻݕࡧਏ͍ɻ (δΣΠ΢ΥʔΫ) tag1, tag2, tag3,... ͬͯ΍Δɻ tagΛ3͔ͭ͠αϙʔτ͠ͳ͍ʁ

Slide 10

Slide 10 text

ͦ͜Ͱ഑ྻܕͰ͢Αɻ CREATE TABLE blogs ( title varchar(64), content text, tags varchar(16)[] )

Slide 11

Slide 11 text

഑ྻܕͷྫ INSERT INTO blogs (title, content, tags) VALUES ('΋͘΋͘ձ', '໌೔͸΋͘΋͘ձͰ͢', '{ "ios", "android" , "PostgreSQL" }') title | contents | tags ------------+----------------------+-------------------------- ΋͘΋͘ձ | ໌೔͸΋͘΋͘ձͰ͢ | {ios,android,PostgreSQL} (1 row)

Slide 12

Slide 12 text

഑ྻܕͷϝϦοτ • ΠϯσοΫε͕ޮ͘ɻ • ݕࡧΛ͢Δҝͷԋࢉࢠ΍ϝιουͷαϙʔτ͕๛෋ɻ • औಘޙ͸֤ݴޠͷ഑ྻͱͯ͠࢖༻Ͱ͖Δ ( Djangoͱ͔͸ͦ ͏. )

Slide 13

Slide 13 text

഑ྻܕͷݕࡧʹ͍ͭͯ 'ios' λά͕͋Δ΋ͷΛݕࡧ. SELECT * FROM blogs WHERE 'ios' = ANY(tags); title | contents | tags ------------+----------------------+-------------------------- ΋͘΋͘ձ | ໌೔͸΋͘΋͘ձͰ͢ | {ios,android,PostgreSQL} (1 row) ios λά ͕͍ͭͯΔ ͔ͭ android λά͕͍ͭͯΔ Έ͍ͨͳݕࡧ΋Ͱ͖Δɻ

Slide 14

Slide 14 text

഑ྻܕͷऔಘʹ͍ͭͯ SELECT tags[1] AS tag FROM blogs; tag ---------- ios (1 row) ͦͷ΄͔ʹ΋ tags[2:3] : ഑ྻͷ 2ʙ3൪໨Λऔಘ tags[2:] : ഑ྻͷ 2൪໨Ҏ߱Λऔಘ tags[:2] : ഑ྻͷ 2൪໨ҎԼΛऔಘ ※ 0͔Β։࢝͠ͳ͍ࣄʹதࢭ

Slide 15

Slide 15 text

ͦͷଞʹ΋ָʹཁ݅Λ ຬͨͤΔ SELECT unnest(tags) , count(*) FROM blogs GROUP BY unnest(tags) ORDER BY count(*) DESC; unnest | count ------------+------- database | 2 ios | 1 PostgreSQL | 1 android | 1 (4 rows)

Slide 16

Slide 16 text

഑ྻܕͷσϝϦοτ • ֎෦Ωʔ੍໿͕షΕͳ͍ɻ => ஋ͷ੔߹ੑΛอূ͢Δͷ͕೉͍͠ɻ => λάݕࡧͳͲͷϑϦʔςΩετͷσʔλΛอ࣋͢Δͷʹ ద͍ͯ͠Δɻ

Slide 17

Slide 17 text

3. ·ͱΊ

Slide 18

Slide 18 text

3. ·ͱΊ ࠓճ͸ɺPostgreSQLͷ഑ྻܕͷ঺հΛ͠·ͨ͠ɻ ͱͯ΋ڧྗͳ࢓૊ΈͰ͕͢΋ͪΖΜϝϦοτɾσϝϦοτ͕͋Γɺ ඞͣ͠΋ۜͷ஄ؙͱ͍͏Θ͚Ͱ͸ͳ͍Ͱ͢ɻ ͳͷͰɺunnest() ΍ array_agg() Λ࢖ͬͯ ߦ <=> ഑ྻؒΛ૬ޓม׵͢ΔͳͲɺ ϝϦοτɾσϝϦοτΛڗडͰ͖ΔΑ͏ͳܗͰ ࢖͍ͬͯ͘ͷ͕ྑ͍͔ͱࢥ͍·͢ʂ

Slide 19

Slide 19 text

4. ࠂ஌ தࠃ஍ํDBษڧձ 2019-02-02 (౔) ୈ26ճ தࠃ஍ํDBษڧձ in Ԭࢁ https://dbstudychugoku.connpass.com/event/112468/ SQLνϡʔχϯά(EXPLAIN)ͷํ๏΍ύϥϝʔλνϡʔχϯάΛ औΓ্͛·͢ɻ ઈࢍLTൃදऀΛืूதͰ͢ʂʂ

Slide 20

Slide 20 text

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