explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QynN

Settings
# exclusive inclusive rows x rows loops node
1. 0.085 826.576 ↓ 32.0 32 1

Sort (cost=887.97..887.97 rows=1 width=162) (actual time=826.570..826.576 rows=32 loops=1)

  • Sort Key: cap_filtered_windows.start_time, ((cap_filtered_windows.stop_time - cap_filtered_windows.start_time)) DESC, cap_filtered_windows.cutoff_time
  • Sort Method: quicksort Memory: 33kB
2.          

CTE holiday_dates

3. 0.011 0.037 ↑ 1.0 12 1

HashAggregate (cost=2.40..2.52 rows=12 width=4) (actual time=0.033..0.037 rows=12 loops=1)

  • Group Key: (make_date((date_part('year'::text, now()))::integer, holidays.month, holidays.day_of_month))
4. 0.006 0.026 ↑ 1.0 12 1

Append (cost=0.00..2.38 rows=12 width=4) (actual time=0.011..0.026 rows=12 loops=1)

5. 0.014 0.014 ↑ 1.0 6 1

Seq Scan on holidays (cost=0.00..1.12 rows=6 width=4) (actual time=0.011..0.014 rows=6 loops=1)

6. 0.006 0.006 ↑ 1.0 6 1

Seq Scan on holidays holidays_1 (cost=0.00..1.14 rows=6 width=4) (actual time=0.004..0.006 rows=6 loops=1)

7.          

CTE next_14_days

8. 0.023 0.033 ↑ 66.7 15 1

Result (cost=0.00..37.52 rows=1,000 width=8) (actual time=0.011..0.033 rows=15 loops=1)

9. 0.009 0.010 ↑ 66.7 15 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=4) (actual time=0.004..0.010 rows=15 loops=1)

10. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

11.          

CTE delivery_schedules

12. 0.042 1.230 ↓ 13.0 39 1

Nested Loop (cost=88.11..277.88 rows=3 width=54) (actual time=0.223..1.230 rows=39 loops=1)

13. 0.054 1.149 ↓ 13.0 39 1

Nested Loop (cost=87.98..277.42 rows=3 width=70) (actual time=0.211..1.149 rows=39 loops=1)

14. 0.058 1.056 ↓ 13.0 39 1

Nested Loop (cost=87.84..276.95 rows=3 width=82) (actual time=0.186..1.056 rows=39 loops=1)

15. 0.180 0.374 ↓ 6.5 39 1

Hash Join (cost=87.14..98.75 rows=6 width=44) (actual time=0.121..0.374 rows=39 loops=1)

  • Hash Cond: ((generic_intervals.id = mapped_intervals.between_interval_id) AND (generic_intervals.day_of_week = mapped_intervals.day_of_week))
16. 0.133 0.133 ↓ 1.1 535 1

Seq Scan on generic_between_intervals generic_intervals (cost=0.00..8.99 rows=499 width=24) (actual time=0.006..0.133 rows=535 loops=1)

17. 0.019 0.061 ↑ 1.0 39 1

