explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R8aK

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 9.282 ↑ 1.5 2 1

Hash Left Join (cost=624.10..829.10 rows=3 width=1,680) (actual time=8.646..9.282 rows=2 loops=1)

  • Hash Cond: ((orderposit0_.op_cc_pos_id = deliverysc27_.positionid) AND (orderposit0_.op_cc_head_id = deliverysc27_.headid))
  • Join Filter: (order3_.oh_ord_type = 6)
2. 0.004 1.276 ↑ 1.5 2 1

Nested Loop Left Join (cost=113.29..293.14 rows=3 width=717) (actual time=0.656..1.276 rows=2 loops=1)

3. 0.004 1.264 ↑ 1.5 2 1

Nested Loop Left Join (cost=113.02..292.26 rows=3 width=705) (actual time=0.651..1.264 rows=2 loops=1)

4. 0.003 1.254 ↑ 1.5 2 1

Nested Loop Left Join (cost=112.89..291.81 rows=3 width=696) (actual time=0.646..1.254 rows=2 loops=1)

5. 0.078 1.227 ↑ 1.5 2 1

Nested Loop Left Join (cost=112.74..291.32 rows=3 width=681) (actual time=0.642..1.227 rows=2 loops=1)

6. 0.005 1.141 ↑ 1.5 2 1

Nested Loop Left Join (cost=74.25..151.81 rows=3 width=616) (actual time=0.601..1.141 rows=2 loops=1)

  • Join Filter: (handlingun13_.hutar_hut_id = handlingun15_.hut_id)
  • Rows Removed by Join Filter: 38
7. 0.003 1.058 ↑ 1.5 2 1

Nested Loop Left Join (cost=71.59..146.88 rows=3 width=574) (actual time=0.521..1.058 rows=2 loops=1)

8. 0.005 0.965 ↑ 1.5 2 1

Nested Loop Left Join (cost=70.46..95.19 rows=3 width=317) (actual time=0.492..0.965 rows=2 loops=1)

  • Join Filter: (handlingun13_.hutar_ar_id = orderposit0_.op_cc_artnr)
  • Rows Removed by Join Filter: 4
9. 0.055 0.902 ↑ 1.5 2 1

Hash Right Join (cost=70.03..77.19 rows=3 width=231) (actual time=0.445..0.902 rows=2 loops=1)

  • Hash Cond: (country8_.co_id = orderposit0_.op_origin_co_id)
10. 0.443 0.443 ↑ 1.0 255 1

Seq Scan on country country8_ (cost=0.00..5.55 rows=255 width=19) (actual time=0.011..0.443 rows=255 loops=1)

11. 0.006 0.404 ↑ 1.5 2 1

