実行計画から学ぶ PostgreSQL の内部動作とクエリ最適化 / Learn PostgreSQL from Explain

2019年 Wantedly 新人研修の資料です。

SQL に対する Explain 結果をベースに、SQL 実行時の PostgreSQL の内部動作や、それを踏まえた上でのクエリ最適化について話しました。

Transcript

1. ©2019 Wantedly, Inc.
   ࣮ߦܭը͔ΒֶͿ
   1PTUHSF42-ͷ಺෦ಈ࡞ͱ
   ΫΤϦ࠷దԽ
   15.May.2019 - Nao Minami (@south37) Wantedly, Inc.
   New Joiner Training 2019
   

   View Slide

2. ©2019 Wantedly, Inc.
   ͸͡Ίʹ
   

   View Slide

3. ©2019 Wantedly, Inc.
   42-ͷ࣮ߦ࣌ʹ3%#ͰߦΘΕΔ಺෦ಈ࡞Λ஌Δ
   &YQMBJOͷಡΈํɺద੾ͳJOEFYͷઃఆΛ஌Δ
   ࠓ೔ͷΰʔϧ
   

   View Slide

4. ©2019 Wantedly, Inc.
   ࠓ೔ѻ͏ςʔϒϧͷεΩʔϚ
   Table "jobs"
   Column | Type | Collation | Nullable | Default
   -------------+------------+-----------+----------+---------------------------------
   id | bigint | | not null | nextval('jobs_id_seq'::regclass)
   company_id | integer | | |
   Indexes:
   "jobs_pkey" PRIMARY KEY, btree (id)
   "index_jobs_on_company_id" btree (company_id)
   Table "companies"
   Column | Type | Collation | Nullable | Default
   -------------+----------------------+-----------+----------+--------------------------------
   id | bigint | | not null | nextval('jobs_id_seq'::regclass)
   country | character varying(2) | | not null | 'JP'::character varying
   Indexes:
   "companies_pkey" PRIMARY KEY, btree (id)
   "index_companies_on_country" btree (country)
   

   View Slide

5. ©2019 Wantedly, Inc.
   ࠓ೔಺෦ಈ࡞Λཧղ͢Δ42-
   SELECT
   companies.country,
   COUNT(jobs.id)
   FROM
   jobs
   INNER JOIN companies ON companies.id = jobs.company_id
   WHERE
   companies.id < 1000
   GROUP BY
   companies.country
   

   View Slide

6. ©2019 Wantedly, Inc.
   ·ͣ͸42-Λ&YQMBJOͯ͠ΈΑ͏
   

   View Slide

7. ©2019 Wantedly, Inc.
   EXPLAIN
   SELECT
   companies.country,
   COUNT(jobs.id)
   FROM
   jobs
   INNER JOIN companies ON companies.id = jobs.company_id
   WHERE
   companies.id < 1000
   GROUP BY
   companies.country
   1PTUHSF42-ͷ&YQMBJO
   

   View Slide

8. ©2019 Wantedly, Inc.
   1PTUHSF42-ͷ&YQMBJO݁ՌΛݟͯΈΔ
   QUERY PLAN
   ------------------------------------------------------------------------------------------------------
   HashAggregate (cost=1213.79..1220.12 rows=634 width=16)
   -> Hash Join (cost=54.28..1188.79 rows=5000 width=16)
   Hash Cond: (jobs.company_id = companies.id)
   -> Seq Scan on jobs (cost=0.00..897.00 rows=50000 width=8)
   -> Hash (cost=41.78..41.78 rows=1000 width=16)
   -> Index Scan using companies_pkey on companies (cost=0.29..41.78 rows=1000 width=16)
   Index Cond: (id < 1000)
   ࣮ߦܭը͸πϦʔߏ଄ )BTI"HHSFHBUF
   )BTI+PJO
   4FR4DBO
   )BTI
   *OEFY4DBO
   

   View Slide

9. ©2019 Wantedly, Inc.
   ίετͷݟํ
   Seq Scan on jobs (cost=0.00..897.00 rows=50000 width=8)
   ॳظԽίετ ૯ίετ Ϩίʔυ਺
   Ϩίʔυ͋ͨΓͷσʔλαΠζ όΠτ
   
   ૯ίετॳظԽίετ
   Ϩίʔυ਺ʷϨίʔυ͋ͨΓͷऔಘίετ
   
   

   View Slide

