explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FPRO

Settings
# exclusive inclusive rows x rows loops node
1. 0.127 1,551.306 ↓ 26.0 26 1

Sort (cost=100,021.87..100,021.87 rows=1 width=162) (actual time=1,551.302..1,551.306 rows=26 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: 31kB
2.          

CTE holiday_dates

3. 0.008 0.042 ↑ 1.0 10 1

HashAggregate (cost=2.34..2.44 rows=10 width=4) (actual time=0.039..0.042 rows=10 loops=1)

  • Group Key: (make_date((date_part('year'::text, now()))::integer, holidays.month, holidays.day_of_month))
4. 0.005 0.034 ↑ 1.0 10 1

Append (cost=0.00..2.31 rows=10 width=4) (actual time=0.015..0.034 rows=10 loops=1)

5. 0.020 0.020 ↑ 1.0 5 1

Seq Scan on holidays (cost=0.00..1.10 rows=5 width=4) (actual time=0.015..0.020 rows=5 loops=1)

6. 0.009 0.009 ↑ 1.0 5 1

Seq Scan on holidays holidays_1 (cost=0.00..1.11 rows=5 width=4) (actual time=0.007..0.009 rows=5 loops=1)

7.          

CTE next_14_days

8. 0.035 0.035 ↑ 66.7 15 1

Result (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.016..0.035 rows=15 loops=1)

9.          

CTE delivery_schedules

10. 0.031 1.314 ↓ 16.0 32 1

Nested Loop (cost=19.85..195.66 rows=2 width=54) (actual time=0.506..1.314 rows=32 loops=1)

11. 0.031 1.219 ↓ 32.0 32 1

Nested Loop (cost=19.71..195.49 rows=1 width=74) (actual time=0.487..1.219 rows=32 loops=1)

12. 0.031 1.124 ↓ 32.0 32 1

Nested Loop (cost=19.58..195.33 rows=1 width=82) (actual time=0.461..1.124 rows=32 loops=1)

13. 0.054 0.421 ↓ 6.4 32 1

Hash Join (cost=18.88..89.19 rows=5 width=44) (actual time=0.366..0.421 rows=32 loops=1)

  • Hash Cond: ((mapped_intervals.between_interval_id = generic_intervals.id) AND (mapped_intervals.day_of_week = generic_intervals.day_of_week))
14. 0.033 0.066 ↑ 1.0 32 1

Bitmap Heap Scan on vendor_mapping_between_intervals mapped_intervals (cost=4.53..74.55 rows=32 width=36) (actual time=0.040..0.066 rows=32 loops=1)

  • Recheck Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Heap Blocks: exact=6
15. 0.033 0.033 ↑ 1.0 32 1

Bitmap Index Scan on vendor_mapping_between_interv_vendor_store_id_between_inter_key (cost=0.00..4.53 rows=32 width=0) (actual time=0.033..0.033 rows=32 loops=1)

  • Index Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
16. 0.150 0.301 ↑ 1.0 414 1

Hash (cost=8.14..8.14 rows=414 width=24) (actual time=0.301..0.301 rows=414 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
17. 0.151 0.151 ↑ 1.0 414 1

Seq Scan on generic_between_intervals generic_intervals (cost=0.00..8.14 rows=414 width=24) (actual time=0.006..0.151 rows=414 loops=1)

18. 0.096 0.672 ↑ 2.0 1 32

Nested Loop (cost=0.70..21.21 rows=2 width=50) (actual time=0.019..0.021 rows=1 loops=32)

19. 0.192 0.192 ↑ 1.0 2 32

Index Only Scan using zip_code_idx on zip_code_area_pairing zip_codes (cost=0.28..4.32 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=32)

  • Index Cond: (postnr = '16756'::text)
  • Heap Fetches: 0
20. 0.384 0.384 ↓ 0.0 0 64

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.006..0.006 rows=0 loops=64)

  • Index Cond: ((interval_id = mapped_intervals.id) AND (zone_id = zip_codes.zone_id))
21. 0.064 0.064 ↑ 1.0 1 32

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

  • Index Cond: (id = zone_pricing.price_category_id)
  • Heap Fetches: 0
22. 0.064 0.064 ↑ 1.0 1 32

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

  • Index Cond: (id = zone_pricing.zone_id)
  • Heap Fetches: 0
23.          

CTE valid_delivery_days

24. 0.089 1.516 ↓ 6.9 69 1

Hash Join (cost=0.07..26.42 rows=10 width=82) (actual time=1.425..1.516 rows=69 loops=1)

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

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

26. 0.026 1.380 ↓ 16.0 32 1

Hash (cost=0.04..0.04 rows=2 width=78) (actual time=1.379..1.380 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 1.354 1.354 ↓ 16.0 32 1

CTE Scan on delivery_schedules (cost=0.00..0.04 rows=2 width=78) (actual time=0.508..1.354 rows=32 loops=1)

28.          

CTE delivery_windows

29. 2.002 2.002 ↓ 6.9 69 1

CTE Scan on valid_delivery_days (cost=0.00..1.02 rows=10 width=74) (actual time=1.656..2.002 rows=69 loops=1)

30.          

CTE holiday_filtered_delivery_windows

31. 2.104 2.151 ↓ 13.8 69 1

CTE Scan on delivery_windows (cost=0.23..0.47 rows=5 width=74) (actual time=1.729..2.151 rows=69 loops=1)

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

SubPlan (for CTE Scan)

33. 0.047 0.047 ↑ 1.0 10 1

CTE Scan on holiday_dates (cost=0.00..0.20 rows=10 width=4) (actual time=0.040..0.047 rows=10 loops=1)

34.          

CTE working_schedules

35. 0.302 0.302 ↓ 1.2 7 1

Seq Scan on vendor_store_work_schedule (cost=0.00..50.78 rows=6 width=40) (actual time=0.213..0.302 rows=7 loops=1)

  • Filter: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Rows Removed by Filter: 2438
36.          

CTE valid_working_days

37. 0.025 0.349 ↑ 2.0 15 1

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

  • Hash Cond: (date_part('dow'::text, next_14_days_1.day) = (working_schedules.day_of_schedule)::double precision)
38. 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.001..0.005 rows=15 loops=1)

39. 0.012 0.319 ↓ 1.2 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.307 0.307 ↓ 1.2 7 1

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

41.          

CTE working_windows

42. 0.403 0.403 ↑ 2.0 15 1

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

43.          

CTE holiday_filtered_working_windows

44. 0.441 0.444 ↑ 1.0 15 1

CTE Scan on working_windows (cost=0.23..0.97 rows=15 width=16) (actual time=0.375..0.444 rows=15 loops=1)

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

SubPlan (for CTE Scan)

46. 0.003 0.003 ↑ 1.0 10 1

CTE Scan on holiday_dates holiday_dates_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.003 rows=10 loops=1)

