explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Cni

Settings
# exclusive inclusive rows x rows loops node
1. 3.082 675.823 ↓ 0.0 0 1

Insert on temp_load (cost=4,604.99..14,710.57 rows=428,952 width=637) (actual time=675.823..675.823 rows=0 loops=1)

2.          

CTE loads

3. 630.967 630.967 ↓ 1.0 2,443 1

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

  • Filter: (status <> 250)
4. 3.954 672.741 ↑ 106.2 4,040 1

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

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

6. 4.668 662.400 ↓ 1.7 4,040 1

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

  • Sort Key: l.posting_id
  • Sort Method: quicksort Memory: 1170kB
7. 2.570 657.732 ↓ 1.7 4,040 1

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

8. 2.049 647.667 ↓ 1.7 4,040 1

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

9. 4.107 638.390 ↑ 1.2 2,020 1

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

10. 633.138 633.138 ↓ 1.0 2,443 1

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

11. 0.391 1.145 ↑ 1.0 2,443 1

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

12. 0.754 0.754 ↑ 1.0 2,443 1

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

13. 3.299 7.228 ↑ 1.0 10,356 1

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

14. 0.807 3.929 ↑ 1.0 10,356 1

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

15. 0.034 0.034 ↑ 1.0 158 1

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

16. 0.017 0.017 ↑ 2.0 1 1

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

17. 0.019 0.019 ↑ 1.0 53 1

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

18. 2.318 2.318 ↑ 1.0 7,842 1

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

19. 0.097 0.097 ↑ 1.0 192 1

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

20. 0.005 0.005 ↓ 0.0 0 1

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

21. 0.616 0.616 ↑ 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.013..0.616 rows=2,110 loops=1)

22. 0.016 0.016 ↓ 0.0 0 1

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

23. 3.690 7.495 ↑ 1.0 10,356 1

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

24. 0.819 3.805 ↑ 1.0 10,356 1

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

25. 0.044 0.044 ↑ 1.0 158 1

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

26. 0.010 0.010 ↑ 2.0 1 1

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

27. 0.022 0.022 ↑ 1.0 53 1

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

28. 1.652 1.652 ↑ 1.0 7,842 1

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

29. 0.137 0.137 ↑ 1.0 192 1

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

30. 0.016 0.016 ↓ 0.0 0 1

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

31. 1.091 1.091 ↑ 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.021..1.091 rows=2,110 loops=1)

32. 0.014 0.014 ↓ 0.0 0 1

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

Planning time : 1.362 ms
Execution time : 676.654 ms