10. ©2019 Wantedly, Inc.
    ༨ஊ&91-"*/"/"-:4&ͩͱ࣮ࡍʹ࣮ߦ
    EXPLAIN
    ANALYSE
    SELECT
    companies.country,
    COUNT(jobs.id)
    FROM
    jobs
    INNER JOIN companies ON companies.id = jobs.company_id
    WHERE
    companies.id < 1000
    GROUP BY
    companies.country
    

    View Slide

11. ©2019 Wantedly, Inc.
    ༨ஊ&91-"*/"/"-:4&ͩͱ࣮ࡍʹ࣮ߦ
    HashAggregate (cost=1213.79..1220.12 rows=634 width=16) (actual time=20.290..20.465 rows=950 loops=1)
    -> Hash Join (cost=54.28..1188.79 rows=5000 width=16) (actual time=1.018..18.102 rows=4983 loops=1)
    Hash Cond:(jobs.company_id = companies.id)
    -> Seq Scan on jobs (cost=0.00..897.00 rows=50000 width=8) (actual time=0.009..6.352 rows=50000 loops=1)
    -> Hash (cost=41.78..41.78 rows=1000 width=16) (actual time=0.995..0.995 rows=999 loops=1)
    Buckets: 1024 Batches: 1 Memory Usage: 51kB
    -> Index Scan using companies_pkey on companies (cost=0.29..41.78 rows=1000 width=16) (actual
    time=0.022..0.527 rows=999 loops=1)
    

    View Slide

12. ©2019 Wantedly, Inc.
    &91-"*/ʹ͍ͭͯ
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDTRMFYQMBJOIUNM
    ΑΓৄ͘͠஌Γ͍ͨํ͸ެࣜυΩϡϝϯτΛࢀর
    

    View Slide

13. ©2019 Wantedly, Inc.
    42-࣮ߦͷ'JSTU4UFQσʔλऔಘ
    

    View Slide

14. ©2019 Wantedly, Inc.
    42-࣮ߦͷ'JSTU4UFQσʔλऔಘ
    QUERY PLAN
    ------------------------------------------------------------------------------------------------------
    HashAggregate (cost=1213.79..1220.12 rows=634 width=16)
    -> Hash Join (cost=54.28..1188.79 rows=5000 width=16)
    Hash Cond: (jobs.company_id = companies.id)
    -> Seq Scan on jobs (cost=0.00..897.00 rows=50000 width=8)
    -> Hash (cost=41.78..41.78 rows=1000 width=16)
    -> Index Scan using companies_pkey on companies (cost=0.29..41.78 rows=1000 width=16)
    Index Cond: (id < 1000)
    )BTI"HHSFHBUF
    )BTI+PJO
    4FR4DBO
    )BTI
    *OEFY4DBO
    

    View Slide

15. ©2019 Wantedly, Inc.
    *OEFY4DBOJOEFYΛར༻ͨ͠σʔλऔಘ
    )BTI"HHSFHBUF
    )BTI+PJO
    4FR4DBO
    )BTI
    *OEFY4DBO
    ·ͣ͸JOEFY4DBOʹ஫໨͢Δ
    

    View Slide

16. ©2019 Wantedly, Inc.
    *OEFYͷ࢓૊Έ
    #USFFJOEFY
    w ϊʔυ͋ͨΓ਺ඦཁૉ
    w ཁૉͱͯ͠ɺஈͰສ݅֨ೲ
    ߴ଎ͳσʔλऔಘ
    IUUQTXXXWFSUBCFMPDPNCMPHUFDIOJDBMBSUJDMFTBMMBCPVUJOEFYFTQBSUNZTRMJOEFYTUSVDUVSFBOEQFSGPSNBODF
    ΑΓҾ༻
    

    View Slide

17. ©2019 Wantedly, Inc.
    *OEFYͷར༻ʹΑΔߴ଎Խ
    EXPLAIN SELECT jobs.* FROM jobs WHERE jobs.id = 1
    QUERY PLAN
    -----------------------------------------------------------------------
    Index Scan using jobs_pkey on jobs (cost=0.29..8.31 rows=1 width=28)
    Index Cond: (id = 1)
    JOEFY͋Γ
    EXPLAIN SELECT jobs.* FROM jobs WHERE jobs.id = 1
    QUERY PLAN
    -----------------------------------------------------------------------
    Seq Scan on jobs (cost=0.00..1022.00 rows=1 width=28)
    Filter: (id = 1)
    JOEFYແ͠
    JOEFYʹΑͬͯίετΛ཈͑Δࣄ͕Ͱ͖Δ
    

    View Slide

