explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M5ed

Settings
# exclusive inclusive rows x rows loops node
1. 2.395 621.986 ↓ 0.0 0 1

ModifyTable (cost=298,222.97..312,163.91 rows=428,952 width=637) (actual time=621.986..621.986 rows=0 loops=1)

2.          

CTE loads

3. 560.377 560.377 ↓ 1.0 2,443 1

Seq Scan on load l_1 (cost=0..3,140.54 rows=2,440 width=52) (actual time=4.896..560.377 rows=2,443 loops=1)

  • Filter: (status <> 250)
4. 1.901 619.591 ↑ 212.4 2,020 1

Unique (cost=295,082.43..304,733.85 rows=428,952 width=637) (actual time=616.929..619.591 rows=2,020 loops=1)

5. 6.127 617.690 ↑ 106.2 4,040 1

Sort (cost=295,082.43..296,154.81 rows=428,952 width=637) (actual time=616.928..617.69 rows=4,040 loops=1)

  • Sort Key: l.load_id, l.shipper_user_id, l.carrier_user_id, sb.name, cb.name, sb.contact_email, cb.contact_email, p.internal_reference_id
  • Sort Method: quicksort Memory: 1170kB
6. 5.685 611.563 ↑ 106.2 4,040 1

Merge Join (cost=1,464.41..11,569.99 rows=428,952 width=637) (actual time=595.476..611.563 rows=4,040 loops=1)

7. 9.428 9.428 ↑ 1.0 34,960 1

Index Scan using idx_posting_id on posting p (cost=0.29..3,583.69 rows=35,160 width=524) (actual time=0.01..9.428 rows=34,960 loops=1)

8. 5.356 596.450 ↓ 1.7 4,040 1

Sort (cost=1,464.12..1,470.22 rows=2,440 width=128) (actual time=595.003..596.45 rows=4,040 loops=1)

  • Sort Key: l.posting_id
  • Sort Method: quicksort Memory: 1170kB
9. 2.160 591.094 ↓ 1.7 4,040 1

Hash Join (cost=1,141.24..1,326.84 rows=2,440 width=128) (actual time=25.727..591.094 rows=4,040 loops=1)

10. 1.905 580.825 ↓ 1.7 4,040 1

Hash Join (cost=628.1..777.19 rows=2,422 width=88) (actual time=17.426..580.825 rows=4,040 loops=1)

11. 4.254 570.285 ↑ 1.2 2,020 1

Hash Join (cost=114.97..227.82 rows=2,404 width=48) (actual time=8.767..570.285 rows=2,020 loops=1)

12. 562.813 562.813 ↓ 1.0 2,443 1

CTE Scan on loads l (cost=0..48.8 rows=2,440 width=52) (actual time=4.9..562.813 rows=2,443 loops=1)

13. 1.227 3.218 ↑ 1.0 2,443 1

Hash (cost=84.43..84.43 rows=2,443 width=16) (actual time=3.218..3.218 rows=2,443 loops=1)

14. 1.991 1.991 ↑ 1.0 2,443 1

Seq Scan on load lo (cost=0..84.43 rows=2,443 width=16) (actual time=0.026..1.991 rows=2,443 loops=1)

15. 3.949 8.635 ↑ 1.0 10,356 1

Hash (cost=383.06..383.06 rows=10,406 width=56) (actual time=8.635..8.635 rows=10,356 loops=1)

16. 1.015 4.686 ↑ 1.0 10,356 1

Append (cost=0..383.06 rows=10,406 width=56) (actual time=0.015..4.686 rows=10,356 loops=1)

17. 0.059 0.059 ↑ 1.0 158 1

Seq Scan on business sb (cost=0..4.58 rows=158 width=39) (actual time=0.015..0.059 rows=158 loops=1)

18. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on broker_business sb_1 (cost=0..1.01 rows=1 width=1,040) (actual time=0.012..0.013 rows=1 loops=1)

19. 0.027 0.027 ↑ 1.0 53 1

Seq Scan on business_history sb_2 (cost=0..2.53 rows=53 width=50) (actual time=0.012..0.027 rows=53 loops=1)

20. 2.946 2.946 ↑ 1.0 7,842 1

Seq Scan on carrier_business sb_3 (cost=0..271.42 rows=7,842 width=51) (actual time=0.011..2.946 rows=7,842 loops=1)

21. 0.052 0.052 ↑ 1.0 192 1

Seq Scan on shipper_business sb_4 (cost=0..5.92 rows=192 width=40) (actual time=0.01..0.052 rows=192 loops=1)

22. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on broker_business_history sb_5 (cost=0..10.2 rows=20 width=1,040) (actual time=0.004..0.004 rows=0 loops=1)

23. 0.565 0.565 ↑ 1.0 2,110 1

Seq Scan on carrier_business_history sb_6 (cost=0..77.1 rows=2,110 width=55) (actual time=0.007..0.565 rows=2,110 loops=1)

24. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on shipper_business_history sb_7 (cost=0..10.3 rows=30 width=1,040) (actual time=0.005..0.005 rows=0 loops=1)

25. 4.115 8.109 ↑ 1.0 10,356 1

Hash (cost=383.06..383.06 rows=10,406 width=56) (actual time=8.109..8.109 rows=10,356 loops=1)

26. 0.950 3.994 ↑ 1.0 10,356 1

Append (cost=0..383.06 rows=10,406 width=56) (actual time=0.011..3.994 rows=10,356 loops=1)

27. 0.055 0.055 ↑ 1.0 158 1

Seq Scan on business cb (cost=0..4.58 rows=158 width=39) (actual time=0.011..0.055 rows=158 loops=1)

28. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on broker_business cb_1 (cost=0..1.01 rows=1 width=1,040) (actual time=0.011..0.011 rows=1 loops=1)

29. 0.028 0.028 ↑ 1.0 53 1

Seq Scan on business_history cb_2 (cost=0..2.53 rows=53 width=50) (actual time=0.018..0.028 rows=53 loops=1)

30. 2.065 2.065 ↑ 1.0 7,842 1

Seq Scan on carrier_business cb_3 (cost=0..271.42 rows=7,842 width=51) (actual time=0.017..2.065 rows=7,842 loops=1)

31. 0.106 0.106 ↑ 1.0 192 1

Seq Scan on shipper_business cb_4 (cost=0..5.92 rows=192 width=40) (actual time=0.028..0.106 rows=192 loops=1)

32. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on broker_business_history cb_5 (cost=0..10.2 rows=20 width=1,040) (actual time=0.008..0.008 rows=0 loops=1)

33. 0.762 0.762 ↑ 1.0 2,110 1

Seq Scan on carrier_business_history cb_6 (cost=0..77.1 rows=2,110 width=55) (actual time=0.017..0.762 rows=2,110 loops=1)

34. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on shipper_business_history cb_7 (cost=0..10.3 rows=30 width=1,040) (actual time=0.009..0.009 rows=0 loops=1)