PostgreSQL EXPLAIN

PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔		媆誕	嬉誕

柟誄

認溇变傴滆䴺 PostgreSQL 蓇彐単婺欔柊冕䔇臺埖䫘潊䔇欓臯蓇彐㔗欓臯蓇彐滆䴺臺埖嚘䫘䔇臘滇套嘘赆欆柟䔇(䞔剘䔇釺废欆柟誻滇䘵欆柟)幽婫套悩嚘䫘庖崔婻臘麺䫘庖傔幽湙䔇誂毖䞖濘傯懟婻膷噖䔇臘婺埡庺欔驔襕䔇螄嘘㔗

滆䴺庺準䔇橔噿髞䔇鄘彖滇鵇螇䔇臺埖欓臯嚔體認儌滇蓇彐単凹誊臯臖臺埖欔驔施閘䔇嚄螇(傖伕䕻釕麵庻埡婺剘嘉螇麟)㔗垂鍙婪滆䴺庖婴婻昄庖誫啂丸婔臯螄嘘嬉䔇劇媘施閘启誫啂欔橬螄嘘䔇攂施閘㔗凹庯崓崔昄昖臵蔯蘔噿媄䔇滇攂施閘嘖滇婘昊底䯇嵄婋懫套婔婻 EXISTS 床昖臵麯蓇彐単儖锬拷橔償劇媘施閘蔯婉滇橔償攂施閘(啹婺欓臯単婘诙埡婔溇螄嘘劯攂滇襕啩婋準)㔗劯湙套悩嘹䫘婔溇 LIMIT 床埖鍊彽誫啂䔇螄嘘昄蓇彐単嚔婘橔䂽䔇嚔體婪啔婔婻劽䊖䔇某唚傖螇䞖巻婻蓇彐嚔體橔䩕㔗

ANALYZE 锬釹凚躘昖臵赆垂鍙欓臯蔯婉備備滇蓇彐㔗垄婘滆䴺婺嵂媹庖婘懟婻蓇彐誗䗹喙鄘誌毬䔇攂施閘(傖懆䓐螇)启垄垂鍙誫啂䔇臯昄㔗認底昄扞凹抩䘵臖蓇彐単䔇鵇橘滇劥启䯄垂䕩誏冽橬婞媷㔗

㔊麉襕㔏襕螄嘟䔇滇昖臵垂鍙婪婘嘪䫘 ANALYZE 䔇施唍滇欓臯䔇㔗儘䞇 EXPLAIN 嚔檕嚄傂嘘 SELECT 誫啂䔇膷庺嘖滇噽垄昖臵䔇嬇嘩䫘誻滇婔湙嚔埏䫘䔇㔗套悩嘹婘 INSERT, UPDATE, DELETE, EXECUTE 臺埖麯嘪䫘 EXPLAIN ANALYZE 蔯婫誻婉愿螷昖臵嘌巉昄扞埇傖䫘婋麵䔇桹濘
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

埗昄

ANALYZE: 欓臯变傴幽滆䴺垂鍙誊臯施閘
VERBOSE: 滆䴺蓇彐湏垯昘䔇喙鄘臘䯄嘵嚟蔯婉備備滇婔婻揻襕㔗锔婩認婻锬釹埻滇婘䬹枪䔇脄臘誺䘋婺橬䫘㔗VERBOSE 膷庺埇脘滇幘埇脘婉滇欷剄噖昘䔇噙嘷埡喿庯陉䘞埗昄 explain_pretty_print 䔇唚㔗
statement: 嘹愿襕昖䩋蓇彐䂷悩䔇傂嘘 SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE 臺埖幋婔㔗

濘懟

婘 PostgreSQL 麯埻橬冽儏䔇婔底桺懼傋䂉橬噿嚻寡単螇䞖嚔體䔇閞鵻㔗埗蔄誗13.1诙埡敘崔媇敇㔗

婺庖螷 PostgreSQL 昖臵蓇彐単婘嚻寡昖臵䔇施唍啔庺劽䊖䔇彴桺驔襕誊臯 ANALYZE 臺埖傖螄嘘橬噿昄扞婘臘婺䔇彖婄䔇䂘螇媇敇㔗套悩嘹澇啔誺認傽庋愙(潡蔙套悩躻婪渇 ANALYZE 傖準臘婺䔇昄扞䂘螇彖婄埏䫘庖滆菖埻寡)闼幽螇䞖庺準䔇嚔體鵇螇冽埇脘婯昖臵䔇垂鍙匂攓幽婉凹庫啹溴冽埇脘嚔锬埡婔婻懫膄噞媾䔇昖臵蓇彐㔗

嘺啹昖臵嚻寡(GEQO)锟橺䔇敋臘欓臯蓇彐㔗啹溴嘷臘䔇昄䕞轙誺庖 geqo_threshold 幋劯儖凚躘嘪䫘嘺啹昖臵嚻寡欓臯蓇彐嘺橸婪婘懟渇臺埖赆欓臯䔇施唍鄘嚔埻寡㔗

冋床

滆䴺婔婻凹埻橬婔婻 integer 彖启 10000 臯臘䔇䞔剘昖臵䔇昖臵蓇彐

EXPLAIN SELECT * FROM foo;

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)

套悩庻婘婔婻䘵嚘幽婫嘪䫘婔婻埇庫䫘䘵嚘䔇 WHERE 溇傽䔇昖臵EXPLAIN 嚔滆䴺婉劯䔇蓇彐

EXPLAIN SELECT * FROM foo WHERE i = 4;

                         QUERY PLAN
--------------------------------------------------------------
 Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
   Index Cond: (i = 4)
(2 rows)

婋麵滇婔婻嘪䫘庖蕔镖庘昄䔇昖臵䔇昖臵蓇彐

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)
(3 rows)

婋麵滇婔婻嘪䫘 EXPLAIN EXECUTE 滆䴺婔婻噾鵇䚡喍䔇昖臵蓇彐䔇冋床

PREPARE query(int, int) AS SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
   ->  Index Scan using test_pkey on test  (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
         Index Cond: ((id > $1) AND (id < $2))
 Total runtime: 0.851 ms
(4 rows)

濘懟認麯滆䴺䔇昄庖䫔躿誻橬锬拷䔇昖臵亡䘖鄘橬埇脘婘劇婻 PostgreSQL 䬽橸幋閘婉劯啹婺蓇彐単婘婉桺櫹誕㔗埥崡ANALYZE 变傴嘪䫘锟橺䔇麺湙準嚄螇昄扞䂘螇啹溴婔渇桄䔇 ANALYZE 誊臯幋劯嚔體嚄螇埇脘嚔埻寡剿嘪昄扞䔇垂鍙彖婄澇橬櫹埻幘認湙㔗

EXPLAIN

劉䓄

臺濘

柟誄

埗昄

濘懟

冋床

噚垹攓

埽蓕