18. ©2019 Wantedly, Inc.
    ҰํɺJOEFYͷར༻ʹ͸ؾΛ͚ͭΔϙΠϯτ΋͋Δ
    

    View Slide

19. ©2019 Wantedly, Inc.
    *OEFYόουύλʔϯJOEFYΛ࡞੒ͨ͠ΧϥϜʹԋࢉ
    EXPLAIN SELECT profiles.* FROM profiles WHERE (lower(email) = 'minami@wantedly.com') LIMIT 1
    QUERY PLAN
    -----------------------------------------------------------------------
    Limit (cost=0.00..5.08 rows=1 width=54)
    -> Seq Scan on profiles (cost=0.00..254.00 rows=50 width=54)
    Filter: (lower(email) = 'minami@wantedly.com'::text)
    #USFFJOEFY͸LFZͷൺֱͰTPSUͯ͠ΔͷͰɺԋࢉ͕ߦΘΕΔͱར༻Ͱ͖ͳ͍
    ʮΫΤϦॻ͖׵͑ʯPSʮ*OEFYFTPO&YQSFTTJPOΛར༻ʯ
    

    View Slide

20. ©2019 Wantedly, Inc.
    *OEFYόουύλʔϯߜΓࠐΈ৚݅ͷΏΔ͍8)&3&
    EXPLAIN SELECT profiles.* FROM profiles WHERE profiles.gender = “female”
    QUERY PLAN
    -----------------------------------------------------------------------
    Seq Scan on profiles (cost=0.00..229.00 rows=5038 width=54)
    Filter: (gender = 'female'::text)
    QSPpMFTHFOEFSͷ෼෍͕ҎԼͩͱɺ*OEFY͸ར༻͞Εͳ͍
    σϑΥϧτͩͱɺ෼ͷҎԼʹߜΓࠐ·ΕΔඞཁ͋Γ
    male female
    

    View Slide

21. ©2019 Wantedly, Inc.
    )%%΁ͷϥϯμ
    ϜΞΫηεͱ
    γʔέϯγϟϧΞΫηεͷ଎౓͕ࠩݪҼ
    4FR4DBO *OEFY4DBO
    3BOEPN"DDFTT
    
    ̐
    
    ཁૉ୯Ґͩͱߴίετ
    ͳͥߜΓࠐΈ৚͕݅؇͍ͱJOEFY͕࢖ΘΕͳ͍ͷ͔ʁ
    

    View Slide

22. ©2019 Wantedly, Inc.
    EXPLAIN SELECT profiles.* FROM profiles WHERE profiles.gender = “female”
    QUERY PLAN
    -----------------------------------------------------------------------
    Bitmap Heap Scan on boxer_profiles (cost=28.08..114.66 rows=1006 width=25)
    Recheck Cond: (gender = ‘female'::text)
    -> Bitmap Index Scan on index_boxer_profiles_on_gender (cost=0.00..27.83 rows=1006 width=0)
    Index Cond: (gender = 'female'::text)
    QSPpMFTHFOEFSͷ෼෍͕ҎԼͳΒɺ*OEFY͸ར༻͞ΕΔ
    male female
    ϕετϓϥΫςΟε8)&3&ͰͪΌΜͱߜΓࠐ·ΕΔΑ͏ʹ͢Δ
    σʔλͷ෼෍ʮ౷ܭ৘ใʯ͕େࣄ
    

    View Slide

23. ©2019 Wantedly, Inc.
    ༨ஊ1PTUHSF42-಺ͰͷσʔλϨΠΞ΢τ
    ৄ͘͠஌Γ͍ͨํ͸
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDTUPSBHFIUNM
    ·ͨ͸ʮ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇʯΛࢀর
    ʮ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ
    ٕज़ධ࿦ࣾʯΑΓҾ༻
    

    View Slide

