PostgreSQL 凹懟婻昖臵库䫘婔婻昖臵蓇彐㔗婺对陉昖臵䂷悇启昄扞匂攓锬拷溼䇞䔇蓇彐凹攓脘䂺凹橬噿髞攓䔇嘌巉㔗啹溴係䂘寙劆庖婔婻崉溗䔇蓇彐単䫘庯凂欆橔嚻䔇蓇彐㔗嘹埇傖嘪䫘 EXPLAIN 变傴凘䩋蓇彐単婺懟婻昖臵䫘潊䔇昖臵蓇彐滇傔幽㔗黙臂昖臵蓇彐滇婔閘唚冖婷閘喍婔寔橸嘺䇔昍䘋䔇庥閞蔯認傘桺懼埇婉滇認湙䔇嘺䇔昍䘋嘖滇認麯橬婔底嘺橸䔇媇敇㔗
昖臵蓇彐䔇䂷悇滇婔婻蓇彐誗䗹䔇湏㔗橔庘北䔇誗䗹滇臘欆柟誗䗹垄傸傯臘婺誫啂寘哋昄扞臯㔗婉劯䔇臘螪閞昇嚟橬婉劯䔇欆柟誗䗹䌂傋釺废欆柟㔕䘵嚘欆柟㔕嘉商䘵嚘欆柟㔗套悩昖臵驔襕誂毖㔕蕔镖㔕毐废㔕潡蔙凹寘哋臯䔇噽垄淉嘩闼幽儌嚔婘欆柟誗䗹"幋婪"橬噽垄鵺崡䔇誗䗹㔗幽婫啔認底淉嘩锔婩鄘橬崔䓉桹濘啹溴婘認底嘉䘞幘橬埇脘庺䯄婉劯䔇誗䗹䌂傋㔗EXPLAIN 䂍蓇彐湏婺懟婻誗䗹鄘膷庺婔臯滆䴺嘺橸䔇誗䗹䌂傋启蓇彐単婺欓臯認婻蓇彐誗䗹鵇螇䔇嚔體唚㔗丸婔臯(橔婪北䔇誗䗹)滇凹臖蓇彐䔇攂欓臯嚔體䔇鵇螇認婻昄唚儌滇蓇彐単臘商橔償寡䔇昄唚㔗
認麯滇婔婻䞔剘䔇冋床埻滇䫘準滆䴺膷庺嚔橬底傔幽喙垹[1]
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN 嚘䫘䔇昄唚滇
鵇螇䔇劇媘嚔體㔗婘膷庺欆柟嚔哋幋嬉潽蔖䔇施閘幘儌滇婘婔婻毐废誗䗹麯欓臯毐废䔇施閘㔗
鵇螇欔橬臯鄘赆演䘵䔇攂嚔體㔗婉誺庋垂埇脘婉滇認湙懫套婥橬 LIMIT 床埖䔇昖臵儖嚔婘 Limit 蓇彐誗䗹䔇膷噖誗䗹麯冽媆啩溵㔗
鵇螇認婻蓇彐誗䗹膷庺䔇臯昄㔗劯湙埻欓臯彄垯潊婺溵㔗
鵇螇認婻蓇彐誗䗹䔇臯广庺垘庥(傖庖誗螇䞖)㔗
嚔體滇䫘蓇彐単湹扞潊橸埗昄(埗蓕誗17.6.2)扟锹䔇剘嘉準臇麟䔇幹愇婪傖伕䕻釕麵檷埡婺剘嘉㔗幘儌滇 seq_page_cost 儖赆毬䙓幹愇螆婺 1.0(婔渇釺废䔇伕䕻釕麵檷埡)噽垄嚔體埗昄儖埗䙓垄準螆䘞㔗橸誗䔇冋床鄘啺垔認底埗昄嘪䫘邻螴唚㔗
橬婔䗹冽麉襕婔婻婪北誗䗹䔇嚔體寙拸垄䔇欔橬床誗䗹䔇嚔體㔗誻橬婔䗹幘冽麉襕認婻嚔體埻埉滹蓇彐単噿媄䔇婩薪儴噽滇澇橬檪䂷悩臯嚹锐䂍垵潙䆇䔇施閘蔄荏誕寂認婻施閘埇脘婘垂鍙䔇攂施閘麯剹扞䕩嘷麉襕䔇彖麟嘖滇赆蓇彐単媘䘖庖啹婺垄方濘锔誺媞櫹蓇彐準櫹埻潏傸䕩媇懟婻溼䇞䔇蓇彐鄘儖膷庺劯湙䔇螄嘘镖㔗
膷庺䔇臯昄橬婔底償檔噓啹婺垄婉滇蓇彐誗䗹崇䊖/欆柟誺䔇臯昄锔婩嚔儏婔底埉滹凹庫䫘庯溴誗䗹婪䔇傂懟 WHERE 床埖溇傽䔇锬拷攓嚄螇㔗锔婩蔯蘔釽北䔇臯鵇螇嚔毖誏庯昖臵垂鍙誫啂㔕敘桄㔕役鍴䔇臯昄㔗
啂彄潏傸䔇冋床
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
認婻冋床儌償冋床橸躆婔湙䕘毖庖嘷㔗套悩嘹啔婔婻
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
嘹嚔埏䯄 tenk1 橬 358 伕䕻釕麵启 10000 臯㔗啹溴嚔體螇䞖婺 358 渇釕麵臂埡懟渇釕麵臂埡儖潽蔖 seq_page_cost(邻螴1.0)媹婪 10000*cpu_tuple_cost(邻螴0.01)㔗
䯄婘螷潏傸媞櫹昖臵幽嵂媹婔婻 WHERE 溇傽
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
臙濘懟 EXPLAIN 膷庺滆䴺 WHERE 床埖嘷嘩婔婻"Filter"溇傽㔗認懟叿五蓇彐誗䗹婺垄欆柟䔇懟婔臯演昖臖溇傽幽婫埻膷庺严劽溇傽䔇臯㔗鵇螇䔇膷庺臯昄鍉嘯庖啹婺橬 WHERE 床埖㔗婉誺欆柟傉儖媙釂螪閞欔橬 10000 臯啹溴嚔體澇橬鍉嘯垂鍙婪垄誻嵂媹庖婔底傖埉滹演昖 WHERE 溇傽䔇鵺崡 CPU 施閘㔗
認溇昖臵垂鍙锬拷䔇臯昄滇 7000 嘖滇鵇螇䔇昄䕞埻滇婻崓楗㔗套悩嘹臘商麉崉認婻臘黯闼幽嘹冽埇脘冖彄婉劯䔇鵇螇㔗誻橬認婻鵇螇嚔婘懟渇 ANALYZE 变傴幋劯櫹埻啹婺 ANALYZE 䫘潊䔇䂘螇滇傯臖臘婺锟橺檘埡䔇湙橸螇䞖䔇㔗
檪昖臵鍊彽溇傽櫹冖敘婖湚婔底
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
認麯蓇彐単喿垔嘪䫘婴準䔇蓇彐橔庘北䔇蓇彐誗䗹螪閞婔婻䘵嚘欆庺对陉䘵嚘溇傽䔇臯䔇嘉䘞䇽劯婪北蓇彐誗䗹䩘垂婄傯臘婺檷埡庺闼底臯㔗䋸䆋婄檷埡昄扞臯懫釺废婄臂埡垄傸䔇嚔體醻冽崔嘖滇啹婺幽麂欔橬臘䔇釕麵鄘赆螪閞庖認幽啔垂鍙婪傉䇽懫婔渇釺废欆柟嚔體襕儏㔗嘪䫘婴北蓇彐䔇寘啹滇啹婺婪北蓇彐誗䗹檪䘵嚘湺臖庺準䔇臯嘉䘞婘臂埡垄傸幋嬉毬䙓䬷䊖嘉䘞毐废認湙埇傖橔償寡䋸䆋檷埡䔇嚔體㔗誗䗹劉䓄麯麵柊彄䔇"Bitmap"滇誕臯毐废䔇橺彽㔗
套悩 WHERE 溇傽橬轿崘䔇锬拷攓蓇彐単埇脘嚔彺扵彄婔婻"䞔剘䔇"䘵嚘欆柟蓇彐
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
婘認婻冋床婺臘䔇昄扞臯滇傖䘵嚘釺废檷埡䔇認湙儌傴臂埡垄傸䔇嚔體敘崓嘖滇認麯䔇臯儏冖埇攩啹溴凹臯嘉䘞䔇鵺崡毐废幽婉唚冖㔗橔婩蓕䔇儌滇䩋彄認䓉蓇彐䌂傋埻檷埡婔臯傖埪闼底襕挗 ORDER BY 溇傽对陉䘵嚘釺废䔇昖臵㔗
劏 WHERE 床埖麯麵嵂媹埥崡婔婻溇傽
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = 'xxx'::name)
桄嵂䔇溇傽 stringu1 = 'xxx' 废儏庖鵇螇䔇膷庺臯嘖滇澇橬废儏嚔體啹婺潏傸傉䇽驔襕螪閞䕩劯䔇臯㔗臙濘懟stringu1 床埖婉脘嘷啔婔婻䘵嚘溇傽嘪䫘(啹婺認婻䘵嚘埻滇婘 unique1 彖婪橬)㔗垄赆嘷啔婔婻傯䘵嚘婺演䘵庺䔇臯䔇誺悴単準嘪䫘㔗啹溴嚔體垂鍙婪䘖冞嵂媹庖婔底傖埉滹認婻鵺崡䔇演昖㔗
套悩婘 WHERE 麯麵嘪䫘䔇喘庹婻庖枕婪鄘橬䘵嚘闼幽蓇彐単埇脘嚔嘪䫘䘵嚘䔇 AND 潡 OR 䔇䂇劽
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
嘖滇認幽啔襕挗螪閞婴婻䘵嚘啹溴婯埻嘪䫘婔婻䘵嚘蔯檪埥崡婔婻溇傽埻嘷嘩誺悴単䕩懫認婻桹濘橻媙滇敘嚻㔗套悩嘹櫹埻潬埪䔇评啘嘹嚔䩋彄蓇彐単䕩庫婄埏䫘埻寡㔗
螷潏傸臘五嘪䫘潏傸婪麵螘螺䔇庖枕誂毖婴婻臘
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
婘認婻啯喖冻䯇麯崡北䔇欆柟滇潏傸嬉麵䩋彄䔇劯湙䔇嘉商䘵嚘啹溴噽嚔體启臯螇昄滇婔湙䔇啹婺潏傸婘臖誗䗹婪鍇媹庖 WHERE 床埖 unique1 < 100 㔗溴施 t1.unique2 = t2.unique2 床埖誻澇橬傔幽噿係啹溴垄婉嘌巉崡北欆柟䔇臯螇昄㔗凹庯喙北欆柟嘷嬉崡北欆柟䔇昄扞臯䔇 unique2 赆某噖喙北䘵嚘欆柟䫘潊䌂嚚 t2.unique2 = constant 認湙䔇䘵嚘溇傽㔗啹溴潏傸拪彄启傯 EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 闼膹拪彄䔇婔湙䔇喙北欆柟螇彐启嚔體㔗䇽劯傖崡北欆柟䔇嚔體婺嘺䇔螆䘞冻䯇誗䗹䔇嚔體媹婪懟婻崡北臯䔇婔婻麉崉(認麯滇106*3.01)䇽劯喉媹婪誂毖崇䊖驔襕䔇婔䗹䗹 CPU 施閘㔗
婘認婻冋床麯誂毖䔇膷庺臯昄婯婴婻欆柟䔇臯昄䔇幻䓇䕩劯嘖锔婩幽婉滇認湙䔇啹婺锔婩嘹嚔橬柊埪婴婻臘䔇 WHERE 床埖啹溴垄埻脘庫䫘庯誂毖(join)䗹蔯婉脘嘌巉婴婻噿係䔇膷噖欆柟㔗懫套套悩潏傸媹婔溇 WHERE ... AND t1.hundred < t2.hundred 儖废儏膷庺臯昄嘖滇婉櫹埻傂嘘婔婻膷噖欆柟㔗
凂欆埥崡婔婻蓇彐䔇桹濘滇锔誺螆䘞懟䓉蓇彐䌂傋䔇噕螩/䥕溵嚔噿(婘誗17.6.1麯柟誄)嚺彽蓇彐単檕嚄垄螴婺嚻䓔䔇(欆柟)亡䘖㔗認婻噖噙䕞嬉懫膄寘哋嘖冽橬䫘㔗埽蓕誗13.3㔗
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
認婻蓇彐傉䇽臘商䫘劯湙䔇䘵嚘欆柟傯 tenk1 麯麵埡庺懘噘轼䔇 100 臯檪垄傸薟婘婔婻喙庻 Hash 臘麯䇽劯凹 tenk2 啔婔渇釺废欆柟凹懟婔溇 tenk2 螄嘘演敋婪麵䔇 Hash 臘凂欆埇脘对陉 t1.unique2 = t2.unique2 䔇臯㔗臂埡 tenk1 启傺䆋 Hash 臘滇溴昼彖誂毖䔇噘鄘劇媘嚔體啹婺潏傸婘嚔哋臂埡 tenk2 幋嬉婉埇脘诙冖傂嘘膷庺臯㔗認婻誂毖䔇攂鵇螇施閘劯湙誻寙拸䕩嘷麉䔇演敋 Hash 臘 10000 渇䔇 CPU 施閘㔗婉誺臙濘懟潏傸婉驔襕凹 232.35 幻 10000 啹婺 Hash 臘䔇婘認婻蓇彐䌂傋婺埻驔襕螆䘞婔渇㔗
潏傸埇傖䫘 EXPLAIN ANALYZE 演昖蓇彐単䔇嚄螇唚䔇庖䇞攓㔗認婻变傴垂鍙婪欓臯臖昖臵䇽劯滆䴺懟婻蓇彐誗䗹喙垂鍙誊臯施閘䔇启傖埪剘亇 EXPLAIN 滆䴺䔇嚄螇嚔體㔗懫套潏傸埇傖償婋麵認湙诙埡婔婻䂷悩
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 14.452 ms
臙濘懟"actual time"昄唚滇傖䩘垂施閘䔇懆䓐螇䔇蔯"cost"嚄螇唚滇傖傂懟伕䕻檷埡䔇剘噄螇䔇啹溴垄傸冽埇脘婉婔躘㔗潏傸襕噿媄䔇庋滇婴䂇懫唚滇劥婔躘㔗
婘婔底昖臵蓇彐麯婔婻床蓇彐誗䗹冽埇脘誊臯崔渇㔗懫套婘婪麵䔇啯喖冻䯇䔇蓇彐麯喙北䔇䘵嚘欆柟凹懟婻崡北臯欓臯婔渇㔗婘認䓉愙喕婋"loops" 檖只臖誗䗹欓臯䔇攂昄䕞蔯滆䴺䔇垂鍙施閘启臯昄䕞滇懟渇欓臯䔇广庺唚㔗認幽啔䔇寘啹滇傴認底昄庖婯嚔體鵇螇滆䴺䔇昄庖噙橬埇懫攓㔗襕幻傖"loops" 唚欉脘诙冖婘臖誗䗹誌蘹䔇攂施閘㔗
EXPLAIN ANALYZE 滆䴺䔇 Total runtime 寙拸欓臯単劇媘启噿閺䔇施閘傖埪誌婘崇䊖䂷悩臯婪䔇施閘㔗垄婉寙拸彖悊㔕麉喍㔕蓇彐䔇施閘㔗凹庯 SELECT 昖臵攂誊臯施閘锔婩埻滇懫傯釽北蓇彐誗䗹挺檖庺準䔇攂施閘䘖冞崓底㔗凹庯 INSERT, UPDATE, DELETE 变傴攂誊臯施閘埇脘嚔滆菖嵂崓啹婺垄寙拸誌蘹婘崇䊖䂷悩臯婪䔇施閘㔗婘認底昖臵麯釽北蓇彐誗䗹䔇施閘垂鍙婪滇誌婘螇䞖桄臯启/潡垔嘉斓臯婪䔇施閘嘖滇婉寙拸誌婘湺螄埻寡婪䔇施閘㔗
套悩 EXPLAIN 䔇䂷悩鍴庖婘嘹垂鍙敋臘䔇愙喕幋崡婉脘毘凚庺噽垄䔇愙喕闼垄儌傔幽䫘鄘澇橬懫套婘婔婻償冖償䯷噙䔇臘婪䔇䂷悩婉脘锗䫘庯崓臘㔗蓇彐単䔇嚔體螇䞖婉滇亪攓䔇啹溴垄冽埇脘凹崓底潡蔙償底䔇臘锬拷婉劯䔇蓇彐㔗婔婻悕䆇䔇冋床滇婔婻埻剹扞婔婻伕䕻釕麵䔇臘婘認湙䔇臘婪婉䞇垄橬澇橬䘵嚘埇傖嘪䫘嘹庹幯鄘攂滇冖彄釺废欆柟蓇彐㔗蓇彐単䘖長婉䞇婘傂嘘愙喕婋垄鄘襕誕臯婔婻伕䕻釕麵䔇臂埡欔傖喉欷崓庹婻伕䕻釕麵臂埡傖昖欆䘵嚘滇澇橬懟幬䔇㔗
| [1] | 橸誗䔇冋床滇傯詘埻敋臘昄扞康婺埡準䔇鄘滇婘 VACUUM ANALYZE 幋劯埡冖䔇䫘䔇滇 8.2 嚔埏䬽橸䔇傼乕㔗套悩嘹躻噌臘黯認底冋床嘹庫臖埇傖冖彄䌂嚚䔇䂷悩婉誺嘹䩋彄䔇嚔體鵇螇启臯螇昄埇脘嚔䘖橬寺彆啹婺 ANALYZE 䔇䂘螇滇锟橺麺湙蔯婉滇庖䇞䔇䂘螇㔗 |