Hash (cost=86.55..86.55 rows=39 width=36) (actual time=0.061..0.061 rows=39 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 0.030 0.042 ↑ 1.0 39 1

Bitmap Heap Scan on vendor_mapping_between_intervals mapped_intervals (cost=4.59..86.55 rows=39 width=36) (actual time=0.017..0.042 rows=39 loops=1)

  • Recheck Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Heap Blocks: exact=10
19. 0.012 0.012 ↑ 1.0 39 1

Bitmap Index Scan on vendor_mapping_between_interv_vendor_store_id_between_inter_key (cost=0.00..4.58 rows=39 width=0) (actual time=0.012..0.012 rows=39 loops=1)

  • Index Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
20. 0.078 0.624 ↑ 3.0 1 39

Nested Loop (cost=0.70..29.67 rows=3 width=50) (actual time=0.015..0.016 rows=1 loops=39)

21. 0.195 0.195 ↑ 1.0 3 39

Index Only Scan using zip_code_idx on zip_code_area_pairing zip_codes (cost=0.28..4.33 rows=3 width=8) (actual time=0.004..0.005 rows=3 loops=39)

  • Index Cond: (postnr = '11120'::text)
  • Heap Fetches: 0
22. 0.351 0.351 ↓ 0.0 0 117

Index Scan using unique_prices_per_vendor_and_interval on between_pricing_per_zone zone_pricing (cost=0.41..8.43 rows=1 width=42) (actual time=0.003..0.003 rows=0 loops=117)

  • Index Cond: ((interval_id = mapped_intervals.id) AND (zone_id = zip_codes.zone_id))
23. 0.039 0.039 ↑ 1.0 1 39

Index Only Scan using areas_pkey on areas (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=39)

  • Index Cond: (id = zone_pricing.zone_id)
  • Heap Fetches: 0
24. 0.039 0.039 ↑ 1.0 1 39

Index Only Scan using price_id_idx on price_categories (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=39)

  • Index Cond: (id = zone_pricing.price_category_id)
  • Heap Fetches: 0
25.          

CTE valid_delivery_days

26. 0.077 1.407 ↓ 5.7 85 1

Hash Join (cost=0.10..26.50 rows=15 width=82) (actual time=1.322..1.407 rows=85 loops=1)

  • Hash Cond: (date_part('dow'::text, next_14_days.day) = (delivery_schedules.day_of_week)::double precision)
27. 0.042 0.042 ↑ 66.7 15 1

CTE Scan on next_14_days (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.012..0.042 rows=15 loops=1)

28. 0.023 1.288 ↓ 13.0 39 1

Hash (cost=0.06..0.06 rows=3 width=78) (actual time=1.288..1.288 rows=39 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
29. 1.265 1.265 ↓ 13.0 39 1

CTE Scan on delivery_schedules (cost=0.00..0.06 rows=3 width=78) (actual time=0.225..1.265 rows=39 loops=1)

30.          

CTE delivery_windows

31. 1.699 1.699 ↓ 5.7 85 1

CTE Scan on valid_delivery_days (cost=0.00..1.54 rows=15 width=74) (actual time=1.336..1.699 rows=85 loops=1)

32.          

CTE holiday_filtered_delivery_windows

33. 1.800 1.844 ↓ 10.6 85 1

CTE Scan on delivery_windows (cost=0.27..0.65 rows=8 width=74) (actual time=1.391..1.844 rows=85 loops=1)

  • Filter: (NOT (hashed SubPlan 6))
34.          

SubPlan (for CTE Scan)

35. 0.044 0.044 ↑ 1.0 12 1

CTE Scan on holiday_dates (cost=0.00..0.24 rows=12 width=4) (actual time=0.034..0.044 rows=12 loops=1)

36.          

CTE working_schedules

37. 0.244 0.244 ↓ 1.2 7 1

Seq Scan on vendor_store_work_schedule (cost=0.00..67.75 rows=6 width=40) (actual time=0.124..0.244 rows=7 loops=1)

  • Filter: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Rows Removed by Filter: 3,180
38.          

CTE valid_working_days

39. 0.029 0.289 ↑ 2.0 15 1

Hash Join (cost=0.20..26.75 rows=30 width=24) (actual time=0.272..0.289 rows=15 loops=1)

  • Hash Cond: (date_part('dow'::text, next_14_days_1.day) = (working_schedules.day_of_schedule)::double precision)
40. 0.005 0.005 ↑ 66.7 15 1

CTE Scan on next_14_days next_14_days_1 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.000..0.005 rows=15 loops=1)

41. 0.006 0.255 ↓ 1.2 7 1

Hash (cost=0.12..0.12 rows=6 width=20) (actual time=0.254..0.255 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.249 0.249 ↓ 1.2 7 1

CTE Scan on working_schedules (cost=0.00..0.12 rows=6 width=20) (actual time=0.125..0.249 rows=7 loops=1)

43.          

CTE working_windows

44. 0.327 0.327 ↑ 2.0 15 1

CTE Scan on valid_working_days (cost=0.00..2.25 rows=30 width=16) (actual time=0.279..0.327 rows=15 loops=1)

45.          

CTE holiday_filtered_working_windows

46. 0.356 0.360 ↑ 1.0 15 1

CTE Scan on working_windows (cost=0.27..1.02 rows=15 width=16) (actual time=0.298..0.360 rows=15 loops=1)

  • Filter: (NOT (hashed SubPlan 11))
47.          

SubPlan (for CTE Scan)

48. 0.004 0.004 ↑ 1.0 12 1

CTE Scan on holiday_dates holiday_dates_1 (cost=0.00..0.24 rows=12 width=4) (actual time=0.000..0.004 rows=12 loops=1)

49.          

CTE intersected_days

50. 0.039 2.402 ↓ 1.9 15 1

HashSetOp Intersect (cost=0.00..0.80 rows=8 width=8) (actual time=2.399..2.402 rows=15 loops=1)

51. 0.042 2.363 ↓ 4.3 100 1

Append (cost=0.00..0.75 rows=23 width=8) (actual time=1.395..2.363 rows=100 loops=1)

52. 0.037 1.943 ↓ 10.6 85 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.26 rows=8 width=8) (actual time=1.394..1.943 rows=85 loops=1)

53. 1.906 1.906 ↓ 10.6 85 1

CTE Scan on holiday_filtered_delivery_windows (cost=0.00..0.18 rows=8 width=4) (actual time=1.394..1.906 rows=85 loops=1)

54. 0.008 0.378 ↑ 1.0 15 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.49 rows=15 width=8) (actual time=0.300..0.378 rows=15 loops=1)

