explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1e8Z : Optimization for: Optimization for: plan #pVOs; plan #hwMt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 139.330 3,495.904 ↓ 13.0 600 1

Unique (cost=8,604.44..8,604.78 rows=46 width=44) (actual time=3,228.539..3,495.904 rows=600 loops=1)

2. 267.677 3,356.574 ↓ 187.7 8,632 1

Sort (cost=8,604.44..8,604.55 rows=46 width=44) (actual time=3,228.514..3,356.574 rows=8,632 loops=1)

  • Sort Key: (count(r.inventory_id) OVER (?)) DESC, ((((cu.first_name)::text || ' '::text) || (cu.last_name)::text))
  • Sort Method: quicksort Memory: 1,058kB
3. 322.504 3,088.897 ↓ 187.7 8,632 1

WindowAgg (cost=8,602.13..8,603.17 rows=46 width=44) (actual time=2,622.649..3,088.897 rows=8,632 loops=1)

4. 313.317 2,766.393 ↓ 187.7 8,632 1

Sort (cost=8,602.13..8,602.25 rows=46 width=21) (actual time=2,622.251..2,766.393 rows=8,632 loops=1)

  • Sort Key: cu.customer_id
  • Sort Method: quicksort Memory: 1,057kB
5. 636.252 2,453.076 ↓ 187.7 8,632 1

Nested Loop Left Join (cost=8,216.45..8,600.86 rows=46 width=21) (actual time=672.893..2,453.076 rows=8,632 loops=1)

6. 462.983 1,385.224 ↓ 187.7 8,632 1

Hash Right Join (cost=8,216.17..8,586.80 rows=46 width=6) (actual time=672.838..1,385.224 rows=8,632 loops=1)

  • Hash Cond: (r.inventory_id = inv.inventory_id)
7. 249.465 249.465 ↑ 1.0 16,044 1

Seq Scan on rental r (cost=0.00..310.44 rows=16,044 width=6) (actual time=0.029..249.465 rows=16,044 loops=1)

8. 42.508 672.776 ↓ 54.2 2,494 1

Hash (cost=8,215.60..8,215.60 rows=46 width=4) (actual time=672.768..672.776 rows=2,494 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 120kB
9. 213.334 630.268 ↓ 54.2 2,494 1

Subquery Scan on inv (cost=76.50..8,215.60 rows=46 width=4) (actual time=36.442..630.268 rows=2,494 loops=1)

  • Filter: (inv.sf_string ~~ '%Behind the Scenes%'::text)
  • Rows Removed by Filter: 7,274
10. 304.664 416.934 ↑ 46.9 9,768 1

Hash Full Join (cost=76.50..2,489.35 rows=458,100 width=710) (actual time=36.399..416.934 rows=9,768 loops=1)

  • Hash Cond: (i.film_id = f.film_id)
11. 76.797 76.797 ↑ 1.0 4,581 1

Seq Scan on inventory i (cost=0.00..70.81 rows=4,581 width=6) (actual time=0.345..76.797 rows=4,581 loops=1)

12. 20.564 35.473 ↑ 1.0 1,000 1

Hash (cost=64.00..64.00 rows=1,000 width=63) (actual time=35.465..35.473 rows=1,000 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 104kB
13. 14.909 14.909 ↑ 1.0 1,000 1

Seq Scan on film f (cost=0.00..64.00 rows=1,000 width=63) (actual time=0.106..14.909 rows=1,000 loops=1)

14. 431.600 431.600 ↑ 1.0 1 8,632

Index Scan using customer_pkey on customer cu (cost=0.28..0.30 rows=1 width=17) (actual time=0.030..0.050 rows=1 loops=8,632)

  • Index Cond: (r.customer_id = customer_id)
Planning time : 0.866 ms
Execution time : 3,507.356 ms