explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AlBe

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 9,549.652 ↑ 1.5 2 1

Hash Left Join (cost=1,270.12..1,475.12 rows=3 width=1,680) (actual time=9,548.567..9,549.652 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.981 ↑ 1.5 2 1

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

3. 0.003 3.961 ↑ 1.5 2 1

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

4. 0.003 3.952 ↑ 1.5 2 1

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

5. 0.665 3.805 ↑ 1.5 2 1

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

6. 0.005 3.134 ↑ 1.5 2 1

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

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

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

8. 0.005 1.670 ↑ 1.5 2 1

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

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

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

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

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

11. 0.007 0.754 ↑ 1.5 2 1

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

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

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

13. 0.001 0.742 ↑ 1.5 2 1

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

14. 0.003 0.735 ↑ 1.5 2 1

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

15. 0.001 0.724 ↑ 1.5 2 1

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

16. 0.002 0.501 ↑ 1.5 2 1

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

17. 0.002 0.271 ↑ 1.5 2 1

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

18. 0.006 0.261 ↑ 1.5 2 1

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

19. 0.013 0.233 ↑ 1.5 2 1

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

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

21. 0.210 0.210 ↑ 1.0 1 1

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

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

Bitmap Index Scan on idx9_ord_pos (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 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.008 0.008 ↓ 0.0 0 2

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

  • Index Cond: (co_id = orderposit1_.opao_cumul_co_id)
25. 0.228 0.228 ↑ 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.114..0.114 rows=1 loops=2)

  • Index Cond: (oh_head_id = orderposit0_.op_cc_head_id)
26. 0.222 0.222 ↑ 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.111..0.111 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.006 0.006 ↑ 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.003..0.003 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.682 ↑ 1.0 2 2

Materialize (cost=0.42..17.92 rows=2 width=86) (actual time=0.338..0.341 rows=2 loops=2)

31. 0.003 0.677 ↑ 1.0 2 1

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

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

33. 0.530 0.530 ↑ 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.265..0.265 rows=1 loops=2)

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

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

35. 0.003 0.898 ↓ 0.0 0 2

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

36. 0.005 0.888 ↓ 0.0 0 2

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

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

  • Index Cond: (ass_cc_artnr = orderposit0_.op_cc_artnr)
38. 0.013 0.013 ↑ 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.013..0.013 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.010 0.554 ↑ 1.0 19 2

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

41. 0.013 0.544 ↑ 1.0 19 1

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

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

Hash Left Join (cost=1.35..2.65 rows=19 width=25) (actual time=0.517..0.523 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.218 0.218 ↑ 1.0 19 1

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

44. 0.005 0.287 ↑ 1.0 14 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
45. 0.282 0.282 ↑ 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.280..0.282 rows=14 loops=1)

46. 0.005 0.008 ↓ 1.1 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.003 0.003 ↓ 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.003 rows=12 loops=1)

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

Index Scan using idx1_party on party party18_ (cost=38.49..46.51 rows=1 width=73) (actual time=0.003..0.003 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.434 0.434 ↓ 0.0 0 2

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

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

53. 0.002 0.204 ↑ 1.0 1 1

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

54. 0.004 0.004 ↑ 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.004..0.004 rows=1 loops=1)

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

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

Index Scan using pk_country on country country24_ (cost=0.14..0.16 rows=1 width=19) (actual time=0.072..0.072 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.016 0.016 ↓ 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.008..0.008 rows=0 loops=2)

  • Index Cond: (reg_id = party18_.p_reg_id)
  • Heap Fetches: 0
60. 0.006 9,545.067 ↑ 2.5 2 1

Hash (cost=1,156.75..1,156.75 rows=5 width=48) (actual time=9,545.067..9,545.067 rows=2 loops=1)

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

Subquery Scan on deliverysc27_ (cost=1,156.54..1,156.75 rows=5 width=48) (actual time=9,545.057..9,545.061 rows=2 loops=1)

62. 0.018 9,545.060 ↑ 2.5 2 1

GroupAggregate (cost=1,156.54..1,156.70 rows=5 width=48) (actual time=9,545.056..9,545.060 rows=2 loops=1)

  • Group Key: deliverysc1_.dsp_op_cc_head_id, deliverysc1_.dsp_op_cc_pos_id
63. 0.013 9,545.042 ↓ 3.2 16 1

Sort (cost=1,156.54..1,156.55 rows=5 width=24) (actual time=9,545.041..9,545.042 rows=16 loops=1)

  • Sort Key: deliverysc1_.dsp_op_cc_head_id
  • Sort Method: quicksort Memory: 26kB