47.          

CTE intersected_days

48. 0.033 2.783 ↓ 3.0 15 1

HashSetOp Intersect (cost=0.00..0.70 rows=5 width=8) (actual time=2.772..2.783 rows=15 loops=1)

49. 0.045 2.750 ↓ 4.2 84 1

Append (cost=0.00..0.65 rows=20 width=8) (actual time=1.732..2.750 rows=84 loops=1)

50. 0.034 2.241 ↓ 13.8 69 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.16 rows=5 width=8) (actual time=1.732..2.241 rows=69 loops=1)

51. 2.207 2.207 ↓ 13.8 69 1

CTE Scan on holiday_filtered_delivery_windows (cost=0.00..0.11 rows=5 width=4) (actual time=1.730..2.207 rows=69 loops=1)

52. 0.004 0.464 ↑ 1.0 15 1

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

53. 0.460 0.460 ↑ 1.0 15 1

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

54.          

CTE joined_schedules

55. 0.277 10.322 ↓ 23.0 69 1

Unique (cost=5.70..5.72 rows=3 width=90) (actual time=9.766..10.322 rows=69 loops=1)

56. 0.644 10.045 ↓ 182.3 547 1

Sort (cost=5.70..5.70 rows=3 width=90) (actual time=9.765..10.045 rows=547 loops=1)

  • Sort Key: delivering.start_time, delivering.stop_time, working.opening_time DESC
  • Sort Method: quicksort Memory: 101kB
57. 0.683 9.401 ↓ 182.3 547 1

Hash Semi Join (cost=0.33..5.67 rows=3 width=90) (actual time=2.875..9.401 rows=547 loops=1)

  • Hash Cond: ((delivering.start_time)::date = intersected_days.value)
58. 1.184 5.925 ↓ 182.3 547 1

Nested Loop (cost=0.16..5.43 rows=3 width=90) (actual time=0.063..5.925 rows=547 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: 488
59. 0.020 0.020 ↓ 13.8 69 1

CTE Scan on holiday_filtered_delivery_windows delivering (cost=0.00..0.10 rows=5 width=74) (actual time=0.000..0.020 rows=69 loops=1)

60. 0.296 0.345 ↓ 3.0 15 69

Materialize (cost=0.16..0.59 rows=5 width=16) (actual time=0.001..0.005 rows=15 loops=69)

61. 0.027 0.049 ↓ 3.0 15 1

Hash Semi Join (cost=0.16..0.56 rows=5 width=16) (actual time=0.033..0.049 rows=15 loops=1)

  • Hash Cond: ((working.opening_time)::date = intersected_days_1.value)
62. 0.007 0.007 ↑ 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.007 rows=15 loops=1)

