A deeper dive into EXPLAIN

Improving Query Performance in PostgreSQL

Amy McCarty

Instructor

EXPLAIN optional parameters

VERBOSE

Columns for each plan node
Shows table schema and aliases

ANALYZE

Runs the query
Actual run times in milliseconds

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 with the dairy and Output underlined

ANALYZE

EXPLAIN ANALYZE
SELECT * FROM cheeses

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296). The remaining sections are underlined in red. (actual time = 0.007..1.087 rows=11992 loops=1) Planning Time: 0.059 ms Execution Time: 1.538 ms

Most useful to minimize run time

Query plan - aggregations

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

Query plan - sort

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

Let's practice!

Improving Query Performance in PostgreSQL