24. ©2019 Wantedly, Inc.
    *OEFYʹ͸σϝϦοτ΋͋ΔࣄΛ஌Δ
    #USFFJOEFYߋ৽ʹίετ͕͔͔Δ
    w Ϩίʔυͷߋ৽ʹ͕͔͔࣌ؒΔΑ͏ʹͳΔ
    w %#ͷ࢖༻͢ΔσʔλαΠζ͕૿͑Δ
    )05͕ޮ͔ͳ͍
    w )05͸1PTUHSF42-ͷΧϥϜͷߋ৽Λૣ͘͢Δ࢓૊ΈʢඞཁͳՕॴͷΈΛߋ৽͢Δʣ
    w ৄ͘͠͸IUUQMFUTQPTUHSFTRMKQEPDVNFOUTUVUPSJBMIPU@ࢀর
    

    View Slide

25. ©2019 Wantedly, Inc.
    1PTUHSF42-ʹ͸༷ʑͳ*OEFY͕͋Δ
    6OJRVF*OEFY
    .VMUJDPMVNO*OEFY
    *OEFYFTPO&YQSFTTJPOT
    1BSUJBM*OEFYFT
    

    View Slide

26. ©2019 Wantedly, Inc.
    1PTUHSF42-ʹ͸༷ʑͳ*OEFY͕͋Δ
    6OJRVF*OEFY
    .VMUJDPMVNO*OEFY
    *OEFYFTPO&YQSFTTJPOT
    1BSUJBM*OEFYFT
    

    View Slide

27. ©2019 Wantedly, Inc.
    .VMUJDPMVNO*OEFY
    IUUQTVTFUIFJOEFYMVLFDPNTRMXIFSFDMBVTFUIFFRVBMTPQFSBUPSDPODBUFOBUFELFZTΑΓҾ༻
    lෳ਺ΧϥϜʹରͯ͠ͷJOEFYzΧϥϜͷ૊Έ߹Θͤͱͯ͠ιʔτ
    

    View Slide

28. ©2019 Wantedly, Inc.
    .VMUJDPMVNO*OEFYͷར༻ʹΑΔߴ଎Խ
    EXPLAIN SELECT tourist_spots.* FROM tourist_spots WHERE tourist_spots.country = “japan" AND
    tourist_spots.city = “tokyo”
    QUERY PLAN
    -----------------------------------------------------------------------
    Index Scan using index_tourist_spots_on_country_and_city on tourist_spots (cost=0.42..8.44 rows=1 width=52)
    Index Cond: ((country = 'japan'::text) AND (city = 'tokyo'::text))
    .VMUJDPMVNOJOEFY͋Γ
    EXPLAIN SELECT jobs.* FROM jobs WHERE jobs.id = 1
    QUERY PLAN
    -----------------------------------------------------------------------
    Index Scan using index_tourist_spot_without_multiple_indices_on_city on tourist_spot_without_multiple_indices
    (cost=0.42..8.44 rows=1 width=52)
    Index Cond: (city = ‘tokyo’::text)
    Filter: (country = 'japan'::text)
    .VMUJDPMVNOJOEFYແ͠
    

    View Slide

29. ©2019 Wantedly, Inc.
    .VMUJDPMVNO*OEFYͷར༻ʹΑΔߴ଎Խ
    EXPLAIN SELECT "tourist_spots".* FROM "tourist_spots" WHERE "tourist_spots"."country
    QUERY PLAN
    -----------------------------------------------------------------------
    Bitmap Heap Scan on tourist_spots (cost=4.50..41.67 rows=10 width=52)
    Recheck Cond: (country = ‘japan’::text)
    -> Bitmap Index Scan on index_tourist_spots_on_country_and_city (cost=0.00..4.49 rows=10 width=0)
    Index Cond: (country = 'japan'::text)
    .VMUJDPMVNO*OEFY͸ઌ಄ͷཁૉͷJOEFYͱͯ͠΋ಈ࡞͢Δ
    ΑΓৄࡉΛ஌Γ͍ͨํ͸
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDJOEFYFTNVMUJDPMVNOIUNM
    

    View Slide

30. ©2019 Wantedly, Inc.
    *OEFYFTPO&YQSFTTJPOT
    ؔ਺ͳͲͷฦΓ஋ΛLFZͱͯ͠JOEFYΛ࡞Δࣄ͕Ͱ͖Δ
    Table "profiles"
    Column | Type | Collation | Nullable | Default
    ---------+------------------------+-----------+----------+---------------------------------
    id | bigint | | not null | nextval('jobs_id_seq'::regclass)
    email | character varying(255) | | not null | ''::character varying
    Indexes:
    "profiles_pkey" PRIMARY KEY, btree (id)
    "index_profiles_on_lower_email" btree (lower(email::text))
    

    View Slide

