explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c74R

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 79.638 2,674.224 ↑ 1.0 1 1

Aggregate (cost=7,519,869.35..7,519,869.36 rows=1 width=0) (actual time=2,674.224..2,674.224 rows=1 loops=1)

2. 400.102 2,594.586 ↑ 321.8 872,983 1

Hash Left Join (cost=132,442.72..4,008,457.64 rows=280,912,937 width=8) (actual time=980.143..2,594.586 rows=872,983 loops=1)

  • Hash Cond: ((ie.invsmnid = s.smid) AND (ie.invsource = s.invoicesource))
3. 196.186 2,194.350 ↑ 85.8 872,983 1

Hash Left Join (cost=132,356.83..1,340,503.47 rows=74,910,120 width=16) (actual time=979.999..2,194.350 rows=872,983 loops=1)

  • Hash Cond: (ie.invportid = p.portno)
4. 111.995 1,997.865 ↑ 22.9 872,983 1

Merge Right Join (cost=132,224.91..441,573.94 rows=19,976,034 width=20) (actual time=979.688..1,997.865 rows=872,983 loops=1)

  • Merge Cond: (sn.sourceid = ie.invsource)
5. 0.008 0.015 ↑ 376.7 3 1

Sort (cost=78.60..81.43 rows=1,130 width=4) (actual time=0.014..0.015 rows=3 loops=1)

  • Sort Key: sn.sourceid
  • Sort Method: quicksort Memory: 25kB
6. 0.007 0.007 ↑ 376.7 3 1

Seq Scan on sourcename sn (cost=0.00..21.30 rows=1,130 width=4) (actual time=0.006..0.007 rows=3 loops=1)

7. 284.231 1,885.855 ↑ 4.0 872,983 1

Materialize (cost=132,146.31..150,688.15 rows=3,535,581 width=20) (actual time=979.672..1,885.855 rows=872,983 loops=1)

8. 277.660 1,601.624 ↑ 4.0 872,983 1

Merge Left Join (cost=132,146.31..141,849.20 rows=3,535,581 width=20) (actual time=979.671..1,601.624 rows=872,983 loops=1)

  • Merge Cond: ((ie.invsource = ris.invoicesource) AND (ie.invordsce = ris.invoiceordersource))
9. 974.251 1,254.709 ↑ 1.0 872,983 1

Sort (cost=131,281.02..133,463.48 rows=872,983 width=24) (actual time=979.612..1,254.709 rows=872,983 loops=1)

  • Sort Key: ie.invsource, ie.invordsce
  • Sort Method: external merge Disk: 29000kB
10. 280.458 280.458 ↑ 1.0 872,983 1

Seq Scan on invoiceelement ie (cost=0.00..27,230.83 rows=872,983 width=24) (actual time=0.076..280.458 rows=872,983 loops=1)

11. 69.219 69.255 ↓ 105.7 872,919 1

Sort (cost=865.29..885.94 rows=8,262 width=8) (actual time=0.054..69.255 rows=872,919 loops=1)

  • Sort Key: ris.invoicesource, ris.invoiceordersource
  • Sort Method: quicksort Memory: 26kB
12. 0.009 0.036 ↑ 306.0 27 1

Merge Right Join (cost=199.77..327.75 rows=8,262 width=8) (actual time=0.028..0.036 rows=27 loops=1)

  • Merge Cond: (rs.reportsourceid = ris.reportsourceid)
13. 0.005 0.015 ↑ 135.0 6 1

Sort (cost=57.23..59.26 rows=810 width=4) (actual time=0.015..0.015 rows=6 loops=1)

  • Sort Key: rs.reportsourceid
  • Sort Method: quicksort Memory: 25kB
14. 0.010 0.010 ↑ 135.0 6 1

Seq Scan on reportsource rs (cost=0.00..18.10 rows=810 width=4) (actual time=0.009..0.010 rows=6 loops=1)

15. 0.007 0.012 ↑ 75.6 27 1

Sort (cost=142.54..147.64 rows=2,040 width=12) (actual time=0.011..0.012 rows=27 loops=1)

  • Sort Key: ris.reportsourceid
  • Sort Method: quicksort Memory: 26kB
16. 0.005 0.005 ↑ 75.6 27 1

Seq Scan on reportinvoicesource ris (cost=0.00..30.40 rows=2,040 width=12) (actual time=0.002..0.005 rows=27 loops=1)

17. 0.050 0.299 ↑ 3.7 403 1

Hash (cost=113.03..113.03 rows=1,511 width=4) (actual time=0.299..0.299 rows=403 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 31kB
18. 0.083 0.249 ↑ 3.7 403 1

Merge Left Join (cost=78.78..113.03 rows=1,511 width=4) (actual time=0.139..0.249 rows=403 loops=1)

  • Merge Cond: (p.locationid = ((portlocation.locationid)::text))
19. 0.083 0.150 ↑ 1.0 403 1

Sort (cost=25.47..26.48 rows=403 width=5) (actual time=0.124..0.150 rows=403 loops=1)

  • Sort Key: p.locationid
  • Sort Method: quicksort Memory: 43kB
20. 0.067 0.067 ↑ 1.0 403 1

Seq Scan on port p (cost=0.00..8.03 rows=403 width=5) (actual time=0.004..0.067 rows=403 loops=1)

21. 0.011 0.016 ↑ 18.8 40 1

Sort (cost=53.32..55.19 rows=750 width=4) (actual time=0.013..0.016 rows=40 loops=1)

  • Sort Key: ((portlocation.locationid)::text)
  • Sort Method: quicksort Memory: 25kB
22. 0.005 0.005 ↑ 46.9 16 1

Seq Scan on reportlocation portlocation (cost=0.00..17.50 rows=750 width=4) (actual time=0.003..0.005 rows=16 loops=1)

23. 0.035 0.134 ↑ 3.8 285 1

Hash (cost=69.85..69.85 rows=1,069 width=8) (actual time=0.134..0.134 rows=285 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 28kB
24. 0.072 0.099 ↑ 3.8 285 1

Hash Left Join (cost=26.88..69.85 rows=1,069 width=8) (actual time=0.015..0.099 rows=285 loops=1)

  • Hash Cond: (s.locationid = salesmanlocation.locationid)
25. 0.021 0.021 ↑ 1.0 285 1

Seq Scan on salesman s (cost=0.00..4.85 rows=285 width=12) (actual time=0.005..0.021 rows=285 loops=1)

26. 0.002 0.006 ↑ 46.9 16 1

Hash (cost=17.50..17.50 rows=750 width=4) (actual time=0.006..0.006 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.004 0.004 ↑ 46.9 16 1

Seq Scan on reportlocation salesmanlocation (cost=0.00..17.50 rows=750 width=4) (actual time=0.002..0.004 rows=16 loops=1)