PostgreSQL命令EXPLAIN ANALYZE是日常工作中了解和優(yōu)化SQL查詢(xún)過(guò)程所用到的最強(qiáng)大工具，后接如SELECT ...，UPDATE ...或者DELETE ...等SQL語(yǔ)句，命令執(zhí)行后并不返回?cái)?shù)據(jù)，而是輸出查詢(xún)計(jì)劃，詳細(xì)說(shuō)明規(guī)劃器通過(guò)何種方式來(lái)執(zhí)行給定的SQL語(yǔ)句。

下面是從Postgres Using EXPLAIN提取的查詢(xún)：

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2
ORDER BY t1.fivethous;

它生成的查詢(xún)計(jì)劃：

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 77kB
   ->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
         ->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 28kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning time: 0.194 ms
 Execution time: 8.008 ms

Postgres構(gòu)建了一個(gè)規(guī)劃節(jié)點(diǎn)的樹(shù)結(jié)構(gòu)，以表示所采取的不同操作，其中root根和每個(gè)->指向其中一個(gè)操作。在某些情況下，EXPLAIN ANALYZE會(huì)提供除執(zhí)行時(shí)間和行數(shù)之外的額外執(zhí)行統(tǒng)計(jì)信息，例如上面例子中的Sort及Hash。除第一個(gè)沒(méi)有->的行之外的任何行都是諸如此類(lèi)的信息，因此查詢(xún)的結(jié)構(gòu)是：

Sort
└── Hash Join
    ├── Seq Scan
    └── Hash
        └── Bitmap Heap Scan
            └── Bitmap Index Scan

每個(gè)樹(shù)分支代表子動(dòng)作，從里到外以確定哪個(gè)是“第一個(gè)”發(fā)生（盡管同一級(jí)別的節(jié)點(diǎn)順序可能不同）。

在tenk_unique1索引上執(zhí)行的第一個(gè)操作是Bitmap Index Scan：

->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
      Index Cond: (unique1 < 100)

這對(duì)應(yīng)于SQL WHERE t1.unique1 < 100。Postgres查找與條件unique1 < 100匹配的行位置。此處不會(huì)返回行數(shù)據(jù)本身。成本估算(cost=0.00..5.04 rows=101 width=0)意味著Postgres預(yù)期將“花費(fèi)” 任意計(jì)算單位的 5.04 來(lái)找到這些行。0.00是此節(jié)點(diǎn)開(kāi)始工作的成本（在這種情況下，即為查詢(xún)的啟動(dòng)時(shí)間）。rows是此索引掃描將返回的預(yù)估行數(shù)，width是這些返回行的預(yù)估大小（以字節(jié)為單位）（0是因?yàn)檫@里只關(guān)心位置，而不是行數(shù)據(jù)的內(nèi)容）。

因?yàn)槭褂昧?code>ANALYZE選項(xiàng)運(yùn)行EXPLAIN，所以查詢(xún)被實(shí)際執(zhí)行并捕獲了計(jì)時(shí)信息。(actual time=0.049..0.049 rows=100 loops=1)表示索引掃描執(zhí)行了1次（loops 值），結(jié)果返回了100行，實(shí)際時(shí)間是0 ..如果節(jié)點(diǎn)執(zhí)行了多次，實(shí)際時(shí)間是每次迭代的平均值，可以將該值乘以循環(huán)次數(shù)以獲取實(shí)際時(shí)間?；诔杀镜淖钚?最大時(shí)間的概念，范圍值也可能會(huì)有所不同。通過(guò)這些值，我們可以為該查詢(xún)生成一個(gè)成本比率，每個(gè)成本單位為0.049ms / 5.04單位≈0.01ms/單位。

索引掃描的結(jié)果將傳遞給Bitmap Heap Scan操作。在此節(jié)點(diǎn)中，Postgres將獲取別名為t1的tenk1表中行的位置，根據(jù)unique1 < 100條件篩選并獲取行。

->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
      Recheck Cond: (unique1 < 100)

當(dāng)乘以之前計(jì)算的0.01值時(shí)，我們可以得到成本預(yù)期的大概時(shí)間（229.20 - 5.07）*0.01≈2.24ms，同時(shí)每行實(shí)際時(shí)間為除以4后的結(jié)果：0.526ms。這可能是因?yàn)槌杀竟浪闶侨〉纳舷薅皇侨∷行枳x取的行，也或者因?yàn)镽echeck條件總是生效。

和表順序讀取行（a Seq Scan）相比，Bitmap Index Scan和Bitmap Heap Scan關(guān)聯(lián)操作成本要昂貴得多，但是因?yàn)樵谶@種情況下只需要訪問(wèn)相對(duì)較少的行，所以關(guān)聯(lián)操作最終會(huì)變得更快。通過(guò)在獲取行之前將行按照物理順序排序來(lái)進(jìn)一步加速，這會(huì)將單獨(dú)獲取的成本降到最低。節(jié)點(diǎn)名稱(chēng)中的“Bitmap”完成了排序操作。

表掃描的結(jié)果（tenk1表中滿(mǎn)足unique1 < 100條件的那些行）將在讀取時(shí)被插入到內(nèi)存的哈希表中。正如我們從成本中看到的那樣，這根本不需要時(shí)間。

->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
      Buckets: 1024  Batches: 1  Memory Usage: 28kB

哈希節(jié)點(diǎn)包括散列桶(hash buckets)和批次數(shù)(batches)相關(guān)的信息，以及內(nèi)存使用峰值情況。如果批次> 1，則還會(huì)包括未顯示的磁盤(pán)使用信息。內(nèi)存占用在100行* 244字節(jié)= 24.4 kB時(shí)是有意義的，它非常接近28kB，我們假定這是哈希鍵本身所占用的內(nèi)存。

接下來(lái)，Postgres從別名為t2的tenk2表讀取所有的10000行，并根據(jù)tenk1表行的Hash檢查它們。散列連接意味著將一個(gè)表的行輸入到內(nèi)存中的散列（先前的操作中已構(gòu)建），之后掃描另一個(gè)表的行，并根據(jù)散列表探測(cè)其值以進(jìn)行匹配。在第二行可以看到“匹配”的條件，Hash Cond: (t2.unique2 = t1.unique2)。請(qǐng)注意，因?yàn)椴樵?xún)是從tenk1和tenk2中選擇所有值，所以在散列連接期間每行的寬度加倍。

->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
      Hash Cond: (t2.unique2 = t1.unique2)
      ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)

現(xiàn)在已經(jīng)收集了滿(mǎn)足條件的所有行，可以對(duì)結(jié)果集進(jìn)行排序Sort Key: t1.fivethous。

Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
  Sort Key: t1.fivethous
  Sort Method: quicksort  Memory: 77kB

Sort節(jié)點(diǎn)包含排序算法quicksort相關(guān)的信息 ，排序是在內(nèi)存中還是在磁盤(pán)上完成（這將極大地影響速度），以及排序所需的內(nèi)存/磁盤(pán)空間量。

熟悉如何解讀查詢(xún)計(jì)劃會(huì)非常有助于優(yōu)化查詢(xún)。例如，Seq Scan節(jié)點(diǎn)通常表示添加索引的必要性，讀取速度可能要快得多。

翻譯并編輯，原文出處：https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan