EXPLAIN’e daha derin bir bakış

PostgreSQL'de Sorgu Performansını İyileştirme

Amy McCarty

Instructor

EXPLAIN isteğe bağlı parametreler

VERBOSE

Her plan düğümü için sütunlar
Tablo şeması ve takma adları gösterir

ANALYZE

Sorguyu çalıştırır
Gerçek çalışma süreleri (ms)

VERBOSE

EXPLAIN VERBOSE
SELECT * FROM cheeses

dairy.cheeses üzerinde Seq Scan (cost=0.00..10.50 rows=5725 width=296) Çıktı: name, species, type, age; dairy ve Output kırmızıyla vurgulanmış

ANALYZE

EXPLAIN ANALYZE
SELECT * FROM cheeses

cheeses üzerinde Seq Scan (cost=0.00..10.50 rows=5725 width=296). Kalan bölümler kırmızıyla vurgulanmış. (actual time = 0.007..1.087 rows=11992 loops=1) Planning Time: 0.059 ms Execution Time: 1.538 ms

Çalışma süresini en aza indirmek için en kullanışlıdır

Sorgu planı - toplamalar

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

Sorgu planı - sıralama

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

Hadi pratik yapalım!

PostgreSQL'de Sorgu Performansını İyileştirme