31. ©2019 Wantedly, Inc.
    *OEFYFTPO&YQSFTTJPOT
    MPXFS FNBJM
    Λར༻ͨ͠8)&3&ͰJOEFY͕ޮ͘
    EXPLAIN SELECT profiles.* FROM profiles WHERE (lower(email) = 'minami@wantedly.com')
    QUERY PLAN
    -----------------------------------------------------------------------
    Index Scan using index_profiles_on_lower_email on profiles (cost=0.29..8.30 rows=1 width=48)
    Index Cond: (lower(email) = 'minami@wantedly.com'::text)
    ৄࡉ͸IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDJOEFYFTFYQSFTTJPOBMIUNMࢀর
    

    View Slide

32. ©2019 Wantedly, Inc.
    42-࣮ߦͷ/FYU4UFQ+0*/
    

    View Slide

33. ©2019 Wantedly, Inc.
    42-࣮ߦͷ/FYU4UFQ+0*/
    QUERY PLAN
    ------------------------------------------------------------------------------------------------------
    HashAggregate (cost=1213.79..1220.12 rows=634 width=16)
    -> Hash Join (cost=54.28..1188.79 rows=5000 width=16)
    Hash Cond: (jobs.company_id = companies.id)
    -> Seq Scan on jobs (cost=0.00..897.00 rows=50000 width=8)
    -> Hash (cost=41.78..41.78 rows=1000 width=16)
    -> Index Scan using companies_pkey on companies (cost=0.29..41.78 rows=1000 width=16)
    Index Cond: (id < 1000)
    )BTI"HHSFHBUF
    )BTI+PJO
    4FR4DBO
    )BTI
    *OEFY4DBO
    

    View Slide

34. ©2019 Wantedly, Inc.
    1PTUHSF42-ͷ+0*/ΞϧΰϦζϜ
    ͭͷ+0*/ΞϧΰϦζϜ͕ଘࡏ
    w /FTUFE-PPQ+PJO
    w )BTI+PJO
    w .FSHF+PJO
    ࠷దͳΞϧΰϦζϜ͕ࣗಈతʹબ୒͞ΕΔ
    w JOEFYͷ༗ແ΍౷ܭ৘ใʢσʔλͷྔɾ෼෍ʣʹґଘ
    

    View Slide

35. ©2019 Wantedly, Inc.
    /FTUFE-PPQ+PJO
    ,z
    IUUQTXXXTMJEFTIBSFOFUBEPSFQVNQQPTUHSFTRMQFSGPSNBODFUVOJOHΑΓҾ༻
    ॏϧʔϓͰ͢΂ͯͷ૊Έ߹ΘͤΛࢼ͢
    w 0 /.
    ʜۃΊͯ஗͍
    w Ϩίʔυ਺͕গͳ͚Ε͹ߴ଎
    w 5BCMFʹJOEFYΛషΕ͹ɺ͋Δఔ౓ߴ଎Խ͕Մೳ
    Ϩίʔυ਺/ Ϩίʔυ਺.
    

    View Slide

36. ©2019 Wantedly, Inc.
    5BCMFʹରͯ͠ɺҰ౓ϑϧεΩϟϯͯ͠
    )BTI5BCMFΛ࡞੒
    w 0 /.
    
    w 5BCMF͕શͯ.FNPSZʹࡌΔ͘Β͍খ͍͞αΠζͰ͋Δ
    ࣄ͕৚݅
    w .FNPSZ্Ͱ5BCMFͷϨίʔυΛ୳ࡧͰ͖ΔͷͰߴ଎
    )BTI+PJO
    ,z
    IUUQTXXXTMJEFTIBSFOFU.JLJ4IJNPHBJQPTUHSFTRMFYQMBJOΑΓҾ༻
    5BCMF
    Ϩίʔυ਺/
    5BCMF
    Ϩίʔυ਺.
    )BTI5BCMF
    

    View Slide

