EXPLAIN

NAME
SYNOPSIS
DESCRIPTION æè¿°
PARAMETERS åæ°
NOTES 注æ
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
è¯è
è·

---

NAME

EXPLAIN - æ¾ç¤ºè¯å¥æ§è¡è§å

SYNOPSIS

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

DESCRIPTION æè¿°

è¿æ¡å½ä»¤æ¾ç¤ºPostgreSQLè§åå¨ä¸ºææä¾çè¯å¥çæçæ§è¡è§åã æ§è¡è§åæ¾ç¤ºè¯- å¥å¼ç¨ç表æ¯å¦ä½è¢«æ«æç--- æ¯ç®åç顺åºæ«æï¼è¿æ¯ç´¢å¼æ«æç --- 并ä¸å¦æå¼ç¨äºå¤ä¸ªè¡¨ï¼ éç¨äºä»ä¹æ ·çè¿æ¥ç®æ³ä»æ¯ä¸ªè¾å¥ç表ä¸- ååºæéè¦çè®°å½ã

æ¾ç¤ºåºæ¥çæå³é®çé¨åæ¯é¢è®¡çè¯å¥æ§è¡å¼éï¼ è¿å°±æ¯è§åå¨å¯¹è¿è¡è¯¥è¯- å¥æéæ¶é´ç估计ï¼ä»¥ç£ç页é¢åå为åä½è®¡éï¼ã å®éä¸æ¾ç¤ºäºä¸¤ä¸ªæ°å- ï¼è¿å第ä¸è¡è®°å½åçå¯å¨æ¶é´ï¼ åè¿åææè®°å½çæ»æ¶é´ã对äºå¤§å¤æ°æ¥è¯¢èè¨ï¼å³å¿çæ¯æ»æ¶é´ï¼ä½æ¯ï¼ å¨æäºç¯å¢ä¸ï¼æ¯å¦ä¸ä¸ª EXISTS åæ¥è¯¢éï¼ è§åå¨å°éæ©æå°å¯å¨æ¶é´èä¸æ¯æå°æ»æ¶é´ï¼å为æ§è¡å¨å¨è·åä¸æ¡è®°å½åæ»æ¯è¦åä¸æ¥ï¼ã åæ ·ï¼å¦æä½ ç¨ä¸æ¡ LIMIT åå¥éå¶è¿åçè®°å½æ°ï¼ è§åå¨ä¼å¨æç»çå¼éä¸åä¸ä¸ªåççæå¼ä»¥è®¡ç®åªä¸ªè§åå¼éæçã

ANALYZE é项导è´æ¥è¯¢è¢«å®éæ§è¡ï¼èä¸ä»ä»æ¯è§åã å®å¨æ¾ç¤ºä¸- å¢å äºå¨æ¯ä¸ªè§åèç¹åé¨è±æçæ»æ¶é´ï¼ä»¥æ¯«ç§è®¡ï¼åå®å®éè¿åçè¡æ°ã è¿äºæ°æ®å¯¹æ索该è§åå¨çé¢ææ¯å¦åç°å®ç¸è¿å¾æ帮å©ã

Important:
è¦è®°ä½çæ¯æ¥è¯¢å®éä¸å¨ä½¿ç¨ ANALYZE çæ¶åæ¯æ§è¡çã 尽管 EXPLAIN ä¼æå¼ä»»ä½ SELECT ä¼è¿åçè¾åºï¼ ä½æ¯å¶å®æ¥è¯¢çå¯ä½ç¨è¿æ¯ä¸æ ·ä¼åççã å¦æä½ å¨ INSERTï¼UPDATEï¼DELETEï¼æè EXECUTE è¯å¥éä½¿ç¨ EXPLAIN ANALYZEï¼èä¸è¿ä¸æ³è®©æ¥è¯¢å½±åä½ çæ°æ®ï¼ ç¨ä¸é¢çæ¹æ³ï¼

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

PARAMETERS åæ°

ANALYZE

æ§è¡å½ä»¤å¹¶æ¾ç¤ºå®éè¿è¡æ¶é´ã

VERBOSE

æ¾ç¤ºè§åæå®æ´çåé¨è¡¨ç°å½¢å¼ï¼èä¸ä»ä»æ¯ä¸ä¸ªæè¦ãé常ï¼è¿ä¸ªé项åªæ¯å¨è°è¯ PostgreSQL çæ¶åæç¨ã VERBOSE è¾åºå¯è½æ¯æå°å¾å·¥æ´çï¼ä¹å¯è½ä¸æ¯ï¼ å·ä½åå³äºéç½®åæ° explain_pretty_printã

statement

ä»»ä½ SELECT, INSERT, UPDATE, DELETE, EXECUTE, æ DECLARE è¯å¥ã

NOTES 注æ

å¨ PostgreSQL éåªæå¾å°çä¸äºææ¡£ä»ç»æå³ä¼åå¨è®¡ç®å¼éçé®é¢ãåè Section 13.1 ‘‘Using EXPLAIN’’ è·åæ´å¤ä¿¡æ¯ã

为äºè®© PostgreSQL æ¥è¯¢è§åå¨å¨ä¼åæ¥è¯¢çæ¶åååºåççå¤æï¼ æ们éè¦è¿è¡ ANALYZE è¯å¥ä»¥è®°å½æå³æ°æ®å¨è¡¨ä¸çåå¸çç»è®¡ä¿¡æ¯ã å¦æ你没åè¿è¿ä»¶äºæï¼æèå¦æèªä¸æ¬¡ ANALYZE 以æ¥ï¼ 表ä¸- çæ°æ®ç»è®¡åå¸åçäºæ¾èååï¼ï¼é£ä¹è®¡ç®åºæ¥çå¼éé¢è®¡å¾å¯è½ä¸æ¥è¯¢çå®éå±æ§å¹¶ä¸å¯¹åºï¼ å æ¤å¾å¯è½ä¼éåä¸ä¸ªæ¯è¾å·®çæ¥è¯¢è§åã

å¨ PostgreSQL 7.3 以åï¼æ¥è¯¢è§åæ¯ä»¥ NOTICE æ¶æ¯çå½¢å¼ååºæ¥çã ç°å¨å®çæ¾ç¤ºæ ¼å¼æ¯ä¸ä¸ªæ¥è¯¢ç»æï¼æ ¼å¼åæäºç±»ä¼¼ä¸ä¸ªæå个ææ¬å- 段ç表ãï¼

EXAMPLES ä¾å

æ¾ç¤ºä¸ä¸ªå¯¹åªæä¸ä¸ª int4 åå 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 è¿è¡ä¹åå¼é估计å¯è½ä¼ååï¼ å³ä½¿æ°æ®çå®éåå¸æ²¡ææ¹åä¹è¿æ ·ã

COMPATIBILITY å¼å®¹æ§

å¨ SQL æ åä¸æ²¡æEXPLAIN è¯å¥ã

è¯è

Postgresql ä¸æç½ç« ä½ä¼å¹³ <laser@pgsqldb.org>

è·

æ¬é¡µé¢ä¸æçç±ä¸æ man æå页计åæä¾ã
ä¸æ man æå页计åï¼https://github.com/man-pages-zh/manpages-zh

---