EXPLAIN 더 자세히 보기

PostgreSQL에서 쿼리 성능 개선하기

Amy McCarty

Instructor

EXPLAIN 선택 매개변수

VERBOSE

각 플랜 노드의 컬럼
테이블 스키마와 별칭 표시

ANALYZE

쿼리를 실행함
실제 실행 시간(ms) 표시

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에서 쿼리 성능 개선하기