37. ©2019 Wantedly, Inc.
    ιʔτࡁΈͷ5BCMFͱ5BCMFʹର͠
    ͯɺҰ౓͚ͩϑϧεΩϟϯ
    w 0 /.
    
    w 5BCMF 5BCMFʹࣄલʹJOEFYΛషͬͯ͋Δࣄ͕৚݅
    w ςʔϒϧαΠζ͕େ͖͍࣌ʹ͸࠷΋ߴ଎
    .FSHF+PJO
    ,z
    IUUQTXXXTMJEFTIBSFOFU.JLJ4IJNPHBJQPTUHSFTRMFYQMBJOΑΓҾ༻
    

    View Slide

38. ©2019 Wantedly, Inc.
    JOEFY͕͋ͬͯ΋+0*/͕஗͍έʔε͸ଘࡏ͢Δ
    

    View Slide

39. ©2019 Wantedly, Inc.
    JOEFY͕͋ͬͯ΋+0*/͕஗͍έʔεͱ͸ʁ
    ͭͷ+0*/ΞϧΰϦζϜ͸ɺͲΜͳʹߴ଎Խͯ͠΋0 /
    Ҏ্
    w /FTUFE-PPQ+PJOʜ0 /.
    
    w )BTI+PJOʜ0 /.
    
    w .FSHF+PJOʜ0 /.
    
    /͕େ͖͍ͱɺ+0*/͸ඞͣ஗͘ͳΔ
    

    View Slide

40. ©2019 Wantedly, Inc.
    JOEFY͕͋ͬͯ΋+0*/͕஗͘ͳΔέʔε۩ମྫ
    EXPLAIN SELECT COUNT(*) FROM users INNER JOIN profiles ON profiles.user_id = users.id
    QUERY PLAN
    -----------------------------------------------------------------------
    Aggregate (cost=23288.72..23288.73 rows=1 width=0)
    -> Hash Join (cost=354.30..23261.80 rows=10769 width=0)
    Hash Cond: (users.id = profiles.user_id)
    -> Seq Scan on users (cost=0.00..11441.64 rows=698964 width=4)
    -> Hash (cost=219.69..219.69 rows=10769 width=4)
    -> Seq Scan on profiles (cost=0.00..219.69 rows=10769 width=4)
    

    View Slide

41. ©2019 Wantedly, Inc.
    ରࡦՄೳͰ͋Ε͹ࣄલʹߜΓࠐΜͰςʔϒϧαΠζ/ΛݮΒ͢
    EXPLAIN SELECT COUNT(*) FROM users INNER JOIN profiles ON profiles.user_id = users.id
    WHERE users.registered = “t”
    QUERY PLAN
    -----------------------------------------------------------------------
    Aggregate (cost=8131.17..8131.18 rows=1 width=0)
    -> Hash Join (cost=1850.65..8128.51 rows=1065 width=0)
    Hash Cond: (users.id = profiles.user_id)
    -> Bitmap Heap Scan on users (cost=1496.35..6639.86 rows=69151 width=4)
    Filter: registered
    -> Bitmap Index Scan on index_users_on_registered (cost=0.00..1479.06 rows=69151 width=0
    Index Cond: (registered = true)
    -> Hash (cost=219.69..219.69 rows=10769 width=4)
    -> Seq Scan on profiles (cost=0.00..219.69 rows=10769 width=4)
    

    View Slide

42. ©2019 Wantedly, Inc.
    42-࣮ߦͷ-BTU4UFQσʔλू໿ "HHSFHBUF
    
    

    View Slide

43. ©2019 Wantedly, Inc.
    42-࣮ߦͷ-BTU4UFQσʔλू໿ "HHSFHBUF
    
    QUERY PLAN
    ------------------------------------------------------------------------------------------------------
    HashAggregate (cost=1213.79..1220.12 rows=634 width=16)
    -> Hash Join (cost=54.28..1188.79 rows=5000 width=16)
    Hash Cond: (jobs.company_id = companies.id)
    -> Seq Scan on jobs (cost=0.00..897.00 rows=50000 width=8)
    -> Hash (cost=41.78..41.78 rows=1000 width=16)
    -> Index Scan using companies_pkey on companies (cost=0.29..41.78 rows=1000 width=16)
    Index Cond: (id < 1000)
    )BTI"HHSFHBUF
    )BTI+PJO
    4FR4DBO
    )BTI
    *OEFY4DBO
    

    View Slide

44. ©2019 Wantedly, Inc.
    1PTUHSF42-ͷ"HHSFHBUFΞϧΰϦζϜ
    ͭͷ"HHSFHBUFΞϧΰϦζϜ͕ଘࡏ
    w (SPVQ"HHSFHBUF
    w )BTI"HHSFHBUF
    

    View Slide

