explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xtPY

Settings
# exclusive inclusive rows x rows loops node
1. 1.764 603.243 ↓ 0.0 0 1

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

2.          

CTE loads

3. 550.707 550.707 ↓ 1.0 2,443 1

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

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

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

5. 5.526 599.745 ↑ 106.2 4,040 1

Sort (cost=295,082.47..296,154.85 rows=428,952 width=637) (actual time=599.091..599.745 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.288 594.219 ↑ 106.2 4,040 1

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

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

8. 5.211 580.662 ↓ 1.7 4,040 1

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

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

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

10. 1.820 568.630 ↓ 1.7 4,040 1

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

11. 4.002 558.314 ↑ 1.2 2,020 1

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

12. 552.928 552.928 ↓ 1.0 2,443 1

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

13. 0.471 1.384 ↑ 1.0 2,443 1

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

14. 0.913 0.913 ↑ 1.0 2,443 1

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

15. 3.765 8.496 ↑ 1.0 10,356 1

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

16. 0.751 4.731 ↑ 1.0 10,356 1

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

17. 0.077 0.077 ↑ 1.0 158 1

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

18. 0.015 0.015 ↑ 2.0 1 1

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

19. 0.036 0.036 ↑ 1.0 53 1

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

20. 3.356 3.356 ↑ 1.0 7,842 1

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

21. 0.045 0.045 ↑ 1.0 192 1

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

22. 0.003 0.003 ↓ 0.0 0 1

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

23. 0.444 0.444 ↑ 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.006..0.444 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.179 4.689 ↑ 1.0 10,356 1

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

26. 0.743 2.510 ↑ 1.0 10,356 1

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

27. 0.026 0.026 ↑ 1.0 158 1

Seq Scan on business cb (cost=0..4.58 rows=158 width=39) (actual time=0.004..0.026 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.011 0.011 ↑ 1.0 53 1

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

30. 1.311 1.311 ↑ 1.0 7,842 1

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

31. 0.047 0.047 ↑ 1.0 192 1

Seq Scan on shipper_business cb_4 (cost=0..5.92 rows=192 width=40) (actual time=0.007..0.047 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.364 0.364 ↑ 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.364 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)