55. 0.370 0.370 ↑ 1.0 15 1

CTE Scan on holiday_filtered_working_windows (cost=0.00..0.34 rows=15 width=4) (actual time=0.299..0.370 rows=15 loops=1)

56.          

CTE joined_schedules

57. 0.236 10.606 ↓ 12.0 84 1

Unique (cost=19.35..19.40 rows=7 width=90) (actual time=10.194..10.606 rows=84 loops=1)

58. 0.599 10.370 ↓ 94.4 661 1

Sort (cost=19.35..19.37 rows=7 width=90) (actual time=10.193..10.370 rows=661 loops=1)

  • Sort Key: delivering.start_time, delivering.stop_time, working.opening_time DESC
  • Sort Method: quicksort Memory: 117kB
59. 0.764 9.771 ↓ 94.4 661 1

Hash Semi Join (cost=0.52..19.25 rows=7 width=90) (actual time=2.482..9.771 rows=661 loops=1)

  • Hash Cond: ((delivering.start_time)::date = intersected_days.value)
60. 0.935 6.589 ↓ 94.4 661 1

Nested Loop (cost=0.26..18.80 rows=7 width=90) (actual time=0.041..6.589 rows=661 loops=1)

  • Join Filter: ((((delivering.start_time)::date - (working.opening_time)::date) >= delivering.delivery_offset_in_days) AND ((SubPlan 14) >= (delivering.delivery_offset_in_days + 1)))
  • Rows Removed by Join Filter: 614
61. 0.026 0.026 ↓ 10.6 85 1

CTE Scan on holiday_filtered_delivery_windows delivering (cost=0.00..0.16 rows=8 width=74) (actual time=0.000..0.026 rows=85 loops=1)

62. 0.302 0.340 ↓ 1.9 15 85

Materialize (cost=0.26..0.74 rows=8 width=16) (actual time=0.001..0.004 rows=15 loops=85)

63. 0.023 0.038 ↓ 1.9 15 1

Hash Semi Join (cost=0.26..0.70 rows=8 width=16) (actual time=0.023..0.038 rows=15 loops=1)

  • Hash Cond: ((working.opening_time)::date = intersected_days_1.value)
64. 0.004 0.004 ↑ 1.0 15 1

CTE Scan on holiday_filtered_working_windows working (cost=0.00..0.30 rows=15 width=16) (actual time=0.000..0.004 rows=15 loops=1)

65. 0.006 0.011 ↓ 1.9 15 1

