explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dki8

Settings
# exclusive inclusive rows x rows loops node
1. 1.134 524.082 ↓ 0.0 0 1

ModifyTable (cost=298,223.01..312,163.95 rows=428,952 width=637) (actual time=524.082..524.082 rows=0 loops=1)

2.          

CTE loads

3. 482.377 482.377 ↓ 1.0 2,443 1

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

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

Unique (cost=295,082.47..304,733.89 rows=428,952 width=637) (actual time=521.458..522.948 rows=2,020 loops=1)

5. 3.595 521.922 ↑ 106.2 4,040 1

Sort (cost=295,082.47..296,154.85 rows=428,952 width=637) (actual time=521.458..521.922 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. 3.573 518.327 ↑ 106.2 4,040 1

Merge Join (cost=1,464.46..11,570.04 rows=428,952 width=637) (actual time=508.67..518.327 rows=4,040 loops=1)

7. 5.627 5.627 ↑ 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.037..5.627 rows=34,960 loops=1)

8. 4.448 509.127 ↓ 1.7 4,040 1

Sort (cost=1,464.17..1,470.27 rows=2,440 width=128) (actual time=508.258..509.127 rows=4,040 loops=1)

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

Hash Join (cost=1,141.28..1,326.88 rows=2,440 width=128) (actual time=17.236..504.679 rows=4,040 loops=1)

10. 1.624 496.750 ↓ 1.7 4,040 1

Hash Join (cost=628.12..777.22 rows=2,422 width=88) (actual time=11.113..496.75 rows=4,040 loops=1)

11. 3.738 489.385 ↑ 1.2 2,020 1

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

12. 484.417 484.417 ↓ 1.0 2,443 1

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

13. 0.426 1.230 ↑ 1.0 2,443 1

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

14. 0.804 0.804 ↑ 1.0 2,443 1

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

15. 2.624 5.741 ↑ 1.0 10,356 1

Hash (cost=383.07..383.07 rows=10,407 width=56) (actual time=5.741..5.741 rows=10,356 loops=1)

16. 0.783 3.117 ↑ 1.0 10,356 1

Append (cost=0..383.07 rows=10,407 width=56) (actual time=0.007..3.117 rows=10,356 loops=1)

17. 0.025 0.025 ↑ 1.0 158 1

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

18. 0.002 0.002 ↑ 2.0 1 1

Seq Scan on broker_business sb_1 (cost=0..1.02 rows=2 width=1,040) (actual time=0.002..0.002 rows=1 loops=1)

19. 0.010 0.010 ↑ 1.0 53 1

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

20. 1.433 1.433 ↑ 1.0 7,842 1

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

21. 0.043 0.043 ↑ 1.0 192 1

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

22. 0.002 0.002 ↓ 0.0 0 1

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

23. 0.799 0.799 ↑ 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.005..0.799 rows=2,110 loops=1)

24. 0.020 0.020 ↓ 0.0 0 1

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

25. 2.834 5.831 ↑ 1.0 10,356 1

Hash (cost=383.07..383.07 rows=10,407 width=56) (actual time=5.83..5.831 rows=10,356 loops=1)

26. 0.793 2.997 ↑ 1.0 10,356 1

Append (cost=0..383.07 rows=10,407 width=56) (actual time=0.024..2.997 rows=10,356 loops=1)

27. 0.081 0.081 ↑ 1.0 158 1

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

28. 0.009 0.009 ↑ 2.0 1 1

Seq Scan on broker_business cb_1 (cost=0..1.02 rows=2 width=1,040) (actual time=0.009..0.009 rows=1 loops=1)

29. 0.023 0.023 ↑ 1.0 53 1

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

30. 1.632 1.632 ↑ 1.0 7,842 1

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

31. 0.039 0.039 ↑ 1.0 192 1

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

32. 0.002 0.002 ↓ 0.0 0 1

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

33. 0.414 0.414 ↑ 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.005..0.414 rows=2,110 loops=1)

34. 0.004 0.004 ↓ 0.0 0 1

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