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

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

©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

©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

©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

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

©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

©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)

©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

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

©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ʹΑͬͯίετΛ཈͑Δࣄ͕Ͱ͖Δ

©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Λར༻ʯ

©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

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

©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&ͰͪΌΜͱߜΓࠐ·ΕΔΑ͏ʹ͢Δ σʔλͷ෼෍ʮ౷ܭ৘ใʯ͕େࣄ

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

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

©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ແ͠

©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

©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ࢀর

©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

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

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

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

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

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

©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)

©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)

©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

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

©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)

©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 ҎԼͷྫͰ͸ɺ ʹݮগʂ

©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*/ͷલʹߜΓࠐΊΔͳΒߜΓࠐΉ ·ͱΊ

©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Λࢀর

©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)

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

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

More Decks by Nao Minami

Other Decks in Technology

Featured

Transcript