explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wr1YF

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 8.708 ↑ 1.5 2 1

Hash Left Join (cost=935.53..1,140.53 rows=3 width=1,680) (actual time=7.871..8.708 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 3.870 ↑ 1.5 2 1

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

3. 0.003 3.858 ↑ 1.5 2 1

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

4. 0.003 3.849 ↑ 1.5 2 1

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

5. 0.592 3.834 ↑ 1.5 2 1

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

6. 0.006 3.234 ↑ 1.5 2 1

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

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

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

8. 0.004 1.776 ↑ 1.5 2 1

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

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

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

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

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

11. 0.005 1.030 ↑ 1.5 2 1

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

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

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

13. 0.002 1.019 ↑ 1.5 2 1

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

14. 0.003 1.013 ↑ 1.5 2 1

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

15. 0.002 1.002 ↑ 1.5 2 1

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

16. 0.004 0.776 ↑ 1.5 2 1

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

17. 0.002 0.254 ↑ 1.5 2 1

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

18. 0.005 0.248 ↑ 1.5 2 1

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

19. 0.011 0.227 ↑ 1.5 2 1

Bitmap Heap Scan on ord_pos orderposit0_ (cost=8.86..20.68 rows=3 width=65) (actual time=0.219..0.227 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.216 ↓ 0.0 0 1

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

21. 0.209 0.209 ↑ 1.0 1 1

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

  • Index Cond: ((op_cc_head_id = 100,015,150) AND (op_cc_pos_id = 1))
22. 0.007 0.007 ↑ 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.007 rows=1 loops=1)

  • Index Cond: ((op_cc_head_id = 100,015,158) AND (op_cc_pos_id = 1))
23. 0.016 0.016 ↑ 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.008..0.008 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.518 0.518 ↑ 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.259..0.259 rows=1 loops=2)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
26. 0.224 0.224 ↑ 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.112..0.112 rows=1 loops=2)

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

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

  • Index Cond: (cp_id = companycat4_.cocam_cp_id)
28. 0.004 0.004 ↑ 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.002..0.002 rows=1 loops=2)

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

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

  • Index Cond: (cp_id = companycat6_.cocam_cp_id)
30. 0.005 0.674 ↑ 1.0 2 2

Materialize (cost=0.42..17.92 rows=2 width=86) (actual time=0.333..0.337 rows=2 loops=2)

31. 0.003 0.669 ↑ 1.0 2 1

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

32. 0.160 0.160 ↑ 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.159..0.160 rows=2 loops=1)

33. 0.506 0.506 ↑ 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.253..0.253 rows=1 loops=2)

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

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

35. 0.005 0.952 ↓ 0.0 0 2

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

36. 0.002 0.940 ↓ 0.0 0 2

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

  • Join Filter: (ccartstsup20_.ass_sup_id = order22_.oh_cocam_id)
37. 0.930 0.930 ↓ 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.465..0.465 rows=0 loops=2)

  • Index Cond: (ass_cc_artnr = orderposit0_.op_cc_artnr)
38. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
  • Heap Fetches: 0
39. 0.007 0.007 ↑ 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.007..0.007 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.013 0.496 ↑ 1.0 19 2

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

41. 0.012 0.483 ↑ 1.0 19 1

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

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

Hash Left Join (cost=1.35..2.65 rows=19 width=25) (actual time=0.452..0.462 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.152 0.152 ↑ 1.0 19 1

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

44. 0.005 0.291 ↑ 1.0 14 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
45. 0.286 0.286 ↑ 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.284..0.286 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.354 0.354 ↓ 0.0 0 2

Index Scan using idx1_party on party party20_ (cost=0.42..8.44 rows=1 width=4) (actual time=0.176..0.177 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.002 0.221 ↑ 1.0 1 1

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

53. 0.002 0.211 ↑ 1.0 1 1

Nested Loop (cost=0.71..16.75 rows=1 width=8) (actual time=0.210..0.211 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.009..0.009 rows=1 loops=1)

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

Index Scan using idx1_party on party party21_ (cost=0.42..8.44 rows=1 width=4) (actual time=0.200..0.200 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.008..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.012 0.012 ↓ 0.0 0 2

Index Scan using pk_country on country country24_ (cost=0.14..0.16 rows=1 width=19) (actual time=0.006..0.006 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.005 4.287 ↑ 3.0 2 1

Hash (cost=822.14..822.14 rows=6 width=48) (actual time=4.287..4.287 rows=2 loops=1)

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

Subquery Scan on deliverysc27_ (cost=821.89..822.14 rows=6 width=48) (actual time=4.279..4.282 rows=2 loops=1)

62. 0.012 4.281 ↑ 3.0 2 1

GroupAggregate (cost=821.89..822.08 rows=6 width=48) (actual time=4.278..4.281 rows=2 loops=1)

  • Group Key: deliverysc1_.dsp_op_cc_head_id, deliverysc1_.dsp_op_cc_pos_id
63. 0.011 4.269 ↓ 2.7 16 1

Sort (cost=821.89..821.90 rows=6 width=24) (actual time=4.268..4.269 rows=16 loops=1)

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

Nested Loop (cost=49.15..821.81 rows=6 width=24) (actual time=4.097..4.258 rows=16 loops=1)

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

Nested Loop (cost=48.73..819.81 rows=4 width=28) (actual time=4.086..4.229 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.081 3.811 ↓ 8.9 124 1

Hash Join (cost=48.44..161.70 rows=14 width=20) (actual time=3.221..3.811 rows=124 loops=1)

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

Seq Scan on delivery_schedule_pos deliverysc1_ (cost=0.00..109.80 rows=886 width=12) (actual time=0.007..0.535 rows=886 loops=1)

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

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

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

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

70. 0.020 0.020 ↓ 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.006..0.020 rows=3 loops=1)

  • Index Cond: (cocom_cocam_id2 = 5)
71. 2.940 2.940 ↑ 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.007..0.980 rows=468 loops=3)

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

Index Scan using idx_test1 on delivery_schedule_pos_classification deliverysc0_ (cost=0.29..2.35 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=124)

  • Index Cond: (dspc_dsp_id = deliverysc1_.dsp_id)
  • Filter: (dspc_status = 10)
  • Rows Removed by Filter: 6
73.          

SubPlan (for Nested Loop)

74. 0.000 0.000 ↓ 0.0 0

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

75. 0.000 0.000 ↓ 0.0 0

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

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

Nested Loop (cost=4.63..44.60 rows=1 width=8) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_test2 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

Bitmap Heap Scan on delivery_schedule_pos_classification deliverysc5_ (cost=4.36..36.29 rows=1 width=12) (never executed)

  • Recheck Cond: (dspc_dsp_id = deliverysc6_.dsp_id)
  • Filter: ((dspc_status = 10) AND (dspc_del_date_from > CURRENT_TIMESTAMP))
79. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_test1 (cost=0.00..4.35 rows=9 width=0) (never executed)

  • Index Cond: (dspc_dsp_id = deliverysc6_.dsp_id)
80. 0.016 0.016 ↑ 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.001..0.001 rows=1 loops=16)

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

SubPlan (for Hash Left Join)

82. 0.002 0.526 ↓ 0.0 0 2

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

  • One-Time Filter: (country8_.co_id IS NOT NULL)
83. 0.524 0.524 ↓ 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.262..0.262 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 : 8.447 ms
Execution time : 9.275 ms