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

Seq Scan on cheeses (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でクエリ性能を改善する