2019年 Wantedly 新人研修の資料です。
SQL に対する Explain 結果をベースに、SQL 実行時の PostgreSQL の内部動作や、それを踏まえた上でのクエリ最適化について話しました。
©2019 Wantedly, Inc.࣮ߦܭը͔ΒֶͿ1PTUHSF42-ͷ෦ಈ࡞ͱΫΤϦ࠷దԽ15.May.2019 - Nao Minami (@south37) Wantedly, Inc.New Joiner Training 2019
View Slide
©2019 Wantedly, Inc.͡Ίʹ
©2019 Wantedly, Inc.42-ͷ࣮ߦ࣌ʹ3%#ͰߦΘΕΔ෦ಈ࡞ΛΔ&YQMBJOͷಡΈํɺదͳJOEFYͷઃఆΛΔࠓͷΰʔϧ
©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 varyingIndexes:"companies_pkey" PRIMARY KEY, btree (id)"index_companies_on_country" btree (country)
©2019 Wantedly, Inc.ࠓ෦ಈ࡞Λཧղ͢Δ42-SELECTcompanies.country,COUNT(jobs.id)FROMjobsINNER JOIN companies ON companies.id = jobs.company_idWHEREcompanies.id < 1000GROUP BYcompanies.country
©2019 Wantedly, Inc.·ͣ42-Λ&YQMBJOͯ͠ΈΑ͏
©2019 Wantedly, Inc.EXPLAINSELECTcompanies.country,COUNT(jobs.id)FROMjobsINNER JOIN companies ON companies.id = jobs.company_idWHEREcompanies.id < 1000GROUP BYcompanies.country1PTUHSF42-ͷ&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+PJO4FR4DBO)BTI*OEFY4DBO
©2019 Wantedly, Inc.ίετͷݟํSeq Scan on jobs (cost=0.00..897.00 rows=50000 width=8)ॳظԽίετ ૯ίετ ϨίʔυϨίʔυ͋ͨΓͷσʔλαΠζ όΠτ૯ίετॳظԽίετ ϨίʔυʷϨίʔυ͋ͨΓͷऔಘίετ
©2019 Wantedly, Inc.༨ஊ&91-"*/"/"-:4&ͩͱ࣮ࡍʹ࣮ߦEXPLAINANALYSESELECTcompanies.country,COUNT(jobs.id)FROMjobsINNER JOIN companies ON companies.id = jobs.company_idWHEREcompanies.id < 1000GROUP BYcompanies.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) (actualtime=0.022..0.527 rows=999 loops=1)
©2019 Wantedly, Inc.&91-"*/ʹ͍ͭͯIUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDTRMFYQMBJOIUNMΑΓৄ͘͠Γ͍ͨํެࣜυΩϡϝϯτΛࢀর
©2019 Wantedly, Inc.42-࣮ߦͷ'JSTU4UFQσʔλऔಘ
©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+PJO4FR4DBO)BTI*OEFY4DBO
©2019 Wantedly, Inc.*OEFY4DBOJOEFYΛར༻ͨ͠σʔλऔಘ)BTI"HHSFHBUF)BTI+PJO4FR4DBO)BTI*OEFY4DBO·ͣJOEFY4DBOʹ͢Δ
©2019 Wantedly, Inc.*OEFYͷΈ#USFFJOEFYw ϊʔυ͋ͨΓඦཁૉw ཁૉͱͯ͠ɺஈͰສ݅֨ೲߴͳσʔλऔಘIUUQTXXXWFSUBCFMPDPNCMPHUFDIOJDBMBSUJDMFTBMMBCPVUJOEFYFTQBSUNZTRMJOEFYTUSVDUVSFBOEQFSGPSNBODFΑΓҾ༻
©2019 Wantedly, Inc.*OEFYͷར༻ʹΑΔߴԽEXPLAIN SELECT jobs.* FROM jobs WHERE jobs.id = 1QUERY 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 = 1QUERY PLAN-----------------------------------------------------------------------Seq Scan on jobs (cost=0.00..1022.00 rows=1 width=28)Filter: (id = 1)JOEFYແ͠JOEFYʹΑͬͯίετΛ͑Δࣄ͕Ͱ͖Δ
©2019 Wantedly, Inc.ҰํɺJOEFYͷར༻ʹؾΛ͚ͭΔϙΠϯτ͋Δ
©2019 Wantedly, Inc.*OEFYόουύλʔϯJOEFYΛ࡞ͨ͠ΧϥϜʹԋࢉEXPLAIN SELECT profiles.* FROM profiles WHERE (lower(email) = '[email protected]') LIMIT 1QUERY 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 *OEFY4DBO3BOEPN"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.༨ஊ1PTUHSF42-ͰͷσʔλϨΠΞτৄ͘͠Γ͍ͨํIUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDTUPSBHFIUNM·ͨʮ෦ߏ͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇʯΛࢀরʮ෦ߏ͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ ٕज़ධࣾʯΑΓҾ༻
©2019 Wantedly, Inc.*OEFYʹσϝϦοτ͋ΔࣄΛΔ#USFFJOEFYߋ৽ʹίετ͕͔͔Δw Ϩίʔυͷߋ৽ʹ͕͔͔࣌ؒΔΑ͏ʹͳΔw %#ͷ༻͢ΔσʔλαΠζ͕૿͑Δ)05͕ޮ͔ͳ͍w )051PTUHSF42-ͷΧϥϜͷߋ৽Λૣ͘͢ΔΈʢඞཁͳՕॴͷΈΛߋ৽͢Δʣw ৄ͘͠IUUQMFUTQPTUHSFTRMKQEPDVNFOUTUVUPSJBMIPU@ࢀর
©2019 Wantedly, Inc.1PTUHSF42-ʹ༷ʑͳ*OEFY͕͋Δ 6OJRVF*OEFY .VMUJDPMVNO*OEFY *OEFYFTPO&YQSFTTJPOT 1BSUJBM*OEFYFT
©2019 Wantedly, Inc..VMUJDPMVNO*OEFYIUUQTVTFUIFJOEFYMVLFDPNTRMXIFSFDMBVTFUIFFRVBMTPQFSBUPSDPODBUFOBUFELFZTΑΓҾ༻lෳΧϥϜʹରͯ͠ͷJOEFYzΧϥϜͷΈ߹Θͤͱͯ͠ιʔτ
©2019 Wantedly, Inc..VMUJDPMVNO*OEFYͷར༻ʹΑΔߴԽEXPLAIN SELECT tourist_spots.* FROM tourist_spots WHERE tourist_spots.country = “japan" ANDtourist_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 = 1QUERY 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"."countryQUERY 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 varyingIndexes:"profiles_pkey" PRIMARY KEY, btree (id)"index_profiles_on_lower_email" btree (lower(email::text))
©2019 Wantedly, Inc.*OEFYFTPO&YQSFTTJPOTMPXFS 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*/
©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+PJO4FR4DBO)BTI*OEFY4DBO
©2019 Wantedly, Inc.1PTUHSF42-ͷ+0*/ΞϧΰϦζϜͭͷ+0*/ΞϧΰϦζϜ͕ଘࡏw /FTUFE-PPQ+PJOw )BTI+PJOw .FSHF+PJO࠷దͳΞϧΰϦζϜ͕ࣗಈతʹબ͞ΕΔw JOEFYͷ༗ແ౷ܭใʢσʔλͷྔɾʣʹґଘ
©2019 Wantedly, Inc./FTUFE-PPQ+PJO,zIUUQTXXXTMJEFTIBSFOFUBEPSFQVNQQPTUHSFTRMQFSGPSNBODFUVOJOHΑΓҾ༻ॏϧʔϓͰͯ͢ͷΈ߹ΘͤΛࢼ͢w 0 /.ʜۃΊ͍ͯw Ϩίʔυ͕গͳ͚Εߴw 5BCMFʹJOEFYΛషΕɺ͋ΔఔߴԽ͕ՄೳϨίʔυ/ Ϩίʔυ.
©2019 Wantedly, Inc.5BCMFʹରͯ͠ɺҰϑϧεΩϟϯͯ͠)BTI5BCMFΛ࡞w 0 /.w 5BCMF͕શͯ.FNPSZʹࡌΔ͘Β͍খ͍͞αΠζͰ͋Δࣄ͕݅w .FNPSZ্Ͱ5BCMFͷϨίʔυΛ୳ࡧͰ͖ΔͷͰߴ)BTI+PJO,zIUUQTXXXTMJEFTIBSFOFU.JLJ4IJNPHBJQPTUHSFTRMFYQMBJOΑΓҾ༻5BCMFϨίʔυ/5BCMFϨίʔυ.)BTI5BCMF
©2019 Wantedly, Inc.ιʔτࡁΈͷ5BCMFͱ5BCMFʹରͯ͠ɺҰ͚ͩϑϧεΩϟϯw 0 /.w 5BCMF 5BCMFʹࣄલʹJOEFYΛషͬͯ͋Δࣄ͕݅w ςʔϒϧαΠζ͕େ͖͍࣌ʹ࠷ߴ.FSHF+PJO,zIUUQTXXXTMJEFTIBSFOFU.JLJ4IJNPHBJQPTUHSFTRMFYQMBJOΑΓҾ༻
©2019 Wantedly, Inc.JOEFY͕͋ͬͯ+0*/͕͍έʔεଘࡏ͢Δ
©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.idQUERY 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.idWHERE 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=0Index 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
©2019 Wantedly, Inc.42-࣮ߦͷ-BTU4UFQσʔλू "HHSFHBUFQUERY 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+PJO4FR4DBO)BTI*OEFY4DBO
©2019 Wantedly, Inc.1PTUHSF42-ͷ"HHSFHBUFΞϧΰϦζϜͭͷ"HHSFHBUFΞϧΰϦζϜ͕ଘࡏw (SPVQ"HHSFHBUFw )BTI"HHSFHBUF
©2019 Wantedly, Inc. (SPVQ"HHSFHBUFw ೖྗ͞ΕͨσʔλΛ(SPVQLFZͰιʔτޙɺ֤άϧʔϓΛॱ࣍ॲཧ͢ΔΞϧΰϦζϜw JOEFY͕͋ͬͯTPSUࡁΈͰ͋ΕɺύΠϓϥΠϯԽՄೳ )BTI"HHSFHBUFw (SPVQLFZΛLFZͱ͢ΔҰ࣌తͳ)BTI5BCMFΛ࡞͠ɺ֤άϧʔϓΛॲཧ͢ΔΞϧΰϦζϜw σʔλαΠζ͕NFNPSZʹࡌΔ͘Β͍খ͚͞Ε͜Ε͕બΕΔ1PTUHSF42-ͷͭͷ"HHSFHBUFΞϧΰϦζϜ
©2019 Wantedly, Inc.ͦͷଞͷ1PTUHSF42-ͷ࣮ߦεςοϓ
©2019 Wantedly, Inc.4PSUͱ-JNJU03%&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 20QUERY 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#:ʹJOEFYEXPLAIN SELECT page_view_logs.* FROM page_view_logs ORDER BY viewed_at ASC LIMIT 20QUERY PLAN-----------------------------------------------------------------------Limit (cost=0.42..1.09 rows=20 width=28)-> Index Scan using index_page_view_log_with_indices_on_viewed_at onpage_view_log_with_indices (cost=0.42..16698.78 rows=501024 width=28)*OEFYʹΑͬͯܶతʹίετ͕Լ͕Δw ҎԼͷྫͰɺ ʹݮগʂ
©2019 Wantedly, Inc.·ͱΊ
©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ʹJOEFYw +0*/ͷલʹߜΓࠐΊΔͳΒߜΓࠐΉ·ͱΊ
©2019 Wantedly, Inc."QQFOEJY1PTUHSF42-ͷಛతͳػೳ
©2019 Wantedly, Inc."QQFOEJY1PTUHSF42-ͷಛతͳػೳ 8JOEPX'VODUJPOT +40/5ZQFT )TUPSF .BUFSJBMJ[FE7JFX 4UPSFE1SPDFEVSF 1-QH42-
©2019 Wantedly, Inc.8JOEPX'VODUJPOTEXPLAIN SELECT country, rank() OVER (PARTITION BY country ORDER BY id DESC) FROM companiesQUERY PLAN-----------------------------------------------------------------------WindowAgg (cost=936.35..1155.63 rows=10964 width=16)-> Sort (cost=936.35..963.76 rows=10964 width=16)Sort Key: country, id1BSUJUJPO͝ͱʹɺΛܭࢉ͢Δػೳw ߴػೳͳूؔΛར༻Մೳcountry | rank--------------+------britain | 1china | 1china | 2china | 3country_0 | 1IUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDUVUPSJBMXJOEPXIUNMΛࢀর
©2019 Wantedly, Inc.+40/5ZQFTINSERT 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)
©2019 Wantedly, Inc.)TUPSFLFZ WBMVFͷϖΞΛͭͷΧϥϜʹอଘՄೳͳσʔλܕw +40/5ZQFTͱಉ༷ʹɺઐ༻ͷΦϖϨʔλͰWBMVFʹΞΫηεՄೳIUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDITUPSFIUNMΛࢀর
©2019 Wantedly, Inc..BUFSJBMJ[FE7JFXΩϟογϡ͞Εͨ7JFXΛ࡞͢Δػೳw 7JFXͷߴԽ͕Մೳw खಈSFGSFTI͕ඞཁIUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDTRMDSFBUFNBUFSJBMJ[FEWJFXIUNMΛࢀর
©2019 Wantedly, Inc.4UPSFE1SPDFEVSF1PTUHSF42-Ͱ࣮ߦՄೳͳGVODUJPOΛఆٛ͢Δػೳw SPVOEUSJQͷݮগɺQFSGPSNBODF্ͷҝʹར༻͞ΕΔέʔε͕͋ΔIUUQXXXQPTUHSFTRMPSHEPDTDVSSFOUTUBUJDQMQHTRMIUNMΛࢀর
©2019 Wantedly, Inc.ࢀߟ
©2019 Wantedly, Inc.w 1PTUHSF42-ެࣜυΩϡϝϯτw IUUQTXXXQPTUHSFTRMPSHEPDTDVSSFOUw ෦ߏ͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ ٕज़ධࣾࢀߟ
©2019 Wantedly, Inc.w 4VTBO:JOIUUQTVOTQMBTIDPNQIPUPT+*WCP(-FIP1IPUP$SFEJU