explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5orN

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

Hash Left Join (cost=539.59..744.59 rows=3 width=1,680) (actual time=2.650..2.838 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.003 0.593 ↑ 1.5 2 1

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

3. 0.003 0.580 ↑ 1.5 2 1

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

4. 0.004 0.569 ↑ 1.5 2 1

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

5. 0.082 0.553 ↑ 1.5 2 1

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

6. 0.006 0.463 ↑ 1.5 2 1

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

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

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

8. 0.004 0.295 ↑ 1.5 2 1

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

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

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

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

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

11. 0.006 0.157 ↑ 1.5 2 1

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

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

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

13. 0.002 0.144 ↑ 1.5 2 1

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

14. 0.001 0.134 ↑ 1.5 2 1

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

15. 0.003 0.123 ↑ 1.5 2 1

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

16. 0.003 0.104 ↑ 1.5 2 1

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

17. 0.002 0.065 ↑ 1.5 2 1

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

18. 0.007 0.059 ↑ 1.5 2 1

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

19. 0.005 0.030 ↑ 1.5 2 1

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

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

21. 0.017 0.017 ↑ 1.0 1 1

Bitmap Index Scan on idx9_ord_pos (cost=0.00..4.43 rows=1 width=0) (actual time=0.017..0.017 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.036 0.036 ↑ 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.018..0.018 rows=1 loops=2)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
26. 0.016 0.016 ↑ 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.008..0.008 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.008 0.008 ↑ 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.004..0.004 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.007 0.048 ↑ 1.0 2 2

Materialize (cost=0.42..17.92 rows=2 width=86) (actual time=0.021..0.024 rows=2 loops=2)

31. 0.003 0.041 ↑ 1.0 2 1

Nested Loop Left Join (cost=0.42..17.91 rows=2 width=86) (actual time=0.036..0.041 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.009..0.010 rows=2 loops=1)

33. 0.028 0.028 ↑ 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.014..0.014 rows=1 loops=2)

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

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

35. 0.006 0.072 ↓ 0.0 0 2

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

36. 0.003 0.056 ↓ 0.0 0 2

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

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

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

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
  • Heap Fetches: 0
39. 0.010 0.010 ↑ 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.010..0.010 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.014 0.086 ↑ 1.0 19 2

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

41. 0.015 0.072 ↑ 1.0 19 1

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

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

Hash Left Join (cost=1.35..2.65 rows=19 width=25) (actual time=0.038..0.048 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.010 0.010 ↑ 1.0 19 1

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

44. 0.005 0.018 ↑ 1.0 14 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
45. 0.013 0.013 ↑ 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.011..0.013 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.024 0.024 ↓ 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.012 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.041 ↑ 1.0 1 1

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

53. 0.003 0.029 ↑ 1.0 1 1

Nested Loop (cost=0.71..16.75 rows=1 width=8) (actual time=0.028..0.029 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.017 0.017 ↑ 1.0 1 1

Index Scan using idx1_party on party party21_ (cost=0.42..8.44 rows=1 width=4) (actual time=0.017..0.017 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.010 0.010 ↑ 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.010..0.010 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.005..0.006 rows=0 loops=2)

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

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

  • Index Cond: (lan_id = party18_.p_lan_id)
59. 0.010 0.010 ↓ 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.005..0.005 rows=0 loops=2)

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

Hash (cost=426.21..426.21 rows=6 width=48) (actual time=2.186..2.186 rows=2 loops=1)

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

Subquery Scan on deliverysc27_ (cost=425.95..426.21 rows=6 width=48) (actual time=2.177..2.183 rows=2 loops=1)

62. 0.015 2.181 ↑ 3.0 2 1

GroupAggregate (cost=425.95..426.15 rows=6 width=48) (actual time=2.176..2.181 rows=2 loops=1)

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

Sort (cost=425.95..425.97 rows=6 width=24) (actual time=2.165..2.166 rows=16 loops=1)

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

Nested Loop (cost=49.14..425.87 rows=6 width=24) (actual time=1.860..2.154 rows=16 loops=1)

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

Nested Loop (cost=48.72..423.87 rows=4 width=28) (actual time=1.846..2.108 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.124 1.859 ↓ 8.9 124 1

Hash Join (cost=48.44..162.91 rows=14 width=20) (actual time=0.778..1.859 rows=124 loops=1)

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

Seq Scan on delivery_schedule_pos deliverysc1_ (cost=0.00..111.00 rows=887 width=12) (actual time=0.007..0.992 rows=887 loops=1)

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

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

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

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

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

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

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

Index Scan using idx_test1 on delivery_schedule_pos_classification deliverysc0_ (cost=0.28..1.97 rows=1 width=20) (actual time=0.001..0.002 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=16.62..16.62 rows=1 width=8) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Sort (cost=16.62..16.62 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..16.61 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

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

  • Index Cond: (dspc_dsp_id = deliverysc6_.dsp_id)
  • Filter: (dspc_del_date_from > CURRENT_TIMESTAMP)
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.034 ↓ 0.0 0 2

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

  • One-Time Filter: (country8_.co_id IS NOT NULL)
82. 0.030 0.030 ↓ 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.015..0.015 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.454 ms
Execution time : 3.594 ms