63. 0.009 0.015 ↓ 3.0 15 1

Hash (cost=0.10..0.10 rows=5 width=4) (actual time=0.015..0.015 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.006 0.006 ↓ 3.0 15 1

CTE Scan on intersected_days intersected_days_1 (cost=0.00..0.10 rows=5 width=4) (actual time=0.000..0.006 rows=15 loops=1)

65.          

SubPlan (for Nested Loop)

66. 1.094 4.376 ↑ 1.0 1 547

Aggregate (cost=0.15..0.16 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=547)

67. 3.282 3.282 ↓ 6.0 6 547

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

  • Filter: ((value >= (working.opening_time)::date) AND (value <= (delivering.start_time)::date))
  • Rows Removed by Filter: 9
68. 0.006 2.793 ↓ 3.0 15 1

Hash (cost=0.10..0.10 rows=5 width=4) (actual time=2.793..2.793 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 2.787 2.787 ↓ 3.0 15 1

CTE Scan on intersected_days (cost=0.00..0.10 rows=5 width=4) (actual time=2.773..2.787 rows=15 loops=1)

70.          

CTE vendor_constraints

71. 0.032 0.032 ↑ 1.0 1 1

Index Scan using vendor_stores_flags_store_idx on vendor_stores_flags (cost=0.27..8.29 rows=1 width=1) (actual time=0.028..0.032 rows=1 loops=1)

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

CTE consolidation_stores

73. 0.016 0.164 ↑ 22.3 9 1

HashAggregate (cost=9.40..11.41 rows=201 width=16) (actual time=0.158..0.164 rows=9 loops=1)

  • Group Key: ('5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
74. 0.002 0.148 ↑ 20.1 10 1

Append (cost=0.00..8.90 rows=201 width=16) (actual time=0.002..0.148 rows=10 loops=1)

75. 0.002 0.002 ↑ 1.0 1 1

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

76. 0.015 0.144 ↑ 22.2 9 1

Subquery Scan on _ (cost=5.85..6.88 rows=200 width=16) (actual time=0.137..0.144 rows=9 loops=1)

77. 0.002 0.129 ↑ 2.0 1 1

Unique (cost=5.85..5.86 rows=2 width=172) (actual time=0.128..0.129 rows=1 loops=1)

78. 0.055 0.127 ↑ 2.0 1 1

Sort (cost=5.85..5.86 rows=2 width=172) (actual time=0.126..0.127 rows=1 loops=1)

  • Sort Key: store_consolidation_groups.store_ids
  • Sort Method: quicksort Memory: 25kB
79. 0.032 0.072 ↑ 2.0 1 1

Hash Semi Join (cost=4.34..5.84 rows=2 width=172) (actual time=0.062..0.072 rows=1 loops=1)

  • Hash Cond: (store_consolidation_groups.zone_id = zip_code_area_pairing.zone_id)
80. 0.017 0.017 ↑ 1.0 19 1

Seq Scan on store_consolidation_groups (cost=0.00..1.43 rows=19 width=176) (actual time=0.009..0.017 rows=19 loops=1)

  • Filter: ('5647cca0-c8a1-11e8-b810-777ae174f124'::uuid = ANY (store_ids))
81. 0.007 0.023 ↑ 1.0 2 1

Hash (cost=4.32..4.32 rows=2 width=8) (actual time=0.022..0.023 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
82. 0.016 0.016 ↑ 1.0 2 1

Index Only Scan using zip_code_idx on zip_code_area_pairing (cost=0.28..4.32 rows=2 width=8) (actual time=0.016..0.016 rows=2 loops=1)

  • Index Cond: (postnr = '16756'::text)
  • Heap Fetches: 0
83.          

CTE post_filter_delivery_time_windows

84. 2.087 1,549.002 ↓ 23.0 69 1

Nested Loop (cost=0.00..99,683.59 rows=3 width=140) (actual time=64.022..1,549.002 rows=69 loops=1)

85. 0.034 0.034 ↑ 1.0 1 1

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

86. 10.458 10.458 ↓ 23.0 69 1

CTE Scan on joined_schedules (cost=0.00..0.06 rows=3 width=90) (actual time=9.767..10.458 rows=69 loops=1)

87.          

SubPlan (for Nested Loop)

88. 0.688 207.414 ↑ 1.0 1 69

Aggregate (cost=7,221.80..7,221.81 rows=1 width=8) (actual time=3.005..3.006 rows=1 loops=69)

89.          

Initplan (for Aggregate)

90. 0.140 0.140 ↑ 1.0 1 20

Index Scan using areas_pkey on areas areas_1 (cost=0.14..8.16 rows=1 width=4,203) (actual time=0.007..0.007 rows=1 loops=20)

  • Index Cond: (id = joined_schedules.zone_id)
91. 147.936 206.586 ↑ 1.0 1 69

Bitmap Heap Scan on orders_stores del (cost=1,469.84..7,213.64 rows=1 width=0) (actual time=2.524..2.994 rows=1 loops=69)

  • Recheck Cond: (sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Filter: (((dropoff_earliest_time)::double precision = date_part('epoch'::text, joined_schedules.start_time)) AND ((dropoff_latest_time)::double precision = date_part('epoch'::text, joined_schedules.stop_time)) AND st_within(geom, $23))
  • Rows Removed by Filter: 1790
  • Heap Blocks: exact=66226
92. 58.650 58.650 ↓ 1.0 1,871 69

Bitmap Index Scan on orders_stores_sender_id_idx (cost=0.00..1,469.84 rows=1,791 width=0) (actual time=0.850..0.850 rows=1,871 loops=69)

  • Index Cond: (sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
93. 0.828 142.761 ↑ 1.0 1 69

Aggregate (cost=6,765.89..6,765.90 rows=1 width=8) (actual time=2.069..2.069 rows=1 loops=69)

94. 102.948 141.933 ↓ 18.0 18 69

Bitmap Heap Scan on orders_stores (cost=1,469.84..6,765.89 rows=1 width=0) (actual time=1.671..2.057 rows=18 loops=69)

  • Recheck Cond: (sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Filter: (((dropoff_earliest_time)::double precision = date_part('epoch'::text, joined_schedules.start_time)) AND ((dropoff_latest_time)::double precision = date_part('epoch'::text, joined_schedules.stop_time)))
  • Rows Removed by Filter: 1773
  • Heap Blocks: exact=66226
95. 38.985 38.985 ↓ 1.0 1,871 69

Bitmap Index Scan on orders_stores_sender_id_idx (cost=0.00..1,469.84 rows=1,791 width=0) (actual time=0.565..0.565 rows=1,871 loops=69)

  • Index Cond: (sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
96. 9.432 1,186.248 ↓ 34.0 34 69

Nested Loop Semi Join (cost=8.31..19,240.12 rows=1 width=6) (actual time=13.304..17.192 rows=34 loops=69)

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

Initplan (for Nested Loop Semi Join)

98. 0.168 0.168 ↑ 1.0 1 24

Index Scan using areas_pkey on areas areas_2 (cost=0.14..8.16 rows=1 width=4,203) (actual time=0.007..0.007 rows=1 loops=24)

  • Index Cond: (id = joined_schedules.zone_id)
99. 2.957 1,172.172 ↓ 65.0 65 69

Nested Loop (cost=0.15..19,223.42 rows=1 width=22) (actual time=13.039..16.988 rows=65 loops=69)

100. 1,122.285 1,122.285 ↓ 4.0 4 69

Seq Scan on orders_stores del_1 (cost=0.00..19,214.97 rows=1 width=48) (actual time=13.027..16.265 rows=4 loops=69)

  • Filter: (((dropoff_earliest_time)::double precision = date_part('epoch'::text, joined_schedules.start_time)) AND ((dropoff_latest_time)::double precision = date_part('epoch'::text, joined_schedules.stop_time)) AND st_within(geom, $29))
  • Rows Removed by Filter: 4545
101. 46.930 46.930 ↓ 18.0 18 247

Index Scan using zip_codes_geom_idx on zip_codes zc (cost=0.15..8.43 rows=1 width=2,608) (actual time=0.027..0.190 rows=18 loops=247)

  • Index Cond: (geom && st_expand(del_1.geom, '0.009'::double precision))
  • Filter: ((del_1.geom && st_expand(geom, '0.009'::double precision)) AND (postnr <> ALL ('{18494,18593,19491,17893,14633,13567,19492}'::text[])) AND _st_dwithin(del_1.geom, geom, '0.009'::double precision))
  • Rows Removed by Filter: 5
102. 4.476 4.476 ↑ 40.2 5 4,476

CTE Scan on consolidation_stores (cost=0.00..4.02 rows=201 width=16) (actual time=0.000..0.001 rows=5 loops=4,476)

103.          

CTE cap_filtered_windows

104. 1,549.526 1,549.526 ↓ 34.0 68 1

CTE Scan on post_filter_delivery_time_windows (cost=0.00..0.12 rows=2 width=82) (actual time=64.037..1,549.526 rows=68 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 (('16756'::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: 1
105. 1,551.179 1,551.179 ↓ 26.0 26 1

CTE Scan on cap_filtered_windows (cost=0.00..0.25 rows=1 width=162) (actual time=289.423..1,551.179 rows=26 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: 42
Planning time : 13.450 ms
Execution time : 1,552.574 ms