更深入了解 EXPLAIN

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

EXPLAIN 可選參數

VERBOSE

顯示各節點欄位
顯示資料表結構與別名

ANALYZE

會執行查詢
顯示實際執行時間（毫秒）

VERBOSE

EXPLAIN VERBOSE
SELECT * FROM cheeses

在 dairy.cheeses 上進行 Seq Scan（cost=0.00..10.50 rows=5725 width=296）輸出：name、species、type、age，並底線標示 dairy 與 Output

ANALYZE

EXPLAIN ANALYZE
SELECT * FROM cheeses

在 cheeses 上進行 Seq Scan（cost=0.00..10.50 rows=5725 width=296）。其餘區段以紅色底線標示。（actual time = 0.007..1.087 rows=11992 loops=1） Planning Time: 0.059 ms Execution Time: 1.538 ms

最適合用來縮短執行時間

查詢計畫－彙總

EXPLAIN ANALYZE
SELECT type, AVG(age) AS avg_age
FROM cheeses
GROUP BY type -- hard or soft cheese

HashAggregate  (cost=314.88..317.38 rows=200 width=40)(actual time = 4.973..4.975 
                rows=2 loops=1)
  Group Key: type
  ->  Seq Scan on cheeses  (cost=0.00..286.25 rows=5725 width=10)(actual time = 
                            0.016..2.546 rows = 11992 loops=1)
Planning Time: 12.891 ms
Execution Time: 5.074 ms

查詢計畫－排序

EXPLAIN ANALYZE
SELECT name, age
FROM cheeses
ORDER BY age DESC

Sort  (cost=1161.37..1191.35 rows=11992 width=20)(actual time = 4.281..5.331 
       rows=11992 loops=1)
  Sort Key: age DESC
  Sort Method: quicksort Memory: 1216kB
  ->  Seq Scan on cheeses  (cost=0.00..348.92 rows=11992 width=20)(actual time = 
                            0.0007..1.799 rows = 11992 loops=1)
Planning Time: 0.131 ms
Execution Time: 5.870 ms

EXPLAIN ANALYZE
SELECT name, age FROM cheeses
INNER JOIN animals ON cheeses.species = animals.species

Hash Join  (cost=182.97..4339.35 rows=335776 width=145)(actual time=2.755..138.418 
            rows=335776 loops=1)
  Hash Cond: (cheeses.species = animals.species)
  ->  Seq Scan on cheeses  (cost=0.00..348.92 rows=11992 width=118) (actual 
                            time=0.010..2.271 rows=11992 loops=1)
  ->  Hash  (cost=106.32..106.32 rows=6132 width=27) (actual time=2.725..2.725 rows=6132 
             loops=1)
        Buckets: 8192  Batches: 1  Memory Usage: 439kB
        ->  Seq Scan on animals  (cost=0.00..106.32 rows=6132 width=27) (actual 
                                  time=0.009..1.008 rows=6132 loops=1)
Planning Time: 0.379 ms
Execution Time: 161.918 ms

一起來練習吧！

改進 PostgreSQL 的查詢效能