$30 off During Our Annual Pro Sale. View Details »

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

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

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

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

Nao Minami

May 15, 2019
Tweet

More Decks by Nao Minami

Other Decks in Technology

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) = '[email protected]') 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) = '[email protected]'::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) = '[email protected]')
    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) = '[email protected]'::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