explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qRqy

Settings
# exclusive inclusive rows x rows loops node
1. 4.802 552.231 ↓ 0.0 0 1

ModifyTable (cost=4,791.08..14,896.66 rows=428,952 width=637) (actual time=552.231..552.231 rows=0 loops=1)

2.          

CTE loads

3. 0.575 506.437 ↓ 1.0 2,443 1

Unique (cost=3,277.82..3,326.62 rows=2,440 width=52) (actual time=505.326..506.437 rows=2,443 loops=1)

4. 2.951 505.862 ↓ 1.0 2,443 1

Sort (cost=3,277.82..3,283.92 rows=2,440 width=52) (actual time=505.325..505.862 rows=2,443 loops=1)

  • Sort Key: l_1.id, (tz_load.isp_get_id(l_1.id, 1000, 1706, '1815572751767831778'::bigint)), (tz_load.isp_get_id(l_1.id, 1001, 1706, '1815572751767831778'::bigint)), (tz_load.isp_get_id(l_1.id, 1000, 1707, '1815572751767831778'::bigint)), (tz_load.isp_get_id(l_1.id, 1001, 1707, '1815572751767831778'::bigint)), (tz_load.isp_get_posting_id(l_1.id, 1000, '1815572751767831778'::bigint)), l_1.status
  • Sort Method: quicksort Memory: 400kB
5. 502.911 502.911 ↓ 1.0 2,443 1

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

  • Filter: (status <> 250)
6. 5.897 547.429 ↑ 106.2 4,040 1

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

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

8. 3.423 531.289 ↓ 1.7 4,040 1

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

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

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

10. 1.161 520.756 ↓ 1.7 4,040 1

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

11. 1.532 510.053 ↑ 1.2 2,020 1

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

12. 507.214 507.214 ↓ 1.0 2,443 1

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

13. 0.522 1.307 ↑ 1.0 2,443 1

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

14. 0.785 0.785 ↑ 1.0 2,443 1

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

15. 4.232 9.542 ↑ 1.0 10,356 1

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

16. 1.030 5.310 ↑ 1.0 10,356 1

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

17. 0.041 0.041 ↑ 1.0 158 1

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

18. 0.008 0.008 ↑ 2.0 1 1

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

19. 0.022 0.022 ↑ 1.0 53 1

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

20. 3.221 3.221 ↑ 1.0 7,842 1

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

21. 0.054 0.054 ↑ 1.0 192 1

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

22. 0.012 0.012 ↓ 0.0 0 1

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

23. 0.917 0.917 ↑ 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.009..0.917 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. 2.905 5.852 ↑ 1.0 10,356 1

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

26. 0.930 2.947 ↑ 1.0 10,356 1

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

27. 0.042 0.042 ↑ 1.0 158 1

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

28. 0.014 0.014 ↑ 2.0 1 1

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

29. 0.014 0.014 ↑ 1.0 53 1

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

30. 1.599 1.599 ↑ 1.0 7,842 1

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

31. 0.041 0.041 ↑ 1.0 192 1

Seq Scan on shipper_business cb_4 (cost=0..5.92 rows=192 width=40) (actual time=0.006..0.041 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.303 0.303 ↑ 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.303 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)