Share
Embed
実行計画から学ぶ PostgreSQL の内部動作とクエリ最適化 / Learn PostgreSQL from Explain
2019年 Wantedly 新人研修の資料です。
SQL に対する Explain 結果をベースに、SQL 実行時の PostgreSQL の内部動作や、それを踏まえた上でのクエリ最適化について話しました。
Nao Minami
May 15, 2019
Transcript
60 slides
-
©2019 Wantedly, Inc. ࣮ߦܭը͔ΒֶͿ 1PTUHSF42-ͷ෦ಈ࡞ͱ ΫΤϦ࠷దԽ 15.May.2019 - Nao Minami
(@south37) Wantedly, Inc. New Joiner Training 2019 -
©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) -
©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) #USFFJOEFYLFZͷൺֱͰ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 )051PTUHSF42-ͷΧϥϜͷߋ৽Λૣ͘͢ΔΈʢඞཁͳՕॴͷΈΛߋ৽͢Δʣ w ৄ͘͠IUUQMFUTQPTUHSFTRMKQEPDVNFOUTUVUPSJBMIPU@ࢀর -
©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 ؔͳͲͷฦΓΛ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)) -
©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. "QQFOEJY1PTUHSF42-ͷಛతͳػೳ 8JOEPX'VODUJPOT +40/5ZQFT )TUPSF
.BUFSJBMJ[FE7JFX 4UPSFE1SPDFEVSF 1-QH42- -
©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ܕ͕ଘࡏɻKTPOCCJOBSZGPSNBUͰ֨ೲɻ w ઐ༻ͷΦϖϨʔλʢAAAAʣΛར༻ͯ͠ɺ+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)