Hash (cost=70.00..70.00 rows=3 width=212) (actual time=0.404..0.404 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.003 0.398 ↑ 1.5 2 1

Nested Loop (cost=10.84..70.00 rows=3 width=212) (actual time=0.355..0.398 rows=2 loops=1)

13. 0.001 0.389 ↑ 1.5 2 1

Nested Loop (cost=10.56..68.43 rows=3 width=198) (actual time=0.348..0.389 rows=2 loops=1)

14. 0.001 0.378 ↑ 1.5 2 1

Nested Loop Left Join (cost=10.28..67.52 rows=3 width=194) (actual time=0.339..0.378 rows=2 loops=1)

15. 0.002 0.367 ↑ 1.5 2 1

Nested Loop Left Join (cost=9.99..65.95 rows=3 width=180) (actual time=0.331..0.367 rows=2 loops=1)

16. 0.003 0.339 ↑ 1.5 2 1

Nested Loop (cost=9.71..65.05 rows=3 width=176) (actual time=0.305..0.339 rows=2 loops=1)

17. 0.002 0.068 ↑ 1.5 2 1

Nested Loop Left Join (cost=9.42..40.12 rows=3 width=143) (actual time=0.045..0.068 rows=2 loops=1)

18. 0.007 0.062 ↑ 1.5 2 1

Nested Loop Left Join (cost=9.27..39.63 rows=3 width=128) (actual time=0.040..0.062 rows=2 loops=1)

19. 0.011 0.033 ↑ 1.5 2 1

Bitmap Heap Scan on ord_pos orderposit0_ (cost=8.86..20.68 rows=3 width=65) (actual time=0.026..0.033 rows=2 loops=1)

  • Recheck Cond: (((op_cc_head_id = 100,015,150) AND (op_cc_pos_id = 1)) OR ((op_cc_head_id = 100,015,158) AND (op_cc_pos_id = 1)))
  • Heap Blocks: exact=2
20. 0.000 0.022 ↓ 0.0 0 1

BitmapOr (cost=8.86..8.86 rows=3 width=0) (actual time=0.022..0.022 rows=0 loops=1)

21. 0.016 0.016 ↑ 1.0 1 1

Bitmap Index Scan on idx9_ord_pos (cost=0.00..4.43 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: ((op_cc_head_id = 100,015,150) AND (op_cc_pos_id = 1))
22. 0.006 0.006 ↑ 1.0 1 1

Bitmap Index Scan on idx9_ord_pos (cost=0.00..4.43 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((op_cc_head_id = 100,015,158) AND (op_cc_pos_id = 1))
23. 0.022 0.022 ↑ 2.0 1 2

Index Scan using pk_ord_pos_addon on ord_pos_addon orderposit1_ (cost=0.42..12.22 rows=2 width=71) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: ((opao_op_cc_head_id = orderposit0_.op_cc_head_id) AND (opao_op_cc_pos_id = orderposit0_.op_cc_pos_id) AND (opao_op_cc_pos_id = 1))
24. 0.004 0.004 ↓ 0.0 0 2

Index Scan using pk_country on country country2_ (cost=0.14..0.16 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=2)

  • Index Cond: (co_id = orderposit1_.opao_cumul_co_id)
25. 0.268 0.268 ↑ 1.0 1 2

Index Scan using idx1_ord_head on ord_head order3_ (cost=0.29..8.31 rows=1 width=37) (actual time=0.134..0.134 rows=1 loops=2)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
26. 0.026 0.026 ↑ 1.0 1 2

Index Only Scan using idx12_comp_cat_mtc on comp_cat_mtc companycat4_ (cost=0.28..0.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=2)

  • Index Cond: (cocam_id = order3_.oh_cust_cocam_id)
  • Heap Fetches: 0
27. 0.010 0.010 ↑ 1.0 1 2

Index Scan using pk_company on company company5_ (cost=0.29..0.52 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (cp_id = companycat4_.cocam_cp_id)
28. 0.010 0.010 ↑ 1.0 1 2

Index Only Scan using idx12_comp_cat_mtc on comp_cat_mtc companycat6_ (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (cocam_id = order3_.oh_cocam_id)
  • Heap Fetches: 0
29. 0.006 0.006 ↑ 1.0 1 2

Index Scan using pk_company on company company7_ (cost=0.29..0.52 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (cp_id = companycat6_.cocam_cp_id)
30. 0.018 0.058 ↑ 1.0 2 2

Materialize (cost=0.42..17.92 rows=2 width=86) (actual time=0.020..0.029 rows=2 loops=2)

31. 0.004 0.040 ↑ 1.0 2 1

Nested Loop Left Join (cost=0.42..17.91 rows=2 width=86) (actual time=0.036..0.040 rows=2 loops=1)

32. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on pck_handling_unit_type_article_restriction handlingun13_ (cost=0.00..1.02 rows=2 width=17) (actual time=0.010..0.010 rows=2 loops=1)

33. 0.026 0.026 ↑ 1.0 1 2

Index Scan using idx2_art_root on art_root articleroo14_ (cost=0.42..8.44 rows=1 width=73) (actual time=0.013..0.013 rows=1 loops=2)

  • Index Cond: (ar_cc_artnr = handlingun13_.hutar_ar_id)
34. 0.002 0.090 ↓ 0.0 0 2

Limit (cost=1.13..17.21 rows=1 width=5,545) (actual time=0.045..0.045 rows=0 loops=2)

35. 0.005 0.088 ↓ 0.0 0 2

Nested Loop (cost=1.13..17.21 rows=1 width=5,545) (actual time=0.044..0.044 rows=0 loops=2)

36. 0.004 0.072 ↓ 0.0 0 2

Nested Loop (cost=0.71..12.76 rows=1 width=261) (actual time=0.036..0.036 rows=0 loops=2)

  • Join Filter: (ccartstsup20_.ass_sup_id = order22_.oh_cocam_id)
37. 0.058 0.058 ↓ 0.0 0 2

Index Scan using pk_art_st_su on art_st_su ccartstsup20_ (cost=0.42..8.44 rows=1 width=257) (actual time=0.029..0.029 rows=0 loops=2)

  • Index Cond: (ass_cc_artnr = orderposit0_.op_cc_artnr)
38. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using idx1_ord_head on ord_head order22_ (cost=0.29..4.31 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
  • Heap Fetches: 0
39. 0.011 0.011 ↑ 1.0 1 1

Index Only Scan using idx9_ord_pos on ord_pos orderposit21_ (cost=0.42..4.44 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((op_cc_head_id = orderposit0_.op_cc_head_id) AND (op_cc_pos_id = orderposit0_.op_cc_pos_id))
  • Heap Fetches: 0
40. 0.011 0.078 ↑ 1.0 19 2

Materialize (cost=2.66..4.12 rows=19 width=46) (actual time=0.029..0.039 rows=19 loops=2)

41. 0.013 0.067 ↑ 1.0 19 1

Hash Left Join (cost=2.66..4.03 rows=19 width=46) (actual time=0.054..0.067 rows=19 loops=1)

  • Hash Cond: (handlingun15_.hut_id = localized17_.lhut_handling_unit_type_id)
42. 0.019 0.045 ↑ 1.0 19 1

Hash Left Join (cost=1.35..2.65 rows=19 width=25) (actual time=0.036..0.045 rows=19 loops=1)

  • Hash Cond: ((handlingun15_.hut_id = localized16_.lhut_handling_unit_type_id) AND (handlingun15_.hut_def_language = localized16_.lhut_language))
43. 0.009 0.009 ↑ 1.0 19 1

Seq Scan on pck_handling_unit_type handlingun15_ (cost=0.00..1.19 rows=19 width=8) (actual time=0.007..0.009 rows=19 loops=1)

44. 0.006 0.017 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=29) (actual time=0.017..0.017 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
45. 0.011 0.011 ↑ 1.0 14 1

Seq Scan on pck_localized_handling_unit_type localized16_ (cost=0.00..1.14 rows=14 width=29) (actual time=0.009..0.011 rows=14 loops=1)

46. 0.005 0.009 ↓ 1.1 12 1

Hash (cost=1.18..1.18 rows=11 width=25) (actual time=0.009..0.009 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.004 0.004 ↓ 1.1 12 1

Seq Scan on pck_localized_handling_unit_type localized17_ (cost=0.00..1.18 rows=11 width=25) (actual time=0.002..0.004 rows=12 loops=1)

  • Filter: (lhut_language = 1)
  • Rows Removed by Filter: 2
48. 0.000 0.008 ↑ 1.0 1 2

Index Scan using idx1_party on party party18_ (cost=38.49..46.51 rows=1 width=73) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: (p_id = COALESCE((SubPlan 2), (SubPlan 3), (SubPlan 4)))
49.          

SubPlan (for Index Scan)

50. 0.004 0.004 ↓ 0.0 0 2

Index Scan using idx1_party on party party19_ (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=2)

  • Index Cond: (p_id = orderposit0_.op_fdel_p_id)
  • Filter: ((p_street IS NOT NULL) AND (p_zip IS NOT NULL) AND (p_city IS NOT NULL))
51. 0.038 0.038 ↓ 0.0 0 2

Index Scan using idx1_party on party party20_ (cost=0.42..8.44 rows=1 width=4) (actual time=0.012..0.019 rows=0 loops=2)

  • Index Cond: (p_id = orderposit0_.op_del_p_id)
  • Filter: ((p_street IS NOT NULL) AND (p_zip IS NOT NULL) AND (p_city IS NOT NULL))
52. 0.001 0.034 ↑ 1.0 1 1

Nested Loop (cost=1.13..21.19 rows=1 width=4) (actual time=0.033..0.034 rows=1 loops=1)

53. 0.002 0.025 ↑ 1.0 1 1

Nested Loop (cost=0.71..16.75 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

54. 0.009 0.009 ↑ 1.0 1 1

Index Scan using idx1_ord_head on ord_head order23_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
55. 0.014 0.014 ↑ 1.0 1 1

Index Scan using idx1_party on party party21_ (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (p_id = order23_.oh_del_p_id)
  • Filter: ((p_street IS NOT NULL) AND (p_zip IS NOT NULL) AND (p_city IS NOT NULL))
56. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using idx9_ord_pos on ord_pos orderposit22_ (cost=0.42..4.44 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ((op_cc_head_id = orderposit0_.op_cc_head_id) AND (op_cc_pos_id = orderposit0_.op_cc_pos_id))
  • Heap Fetches: 0
57. 0.024 0.024 ↓ 0.0 0 2

Index Scan using pk_country on country country24_ (cost=0.14..0.16 rows=1 width=19) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: (co_id = party18_.p_co_id)
58. 0.006 0.006 ↓ 0.0 0 2

Index Scan using idx2_language on language language25_ (cost=0.13..0.15 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: (lan_id = party18_.p_lan_id)
59. 0.008 0.008 ↓ 0.0 0 2

Index Only Scan using idx2_region on region region26_ (cost=0.28..0.29 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=2)

  • Index Cond: (reg_id = party18_.p_reg_id)
  • Heap Fetches: 0
60. 0.004 7.949 ↑ 3.0 2 1

Hash (cost=510.72..510.72 rows=6 width=48) (actual time=7.948..7.949 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
61. 0.001 7.945 ↑ 3.0 2 1

Subquery Scan on deliverysc27_ (cost=510.46..510.72 rows=6 width=48) (actual time=7.941..7.945 rows=2 loops=1)

62. 0.015 7.944 ↑ 3.0 2 1

GroupAggregate (cost=510.46..510.66 rows=6 width=48) (actual time=7.940..7.944 rows=2 loops=1)

  • Group Key: deliverysc1_.dsp_op_cc_head_id, deliverysc1_.dsp_op_cc_pos_id
63. 0.012 7.929 ↓ 2.7 16 1

Sort (cost=510.46..510.48 rows=6 width=24) (actual time=7.929..7.929 rows=16 loops=1)

  • Sort Key: deliverysc1_.dsp_op_cc_head_id
  • Sort Method: quicksort Memory: 26kB
64. 0.006 7.917 ↓ 2.7 16 1

Nested Loop (cost=49.14..510.38 rows=6 width=24) (actual time=7.633..7.917 rows=16 loops=1)

  • Join Filter: (deliverysc1_.dsp_op_cc_head_id = orderposit2_.op_cc_head_id)
65. 0.088 7.879 ↓ 4.0 16 1

Nested Loop (cost=48.72..508.38 rows=4 width=28) (actual time=7.620..7.879 rows=16 loops=1)

  • Join Filter: (((companyent4_.cocom_dispatch_n_qty_periode > 0) AND (deliverysc0_.dspc_del_date_from <= ((CURRENT_TIMESTAMP + ((companyent4_.cocom_dispatch_n_qty_periode)::double precision * '7 days'::interval)) + (((1 * ((7 - (date_part('dow'::text, CURRENT_TIMESTAMP))::integer) + 1)))::double precision * '1 day'::interval)))) OR ((companyent4_.cocom_dispatch_n_qty_periode IS NULL) AND (deliverysc0_.dspc_del_date_from <= (SubPlan 5))))
66. 0.110 5.559 ↓ 9.5 124 1

Hash Join (cost=48.44..160.64 rows=13 width=20) (actual time=4.475..5.559 rows=124 loops=1)

  • Hash Cond: (deliverysc1_.dsp_op_cc_head_id = order3__1.oh_head_id)
67. 1.016 1.016 ↓ 1.0 886 1

Seq Scan on delivery_schedule_pos deliverysc1_ (cost=0.00..108.89 rows=848 width=12) (actual time=0.010..1.016 rows=886 loops=1)

  • Filter: (dsp_op_cc_pos_id = 1)
  • Rows Removed by Filter: 1,018
68. 0.268 4.433 ↓ 1.6 1,404 1

Hash (cost=37.75..37.75 rows=855 width=8) (actual time=4.433..4.433 rows=1,404 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 71kB
69. 0.136 4.165 ↓ 1.6 1,404 1

Nested Loop (cost=0.70..37.75 rows=855 width=8) (actual time=0.030..4.165 rows=1,404 loops=1)

70. 0.348 0.348 ↓ 3.0 3 1

Index Scan using idx9_comp_comp_mtc on comp_comp_mtc companyent4_ (cost=0.29..8.30 rows=1 width=8) (actual time=0.009..0.348 rows=3 loops=1)

  • Index Cond: (cocom_cocam_id2 = 5)
71. 3.681 3.681 ↑ 1.0 468 3

Index Only Scan using idx_oh_receipt_overview_cust on ord_head order3__1 (cost=0.41..24.71 rows=474 width=8) (actual time=0.010..1.227 rows=468 loops=3)

  • Index Cond: (oh_cust_cocam_id = companyent4_.cocom_cocam_id1)
  • Heap Fetches: 6
72. 2.232 2.232 ↓ 0.0 0 124

Index Scan using idx_test1 on delivery_schedule_pos_classification deliverysc0_ (cost=0.28..13.58 rows=1 width=20) (actual time=0.018..0.018 rows=0 loops=124)

  • Index Cond: (dspc_dsp_id = deliverysc1_.dsp_id)
73.          

SubPlan (for Nested Loop)

74. 0.000 0.000 ↓ 0.0 0

Limit (cost=13.12..13.12 rows=1 width=8) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Sort (cost=13.12..13.12 rows=1 width=8) (never executed)

  • Sort Key: deliverysc5_.dspc_del_date_from
76. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..13.11 rows=1 width=8) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Index Scan using fk1_delivery_schedule_pos on delivery_schedule_pos deliverysc6_ (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((dsp_op_cc_head_id = deliverysc1_.dsp_op_cc_head_id) AND (dsp_op_cc_pos_id = deliverysc1_.dsp_op_cc_pos_id))
78. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_test1 on delivery_schedule_pos_classification deliverysc5_ (cost=0.28..4.80 rows=1 width=12) (never executed)

  • Index Cond: ((dspc_del_date_from > CURRENT_TIMESTAMP) AND (dspc_dsp_id = deliverysc6_.dsp_id))
  • Heap Fetches: 0
79. 0.032 0.032 ↑ 2.0 1 16

Index Only Scan using pk_ord_pos on ord_pos orderposit2_ (cost=0.42..0.47 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=16)

  • Index Cond: ((op_cc_head_id = order3__1.oh_head_id) AND (op_cc_pos_id = 1))
  • Heap Fetches: 0
80.          

SubPlan (for Hash Left Join)

81. 0.004 0.030 ↓ 0.0 0 2

Result (cost=0.29..8.30 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=2)

  • One-Time Filter: (country8_.co_id IS NOT NULL)
82. 0.026 0.026 ↓ 0.0 0 2

Index Scan using pk_sup_dec_ctrl on sup_dec_ctrl ccsupdecct35_ (cost=0.29..8.30 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=2)

  • Index Cond: ((sdc_op_cc_head_id = orderposit0_.op_cc_head_id) AND (sdc_op_cc_pos_id = orderposit0_.op_cc_pos_id))
Planning time : 12.513 ms
Execution time : 10.025 ms