explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rLyn

Settings
# exclusive inclusive rows x rows loops node
1. 1.542 597.666 ↓ 0.0 0 1

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

2.          

CTE loads

3. 547.040 547.040 ↓ 1.0 2,443 1

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

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

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

5. 5.431 594.501 ↑ 106.2 4,040 1

Sort (cost=295,082.47..296,154.85 rows=428,952 width=637) (actual time=593.893..594.501 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.626 589.070 ↑ 106.2 4,040 1

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

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

8. 4.712 574.667 ↓ 1.7 4,040 1

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

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

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

10. 1.958 563.375 ↓ 1.7 4,040 1

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

11. 4.101 554.902 ↑ 1.2 2,020 1

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

12. 549.296 549.296 ↓ 1.0 2,443 1

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

13. 0.463 1.505 ↑ 1.0 2,443 1

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

14. 1.042 1.042 ↑ 1.0 2,443 1

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

15. 2.819 6.515 ↑ 1.0 10,356 1

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

16. 0.800 3.696 ↑ 1.0 10,356 1

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

17. 0.071 0.071 ↑ 1.0 158 1

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

18. 0.005 0.005 ↑ 2.0 1 1

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

19. 0.082 0.082 ↑ 1.0 53 1

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

20. 2.304 2.304 ↑ 1.0 7,842 1

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

21. 0.026 0.026 ↑ 1.0 192 1

Seq Scan on shipper_business sb_4 (cost=0..5.92 rows=192 width=40) (actual time=0.008..0.026 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.402 0.402 ↑ 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.402 rows=2,110 loops=1)

24. 0.004 0.004 ↓ 0.0 0 1

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

25. 2.267 4.471 ↑ 1.0 10,356 1

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

26. 0.676 2.204 ↑ 1.0 10,356 1

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

27. 0.022 0.022 ↑ 1.0 158 1

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

28. 0.003 0.003 ↑ 2.0 1 1

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

29. 0.009 0.009 ↑ 1.0 53 1

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

30. 1.159 1.159 ↑ 1.0 7,842 1

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

31. 0.040 0.040 ↑ 1.0 192 1

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

32. 0.001 0.001 ↓ 0.0 0 1

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

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

34. 0.002 0.002 ↓ 0.0 0 1

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