Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

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

実行計画から学ぶ 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. ࠓ೔ѻ͏ςʔϒϧͷεΩʔϚ 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)
  2. ©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
  3. ©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
  4. ©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
  5. ©2019 Wantedly, Inc. ίετͷݟํ Seq Scan on jobs (cost=0.00..897.00 rows=50000

    width=8) ॳظԽίετ ૯ίετ Ϩίʔυ਺ Ϩίʔυ͋ͨΓͷσʔλαΠζ όΠτ ૯ίετॳظԽίετ   Ϩίʔυ਺ʷϨίʔυ͋ͨΓͷऔಘίετ
  6. ©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
  7. ©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)
  8. ©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
  9. ©2019 Wantedly, Inc. *OEFYͷ࢓૊Έ #USFFJOEFY w ϊʔυ͋ͨΓ਺ඦཁૉ w ཁૉͱͯ͠ɺஈͰສ݅֨ೲ ߴ଎ͳσʔλऔಘ

    IUUQTXXXWFSUBCFMPDPNCMPHUFDIOJDBMBSUJDMFTBMMBCPVUJOEFYFTQBSUNZTRMJOEFYTUSVDUVSFBOEQFSGPSNBODF ΑΓҾ༻
  10. ©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ʹΑͬͯίετΛ཈͑Δࣄ͕Ͱ͖Δ
  11. ©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Λར༻ʯ
  12. ©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
  13. ©2019 Wantedly, Inc. )%%΁ͷϥϯμ ϜΞΫηεͱ γʔέϯγϟϧΞΫηεͷ଎౓͕ࠩݪҼ 4FR4DBO *OEFY4DBO 3BOEPN"DDFTT 

      ̐     ཁૉ୯Ґͩͱߴίετ ͳͥߜΓࠐΈ৚͕݅؇͍ͱJOEFY͕࢖ΘΕͳ͍ͷ͔ʁ
  14. ©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&ͰͪΌΜͱߜΓࠐ·ΕΔΑ͏ʹ͢Δ σʔλͷ෼෍ʮ౷ܭ৘ใʯ͕େࣄ
  15. ©2019 Wantedly, Inc. *OEFYʹ͸σϝϦοτ΋͋ΔࣄΛ஌Δ #USFFJOEFYߋ৽ʹίετ͕͔͔Δ w Ϩίʔυͷߋ৽ʹ͕͔͔࣌ؒΔΑ͏ʹͳΔ w %#ͷ࢖༻͢ΔσʔλαΠζ͕૿͑Δ )05͕ޮ͔ͳ͍

    w )05͸1PTUHSF42-ͷΧϥϜͷߋ৽Λૣ͘͢Δ࢓૊ΈʢඞཁͳՕॴͷΈΛߋ৽͢Δʣ w ৄ͘͠͸IUUQMFUTQPTUHSFTRMKQEPDVNFOUTUVUPSJBMIPU@ࢀর
  16. ©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ແ͠
  17. ©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
  18. ©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))
  19. ©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ࢀর
  20. ©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
  21. ©2019 Wantedly, Inc. 1PTUHSF42-ͷ+0*/ΞϧΰϦζϜ ͭͷ+0*/ΞϧΰϦζϜ͕ଘࡏ w /FTUFE-PPQ+PJO w )BTI+PJO w

    .FSHF+PJO ࠷దͳΞϧΰϦζϜ͕ࣗಈతʹબ୒͞ΕΔ w JOEFYͷ༗ແ΍౷ܭ৘ใʢσʔλͷྔɾ෼෍ʣʹґଘ
  22. ©2019 Wantedly, Inc. /FTUFE-PPQ+PJO ,z IUUQTXXXTMJEFTIBSFOFUBEPSFQVNQQPTUHSFTRMQFSGPSNBODFUVOJOHΑΓҾ༻ ॏϧʔϓͰ͢΂ͯͷ૊Έ߹ΘͤΛࢼ͢ w 0 /.

    ʜۃΊͯ஗͍ w Ϩίʔυ਺͕গͳ͚Ε͹ߴ଎ w 5BCMFʹJOEFYΛషΕ͹ɺ͋Δఔ౓ߴ଎Խ͕Մೳ Ϩίʔυ਺/ Ϩίʔυ਺.
  23. ©2019 Wantedly, Inc. 5BCMFʹରͯ͠ɺҰ౓ϑϧεΩϟϯͯ͠ )BTI5BCMFΛ࡞੒ w 0 / . 

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

    w 5BCMF 5BCMFʹࣄલʹJOEFYΛషͬͯ͋Δࣄ͕৚݅ w ςʔϒϧαΠζ͕େ͖͍࣌ʹ͸࠷΋ߴ଎ .FSHF+PJO ,z IUUQTXXXTMJEFTIBSFOFU.JLJ4IJNPHBJQPTUHSFTRMFYQMBJOΑΓҾ༻
  25. ©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)
  26. ©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)
  27. ©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
  28. ©2019 Wantedly, Inc.  (SPVQ"HHSFHBUF w ೖྗ͞ΕͨσʔλΛ(SPVQLFZͰιʔτޙɺ֤άϧʔϓΛॱ࣍ॲཧ͢ΔΞϧΰϦζϜ w JOEFY͕͋ͬͯTPSUࡁΈͰ͋Ε͹ɺύΠϓϥΠϯԽ΋Մೳ 

    )BTI"HHSFHBUF w (SPVQLFZΛLFZͱ͢ΔҰ࣌తͳ)BTI5BCMFΛ࡞੒͠ɺ֤άϧʔϓΛॲཧ͢ΔΞϧΰϦζϜ w σʔλαΠζ͕NFNPSZʹࡌΔ͘Β͍খ͚͞Ε͹͜Ε͕બ͹ΕΔ 1PTUHSF42-ͷͭͷ"HHSFHBUFΞϧΰϦζϜ
  29. ©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)
  30. ©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 ҎԼͷྫͰ͸ɺ ʹݮগʂ
  31. ©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*/ͷલʹߜΓࠐΊΔͳΒߜΓࠐΉ ·ͱΊ
  32. ©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Λࢀর
  33. ©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)