45. ©2019 Wantedly, Inc.
    (SPVQ"HHSFHBUF
    w ೖྗ͞ΕͨσʔλΛ(SPVQLFZͰιʔτޙɺ֤άϧʔϓΛॱ࣍ॲཧ͢ΔΞϧΰϦζϜ
    w JOEFY͕͋ͬͯTPSUࡁΈͰ͋Ε͹ɺύΠϓϥΠϯԽ΋Մೳ
    )BTI"HHSFHBUF
    w (SPVQLFZΛLFZͱ͢ΔҰ࣌తͳ)BTI5BCMFΛ࡞੒͠ɺ֤άϧʔϓΛॲཧ͢ΔΞϧΰϦζϜ
    w σʔλαΠζ͕NFNPSZʹࡌΔ͘Β͍খ͚͞Ε͹͜Ε͕બ͹ΕΔ
    1PTUHSF42-ͷͭͷ"HHSFHBUFΞϧΰϦζϜ
    

    View Slide

46. ©2019 Wantedly, Inc.
    ͦͷଞͷ1PTUHSF42-ͷ࣮ߦεςοϓ
    

    View Slide

47. ©2019 Wantedly, Inc.
    4PSUͱ-JNJU
    03%&3#:Λࢦఆ͢Δͱɺ4PSUॲཧ͕ߦΘΕΔ
    w JOEFY͕͋Ε͹͢Ͱʹ4PSUࡁΈͰ͋ΔͨΊɺ4PSUͷίετ͸͔͔Βͳ͍
    w JOEFY͕ແ͍৔߹ɺ4PSUΛߦ͏ඞཁ͕͋Δɻ-JNJUͰߴʑ਺݅ཉ͍͠৔߹Ͱ΋ɺςʔϒϧશମ
    ͷ4DBOͱ4PSU͕૸ΔɻڊେͳσʔλͰ͸%JTLTPSU͕ൃੜ͠ɺۃΊͯ஗͘ͳΔɻ
    EXPLAIN SELECT page_view_logs.* FROM page_view_logs ORDER BY viewed_at ASC LIMIT 20
    QUERY PLAN
    -----------------------------------------------------------------------
    Limit (cost=22026.31..22026.36 rows=20 width=28)
    -> Sort (cost=22026.31..23278.87 rows=501024 width=28)
    Sort Key: viewed_at
    -> Seq Scan on page_view_logs (cost=0.00..8694.24 rows=501024 width=28)
    

    View Slide

48. ©2019 Wantedly, Inc.
    03%&3#:ʹ͸JOEFY
    EXPLAIN SELECT page_view_logs.* FROM page_view_logs ORDER BY viewed_at ASC LIMIT 20
    QUERY PLAN
    -----------------------------------------------------------------------
    Limit (cost=0.42..1.09 rows=20 width=28)
    -> Index Scan using index_page_view_log_with_indices_on_viewed_at on
    page_view_log_with_indices (cost=0.42..16698.78 rows=501024 width=28)
    *OEFYʹΑͬͯܶతʹίετ͕Լ͕Δ
    w ҎԼͷྫͰ͸ɺ ʹݮগʂ
    

    View Slide

49. ©2019 Wantedly, Inc.
    ·ͱΊ
    

    View Slide

50. ©2019 Wantedly, Inc.
    1PTUHSF42-͸42-࣮ߦ࣌ʹ࣮ߦܭըΛཱͯΔ
    w 4DBO +PJO "HHSFHBUF 4PSUͳͲ༷ʑͳ4UFQ͕ଘࡏ
    w 4UFQ͝ͱʹɺ༷ʑͳख๏΍ΞϧΰϦζϜ͕ଘࡏ
    w બ͹ΕΔ࣮ߦܭը͸ɺJOEFYͷ༗ແ΍౷ܭ৘ใʢσʔλྔɺσʔλ෼෍ʣʹґଘ
    ద੾ͳTDIFNB JOEFY RVFSZͷબ୒͕ॏཁ
    w 5JQT8)&3& +0*/ 03%&3#: (3061#:ͷLFZʹ͸JOEFY
    w +0*/ͷલʹߜΓࠐΊΔͳΒߜΓࠐΉ
    ·ͱΊ
    

    View Slide

