En djupare titt på EXPLAIN

Förbättra frågeprestanda i PostgreSQL

Amy McCarty

Instructor

Valfria parametrar i EXPLAIN

VERBOSE

Kolumner för varje plannod
Visar tabellschema och alias

ANALYZE

Kör frågan
Faktiska körtider i millisekunder

VERBOSE

EXPLAIN VERBOSE
SELECT * FROM cheeses

Seq Scan on dairy.cheeses (cost=0.00..10.50 rows=5725 width=296) Output: name, species, type, age med dairy och Output understrukna

ANALYZE

EXPLAIN ANALYZE
SELECT * FROM cheeses

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296). Resterande delar är understrukna i rött. (actual time = 0.007..1.087 rows=11992 loops=1) Planning Time: 0.059 ms Execution Time: 1.538 ms

Mest användbart för att minimera körtiden

Frågeplan – aggregeringar

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

Frågeplan – sortering

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

Nu kör vi en övning!

Förbättra frågeprestanda i PostgreSQL