Hash (cost=0.16..0.16 rows=8 width=4) (actual time=0.011..0.011 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
66. 0.005 0.005 ↓ 1.9 15 1

CTE Scan on intersected_days intersected_days_1 (cost=0.00..0.16 rows=8 width=4) (actual time=0.001..0.005 rows=15 loops=1)

67.          

SubPlan (for Nested Loop)

68. 1.322 5.288 ↑ 1.0 1 661

Aggregate (cost=0.24..0.25 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=661)

69. 3.966 3.966 ↓ 6.0 6 661

CTE Scan on intersected_days id (cost=0.00..0.24 rows=1 width=0) (actual time=0.001..0.006 rows=6 loops=661)

  • Filter: ((value >= (working.opening_time)::date) AND (value <= (delivering.start_time)::date))
  • Rows Removed by Filter: 9
70. 0.007 2.418 ↓ 1.9 15 1

Hash (cost=0.16..0.16 rows=8 width=4) (actual time=2.418..2.418 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 2.411 2.411 ↓ 1.9 15 1

CTE Scan on intersected_days (cost=0.00..0.16 rows=8 width=4) (actual time=2.400..2.411 rows=15 loops=1)

72.          

CTE vendor_constraints

73. 0.009 0.009 ↑ 1.0 1 1

Index Scan using vendor_stores_flags_store_idx on vendor_stores_flags (cost=0.28..8.29 rows=1 width=1) (actual time=0.007..0.009 rows=1 loops=1)

  • Index Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
74.          

CTE consolidation_stores

75. 0.011 0.107 ↑ 22.3 9 1

HashAggregate (cost=9.49..11.50 rows=201 width=16) (actual time=0.103..0.107 rows=9 loops=1)

  • Group Key: ('5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
76. 0.005 0.096 ↑ 20.1 10 1

Append (cost=0.00..8.98 rows=201 width=16) (actual time=0.001..0.096 rows=10 loops=1)

77. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.001 rows=1 loops=1)

78. 0.005 0.090 ↑ 22.2 9 1

ProjectSet (cost=5.91..6.96 rows=200 width=16) (actual time=0.087..0.090 rows=9 loops=1)

79. 0.003 0.085 ↑ 2.0 1 1

Unique (cost=5.91..5.93 rows=2 width=168) (actual time=0.084..0.085 rows=1 loops=1)

80. 0.032 0.082 ↑ 3.0 1 1

Sort (cost=5.91..5.92 rows=3 width=168) (actual time=0.081..0.082 rows=1 loops=1)

  • Sort Key: store_consolidation_groups.store_ids
  • Sort Method: quicksort Memory: 25kB
81. 0.020 0.050 ↑ 3.0 1 1

Hash Semi Join (cost=4.37..5.89 rows=3 width=168) (actual time=0.047..0.050 rows=1 loops=1)

  • Hash Cond: (store_consolidation_groups.zone_id = zip_code_area_pairing.zone_id)
82. 0.011 0.011 ↑ 1.0 19 1

Seq Scan on store_consolidation_groups (cost=0.00..1.43 rows=19 width=172) (actual time=0.004..0.011 rows=19 loops=1)

  • Filter: ('5647cca0-c8a1-11e8-b810-777ae174f124'::uuid = ANY (store_ids))
83. 0.004 0.019 ↑ 1.0 3 1

Hash (cost=4.33..4.33 rows=3 width=8) (actual time=0.019..0.019 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
84. 0.015 0.015 ↑ 1.0 3 1

Index Only Scan using zip_code_idx on zip_code_area_pairing (cost=0.28..4.33 rows=3 width=8) (actual time=0.013..0.015 rows=3 loops=1)

  • Index Cond: (postnr = '11120'::text)
  • Heap Fetches: 0
85.          

CTE post_filter_delivery_time_windows

86. 3.410 825.795 ↓ 12.0 84 1

Nested Loop (cost=0.00..402.98 rows=7 width=140) (actual time=11.720..825.795 rows=84 loops=1)

87. 0.011 0.011 ↑ 1.0 1 1

CTE Scan on vendor_constraints vc (cost=0.00..0.02 rows=1 width=1) (actual time=0.008..0.011 rows=1 loops=1)

88. 10.682 10.682 ↓ 12.0 84 1

CTE Scan on joined_schedules (cost=0.00..0.14 rows=7 width=90) (actual time=10.195..10.682 rows=84 loops=1)

89.          

SubPlan (for Nested Loop)

90. 0.294 156.240 ↑ 1.0 1 84

Aggregate (cost=16.98..16.99 rows=1 width=8) (actual time=1.860..1.860 rows=1 loops=84)

91.          

Initplan (for Aggregate)

92. 0.042 0.042 ↑ 1.0 1 14

Index Scan using areas_pkey on areas areas_1 (cost=0.14..8.16 rows=1 width=11,663) (actual time=0.002..0.003 rows=1 loops=14)

  • Index Cond: (id = joined_schedules.zone_id)
93. 155.904 155.904 ↓ 3.0 3 84

Index Scan using orders_stores_dropoff_window_idx on orders_stores del (cost=0.55..8.82 rows=1 width=0) (actual time=0.039..1.856 rows=3 loops=84)

  • Index Cond: ((dropoff_earliest_time = (date_part('epoch'::text, joined_schedules.start_time))::bigint) AND (dropoff_latest_time = (date_part('epoch'::text, joined_schedules.stop_time))::bigint))
  • Filter: ((sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid) AND st_within(geom, $23))
  • Rows Removed by Filter: 82
94. 0.840 143.304 ↑ 1.0 1 84

Aggregate (cost=8.58..8.59 rows=1 width=8) (actual time=1.706..1.706 rows=1 loops=84)

95. 142.464 142.464 ↓ 32.0 32 84

Index Scan using orders_stores_dropoff_window_idx on orders_stores (cost=0.55..8.57 rows=1 width=0) (actual time=0.004..1.696 rows=32 loops=84)

  • Index Cond: ((dropoff_earliest_time = (date_part('epoch'::text, joined_schedules.start_time))::bigint) AND (dropoff_latest_time = (date_part('epoch'::text, joined_schedules.stop_time))::bigint))
  • Filter: (sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Rows Removed by Filter: 53
96. 66.875 512.148 ↓ 115.0 115 84

Nested Loop Semi Join (cost=8.86..31.96 rows=1 width=6) (actual time=0.269..6.097 rows=115 loops=84)

  • Join Filter: (del_1.sender_store_id = consolidation_stores.id)
  • Rows Removed by Join Filter: 2,469
97.          

Initplan (for Nested Loop Semi Join)

98. 0.069 0.069 ↑ 1.0 1 23

Index Scan using areas_pkey on areas areas_2 (cost=0.14..8.16 rows=1 width=11,663) (actual time=0.002..0.003 rows=1 loops=23)

  • Index Cond: (id = joined_schedules.zone_id)
99. 13.496 395.304 ↓ 297.0 297 84

Nested Loop (cost=0.70..17.27 rows=1 width=22) (actual time=0.018..4.706 rows=297 loops=84)

100. 172.200 172.200 ↓ 9.0 9 84

Index Scan using orders_stores_dropoff_window_idx on orders_stores del_1 (cost=0.55..8.82 rows=1 width=48) (actual time=0.012..2.050 rows=9 loops=84)

  • Index Cond: ((dropoff_earliest_time = (date_part('epoch'::text, joined_schedules.start_time))::bigint) AND (dropoff_latest_time = (date_part('epoch'::text, joined_schedules.stop_time))::bigint))
  • Filter: st_within(geom, $29)
  • Rows Removed by Filter: 75
101. 209.608 209.608 ↓ 32.0 32 788

Index Scan using zip_codes_geom_idx on zip_codes zc (cost=0.15..8.43 rows=1 width=2,608) (actual time=0.019..0.266 rows=32 loops=788)

  • Index Cond: (geom && st_expand(del_1.geom, '0.00899999999999999932'::double precision))
  • Filter: ((del_1.geom && st_expand(geom, '0.00899999999999999932'::double precision)) AND (postnr <> ALL ('{18494,18593,19491,17893,14633,13567,19492}'::text[])) AND _st_dwithin(del_1.geom, geom, '0.00899999999999999932'::double precision))
  • Rows Removed by Filter: 7
102. 49.900 49.900 ↑ 22.3 9 24,950

CTE Scan on consolidation_stores (cost=0.00..4.02 rows=201 width=16) (actual time=0.000..0.002 rows=9 loops=24,950)

103.          

CTE cap_filtered_windows

104. 825.969 825.969 ↓ 20.5 82 1

CTE Scan on post_filter_delivery_time_windows (cost=0.00..0.28 rows=4 width=82) (actual time=11.724..825.969 rows=82 loops=1)

  • Filter: ((enabled_caps AND (current_number_of_items_being_delivered < maximum_number_of_items) AND (current_number_of_items_being_delivered_entire_slot < max_consolidation_cap)) OR (('11120'::text = ANY (already_present_postnr)) AND (current_number_of_items_being_delivered_entire_slot < max_consolidation_cap)) OR (NOT enabled_caps))
  • Rows Removed by Filter: 2
105. 826.491 826.491 ↓ 32.0 32 1

CTE Scan on cap_filtered_windows (cost=0.00..0.34 rows=1 width=162) (actual time=721.051..826.491 rows=32 loops=1)

  • Filter: ((opening_time IS NOT NULL) AND (start_time IS NOT NULL) AND (cutoff_time IS NOT NULL) AND (cutoff_time > now()) AND ((opening_time)::date >= (now())::date) AND ((opening_time)::date < ((now())::date + '7 days'::interval)))
  • Rows Removed by Filter: 50
Planning time : 11.328 ms
Execution time : 827.670 ms