51. ©2019 Wantedly, Inc.
    "QQFOEJY1PTUHSF42-ͷಛ௃తͳػೳ
    

    View Slide

52. ©2019 Wantedly, Inc.
    "QQFOEJY1PTUHSF42-ͷಛ௃తͳػೳ
    8JOEPX'VODUJPOT
    +40/5ZQFT
    )TUPSF
    .BUFSJBMJ[FE7JFX
    4UPSFE1SPDFEVSF 1-QH42-
    
    

    View Slide

53. ©2019 Wantedly, Inc.
    8JOEPX'VODUJPOT
    EXPLAIN SELECT country, rank() OVER (PARTITION BY country ORDER BY id DESC) FROM companies
    QUERY PLAN
    -----------------------------------------------------------------------
    WindowAgg (cost=936.35..1155.63 rows=10964 width=16)
    -> Sort (cost=936.35..963.76 rows=10964 width=16)
    Sort Key: country, id
    1BSUJUJPO͝ͱʹɺ஋Λܭࢉ͢Δػೳ
    w ߴػೳͳू໿ؔ਺Λར༻Մೳ
    country | rank
    --------------+------
    britain | 1
    china | 1
    china | 2
    china | 3
    country_0 | 1
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDUVUPSJBMXJOEPXIUNMΛࢀর
    

    View Slide

54. ©2019 Wantedly, Inc.
    +40/5ZQFT
    INSERT INTO events (payload)
    VALUES (“{\”name\":\"test1\",\"value\":\"OK\"}")
    RETURNING “id"
    +40/σʔλΛઐ༻σʔλܕͱͯ͠อଘ͢Δػೳ
    w KTPOܕͱKTPOCܕ͕ଘࡏɻKTPOC͸CJOBSZGPSNBUͰ֨ೲɻ
    w ઐ༻ͷΦϖϨʔλʢAA΍AAʣΛར༻ͯ͠ɺ+40/pFMEͰͷ8)&3&΍4&-&$5͕Մೳ
    IUUQTXXXQPTUHSFTRMPSHEPDTDVSSFOUEBUBUZQFKTPOIUNMΛࢀর
    EXPLAIN SELECT events.* FROM events WHERE (payload->>'name' = 'test1')
    QUERY PLAN
    -----------------------------------------------------------------------
    Seq Scan on events (cost=0.00..24.85 rows=5 width=52)
    Filter: ((payload ->> 'name'::text) = 'test1'::text)
    

    View Slide

55. ©2019 Wantedly, Inc.
    )TUPSF
    LFZ WBMVFͷϖΞΛͭͷΧϥϜʹอଘՄೳͳσʔλܕ
    w +40/5ZQFTͱಉ༷ʹɺઐ༻ͷΦϖϨʔλͰWBMVFʹΞΫηεՄೳ
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDITUPSFIUNMΛࢀর
    

    View Slide

56. ©2019 Wantedly, Inc.
    .BUFSJBMJ[FE7JFX
    Ωϟογϡ͞Εͨ7JFXΛ࡞੒͢Δػೳ
    w 7JFXͷߴ଎Խ͕Մೳ
    w खಈSFGSFTI͕ඞཁ
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDTRMDSFBUFNBUFSJBMJ[FEWJFXIUNMΛࢀর
    

    View Slide

57. ©2019 Wantedly, Inc.
    4UPSFE1SPDFEVSF
    1PTUHSF42-Ͱ࣮ߦՄೳͳGVODUJPOΛఆٛ͢Δػೳ
    w SPVOEUSJQͷݮগ΍ɺQFSGPSNBODF޲্ͷҝʹར༻͞ΕΔέʔε͕͋Δ
    IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDQMQHTRMIUNMΛࢀর
    

    View Slide

58. ©2019 Wantedly, Inc.
    ࢀߟ
    

    View Slide

59. ©2019 Wantedly, Inc.
    w 1PTUHSF42-ެࣜυΩϡϝϯτ
    w IUUQTXXXQPTUHSFTRMPSHEPDTDVSSFOU
    w ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ
    ٕज़ධ࿦ࣾ
    ࢀߟ
    

    View Slide

60. ©2019 Wantedly, Inc.
    w 4VTBO:JOIUUQTVOTQMBTIDPNQIPUPT+*WCP(-FIP
    1IPUP$SFEJU
    

    View Slide