64. 0.072 9,545.029 ↓ 3.2 16 1

Nested Loop (cost=120.48..1,156.48 rows=5 width=24) (actual time=8.342..9,545.029 rows=16 loops=1)

  • Join Filter: ((deliverysc1_.dsp_op_cc_head_id = order3__1.oh_head_id) AND (companyent4_.cocom_cocam_id1 = order3__1.oh_cust_cocam_id))
  • Rows Removed by Join Filter: 1,158
65. 0.409 9,529.695 ↓ 3.7 1,174 1

Nested Loop (cost=120.19..989.94 rows=317 width=32) (actual time=3.155..9,529.695 rows=1,174 loops=1)

66. 7.273 9,526.938 ↓ 5.7 1,174 1

Hash Join (cost=119.77..720.11 rows=206 width=28) (actual time=3.141..9,526.938 rows=1,174 loops=1)

  • Hash Cond: (deliverysc0_.dspc_dsp_id = deliverysc1_.dsp_id)
  • 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))))
  • Rows Removed by Join Filter: 2,108
67. 1.606 12.115 ↓ 3.4 4,614 1

Nested Loop (cost=0.29..597.04 rows=1,363 width=28) (actual time=0.243..12.115 rows=4,614 loops=1)

68. 0.036 0.036 ↓ 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.036 rows=3 loops=1)

  • Index Cond: (cocom_cocam_id2 = 5)
69. 10.473 10.473 ↓ 1.1 1,538 3

Seq Scan on delivery_schedule_pos_classification deliverysc0_ (cost=0.00..575.11 rows=1,363 width=20) (actual time=0.080..3.491 rows=1,538 loops=3)

  • Filter: (dspc_status = 10)
  • Rows Removed by Filter: 16,533
70. 0.118 2.878 ↓ 1.0 886 1

Hash (cost=108.89..108.89 rows=848 width=12) (actual time=2.878..2.878 rows=886 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
71. 2.760 2.760 ↓ 1.0 886 1

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

  • Filter: (dsp_op_cc_pos_id = 1)
  • Rows Removed by Filter: 1,018
72.          

SubPlan (for Hash Join)

73. 2.188 9,504.672 ↓ 0.0 0 2,188

Limit (cost=668.29..668.29 rows=1 width=8) (actual time=4.343..4.344 rows=0 loops=2,188)

74. 4.376 9,502.484 ↓ 0.0 0 2,188

Sort (cost=668.29..668.29 rows=1 width=8) (actual time=4.343..4.343 rows=0 loops=2,188)

  • Sort Key: deliverysc5_.dspc_del_date_from
  • Sort Method: top-N heapsort Memory: 25kB
75. 32.686 9,498.108 ↓ 0.0 0 2,188

Nested Loop (cost=0.28..668.28 rows=1 width=8) (actual time=3.516..4.341 rows=0 loops=2,188)

  • Join Filter: (deliverysc5_.dspc_dsp_id = deliverysc6_.dsp_id)
  • Rows Removed by Join Filter: 313
76. 8.752 8.752 ↓ 3.0 3 2,188

Index Scan using fk1_delivery_schedule_pos on delivery_schedule_pos deliverysc6_ (cost=0.28..8.30 rows=1 width=4) (actual time=0.003..0.004 rows=3 loops=2,188)

  • 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))
77. 9,456.670 9,456.670 ↓ 3.5 120 5,714

Seq Scan on delivery_schedule_pos_classification deliverysc5_ (cost=0.00..659.56 rows=34 width=12) (actual time=1.453..1.655 rows=120 loops=5,714)

  • Filter: ((dspc_status = 10) AND (dspc_del_date_from > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 17,951
78. 2.348 2.348 ↑ 2.0 1 1,174

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

  • Index Cond: ((op_cc_head_id = deliverysc1_.dsp_op_cc_head_id) AND (op_cc_pos_id = 1))
  • Heap Fetches: 0
79. 15.262 15.262 ↑ 1.0 1 1,174

Index Scan using pk_ord_head on ord_head order3__1 (cost=0.29..0.51 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1,174)

  • Index Cond: (oh_head_id = orderposit2_.op_cc_head_id)
80.          

SubPlan (for Hash Left Join)

81. 0.004 0.562 ↓ 0.0 0 2

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

  • One-Time Filter: (country8_.co_id IS NOT NULL)
82. 0.558 0.558 ↓ 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.279..0.279 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 : 7.340 ms
Execution time : 9,549.943 ms