explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lT8c : Optimization for: plan #Z6yN

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.029 2,601.642 ↑ 1.0 1 1

Limit (cost=190,799.86..190,799.95 rows=1 width=336) (actual time=2,601.614..2,601.642 rows=1 loops=1)

  • Output: r.prediction_datetime, h.retreat_point_id, rp.name, w.territory_id, (sum((COALESCE(orders.num_orders, '0'::bigint)))), (sum(r.num_orders_pred)), (max(w.weather_datetime)), (max(w.summary)), (max(w.icon)), (max(w.precip_intensity)), (max(w.precip_probability)), (max(w.temperature)), (max(w.apparent_temperature)), (max(w.dew_point)), (max(w.humidity)), (max(w.pressure)), (max(w.wind_speed)), (max(w.wind_gust)), (max(w.wind_bearing)), (max(w.cloud_cover)), (max(w.uv_index)), (max(w.visibility)), (max(w.extraction_datetime)), (max(w.observed_weather_id)), (max(w.forecast_weather_id))
  • Buffers: shared hit=65,648, temp read=19,476 written=21,066
2. 0.023 2,601.613 ↑ 20,897.0 1 1

GroupAggregate (cost=190,799.86..192,419.38 rows=20,897 width=336) (actual time=2,601.613..2,601.613 rows=1 loops=1)

  • Output: r.prediction_datetime, h.retreat_point_id, rp.name, w.territory_id, sum((COALESCE(orders.num_orders, '0'::bigint))), sum(r.num_orders_pred), max(w.weather_datetime), max(w.summary), max(w.icon), max(w.precip_intensity), max(w.precip_probability), max(w.temperature), max(w.apparent_temperature), max(w.dew_point), max(w.humidity), max(w.pressure), max(w.wind_speed), max(w.wind_gust), max(w.wind_bearing), max(w.cloud_cover), max(w.uv_index), max(w.visibility), max(w.extraction_datetime), max(w.observed_weather_id), max(w.forecast_weather_id)
  • Group Key: r.prediction_datetime, h.retreat_point_id, rp.name, w.territory_id
  • Buffers: shared hit=65,648, temp read=19,476 written=21,066
3. 43.379 2,601.590 ↑ 10,448.5 2 1

Sort (cost=190,799.86..190,852.11 rows=20,897 width=308) (actual time=2,601.588..2,601.590 rows=2 loops=1)

  • Output: r.prediction_datetime, h.retreat_point_id, rp.name, w.territory_id, (COALESCE(orders.num_orders, '0'::bigint)), r.num_orders_pred, w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.observed_weather_id, w.forecast_weather_id
  • Sort Key: r.prediction_datetime, h.retreat_point_id, rp.name, w.territory_id
  • Sort Method: external merge Disk: 13,904kB
  • Buffers: shared hit=65,648, temp read=19,476 written=21,066
4. 24.163 2,558.211 ↓ 2.4 50,677 1

Hash Join (cost=171,804.57..186,297.40 rows=20,897 width=308) (actual time=2,363.167..2,558.211 rows=50,677 loops=1)

  • Output: r.prediction_datetime, h.retreat_point_id, rp.name, w.territory_id, (COALESCE(orders.num_orders, '0'::bigint)), r.num_orders_pred, w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.observed_weather_id, w.forecast_weather_id
  • Hash Cond: (h.retreat_point_id = (rp.id)::text)
  • Buffers: shared hit=65,648, temp read=18,979 written=19,322
5. 14.224 2,534.027 ↓ 1.6 50,677 1

Hash Join (cost=171,803.30..185,891.25 rows=31,345 width=276) (actual time=2,363.137..2,534.027 rows=50,677 loops=1)

  • Output: r.prediction_datetime, w.territory_id, (COALESCE(orders.num_orders, '0'::bigint)), r.num_orders_pred, w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.observed_weather_id, w.forecast_weather_id, h.retreat_point_id
  • Inner Unique: true
  • Hash Cond: (h_1.id = h.id)
  • Buffers: shared hit=65,647, temp read=18,979 written=19,322
6. 32.098 2,519.786 ↑ 6.9 50,677 1

Merge Left Join (cost=171,799.89..181,471.48 rows=348,277 width=292) (actual time=2,363.113..2,519.786 rows=50,677 loops=1)

  • Output: NULL::text, r.prediction_datetime, COALESCE(orders.num_orders, '0'::bigint), r.num_orders_pred, h_1.id, w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.territory_id, w.observed_weather_id, w.forecast_weather_id
  • Merge Cond: ((r.habitat_id = orders.habitat_id) AND (date_trunc('hour'::text, r.prediction_datetime) = (date_trunc('hour'::text, orders.order_datetime))))
  • Buffers: shared hit=65,644, temp read=18,979 written=19,322
7.          

CTE orders

8. 41.846 657.424 ↑ 4.8 59,267 1

GroupAggregate (cost=105,330.61..111,795.09 rows=287,310 width=32) (actual time=566.400..657.424 rows=59,267 loops=1)

  • Output: (date_trunc('HOUR'::text, trx.pickup_at_est)), h_2.id, count(trx.seller_id)
  • Group Key: (date_trunc('HOUR'::text, trx.pickup_at_est)), h_2.id
  • Buffers: shared hit=60,286, temp read=2,345 written=2,355
9. 186.761 615.578 ↓ 1.1 308,193 1

Sort (cost=105,330.61..106,048.89 rows=287,310 width=52) (actual time=566.392..615.578 rows=308,193 loops=1)

  • Output: (date_trunc('HOUR'::text, trx.pickup_at_est)), h_2.id, trx.seller_id
  • Sort Key: (date_trunc('HOUR'::text, trx.pickup_at_est)), h_2.id
  • Sort Method: external merge Disk: 18,760kB
  • Buffers: shared hit=60,286, temp read=2,345 written=2,355
10. 103.861 428.817 ↓ 1.1 308,193 1

Hash Join (cost=71.90..69,461.73 rows=287,310 width=52) (actual time=0.255..428.817 rows=308,193 loops=1)

  • Output: date_trunc('HOUR'::text, trx.pickup_at_est), h_2.id, trx.seller_id
  • Inner Unique: true
  • Hash Cond: (biz.habitat = h_2.id)
  • Buffers: shared hit=60,286
11. 98.269 324.941 ↓ 1.1 308,193 1

Hash Join (cost=68.49..67,808.29 rows=287,310 width=52) (actual time=0.232..324.941 rows=308,193 loops=1)

  • Output: trx.pickup_at_est, trx.seller_id, biz.habitat
  • Inner Unique: true
  • Hash Cond: (trx.seller_id = biz.id)
  • Buffers: shared hit=60,283
12. 226.451 226.451 ↓ 1.1 308,193 1

Seq Scan on public.transactions trx (cost=0.00..66,976.01 rows=287,310 width=36) (actual time=0.006..226.451 rows=308,193 loops=1)

  • Output: trx.id, trx.daas_type, trx.accepted_by, trx.accepted_by_admin, trx.accepted_by_vendor, trx.admin_assign, trx.cancelled_by_admin, trx.cancelled_by_vendor, trx.cancelled_time, trx.cash_tip, trx.company_address, trx.company_name, trx.company_phone, trx.customer_name, trx.customer_phone, trx.delivery_address, trx.delivery_instructions, trx.dropoff_variation_min, trx.external_id, trx.method, trx.missed_by_vendor, trx.order_number, trx.order_size, trx.partner_name, trx.pay_ref, trx.plain_order, trx.prep_time, trx.ready_at, trx.ready_text_sent, trx.receipt_picture, trx.runner_assigned_at, trx.runner_id, trx.runner_obj, trx.scheduled, trx.seller_id, trx.settled_by_admin, trx.status, trx.third_party, trx.tip, trx.week, trx.customer_id, trx.quote_id, trx.closed_at, trx.created_at, trx.accepted_at, trx.deliver_by, trx.delivered_at_est, trx.dropoff_time, trx.picked_up_at, trx.pickup_at_est, trx.time_requested, trx.human_time_requested, trx.diner_id, trx.order_total, trx.runner_accepted_at, trx.runner_arrived_origin, trx.runner_arrived_dropoff, trx.restaurant_not_ready_at, trx.cancel_reason, trx.cancel_confirmation_id, trx.external_confirmation_id, trx.external_payload, trx.external_cancellation_id, trx.cancel_note, trx.issue_count, trx.vendor_delivery, trx.oozo, trx.is_test, trx.time_sent_to_dispatch, trx.diner_delivery_fee, trx.vendor_acknowledged_at, trx.vendor_order_alert_received, trx.pizza, trx.grubhub_customer_reference_number, trx.tt_calc, trx.pickup_details, trx.rated, trx.territory_id, trx.invoice_item_id, trx.stripe_transfer_id, trx.engine_suggestion, trx.pseudonym, trx.self_delivery, trx.cancel_probability, trx.est_cancel_time, trx.daas_rate, trx.suite, trx.engine_stage
  • Filter: ((trx.method = 'Delivery'::text) AND (trx.status = 'completed'::text))
  • Rows Removed by Filter: 142,073
  • Buffers: shared hit=60,222
13. 0.054 0.221 ↓ 1.0 334 1

Hash (cost=64.33..64.33 rows=333 width=49) (actual time=0.220..0.221 rows=334 loops=1)

  • Output: biz.id, biz.habitat
  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=61
14. 0.167 0.167 ↓ 1.0 334 1

Seq Scan on public.businessprofiles biz (cost=0.00..64.33 rows=333 width=49) (actual time=0.005..0.167 rows=334 loops=1)

  • Output: biz.id, biz.habitat
  • Buffers: shared hit=61
15. 0.005 0.015 ↑ 1.4 13 1

Hash (cost=3.18..3.18 rows=18 width=16) (actual time=0.014..0.015 rows=13 loops=1)

  • Output: h_2.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
16. 0.010 0.010 ↑ 1.4 13 1

Seq Scan on public.habitats h_2 (cost=0.00..3.18 rows=18 width=16) (actual time=0.007..0.010 rows=13 loops=1)

  • Output: h_2.id
  • Buffers: shared hit=3
17. 58.723 1,757.038 ↓ 1.0 50,677 1

Merge Left Join (cost=21,333.22..21,965.42 rows=48,488 width=268) (actual time=1,642.806..1,757.038 rows=50,677 loops=1)

  • Output: r.prediction_datetime, r.num_orders_pred, r.habitat_id, h_1.id, w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.territory_id, w.observed_weather_id, w.forecast_weather_id
  • Merge Cond: ((r.habitat_id = w.habitat_id) AND ((date_trunc('hour'::text, r.prediction_datetime)) = (date_trunc('hour'::text, w.weather_datetime))))
  • Buffers: shared hit=5,358, temp read=16,271 written=16,300
18. 35.456 56.452 ↓ 1.0 50,677 1

Sort (cost=14,474.17..14,595.39 rows=48,488 width=44) (actual time=49.297..56.452 rows=50,677 loops=1)

  • Output: r.prediction_datetime, r.num_orders_pred, r.habitat_id, h_1.id, (date_trunc('hour'::text, r.prediction_datetime))
  • Sort Key: r.habitat_id, (date_trunc('hour'::text, r.prediction_datetime))
  • Sort Method: external merge Disk: 3,288kB
  • Buffers: shared hit=1,379, temp read=411 written=413
19. 13.689 20.996 ↓ 1.0 50,677 1

Hash Join (cost=2,058.38..10,700.51 rows=48,488 width=44) (actual time=2.203..20.996 rows=50,677 loops=1)

  • Output: r.prediction_datetime, r.num_orders_pred, r.habitat_id, h_1.id, date_trunc('hour'::text, r.prediction_datetime)
  • Inner Unique: true
  • Hash Cond: (r.habitat_id = h_1.id)
  • Buffers: shared hit=1,379
20. 5.195 7.298 ↓ 1.0 50,677 1

Bitmap Heap Scan on insights.prd_raw_order_forecasts_hourly r (cost=2,054.98..10,539.86 rows=48,488 width=28) (actual time=2.185..7.298 rows=50,677 loops=1)

  • Output: r.id, r.run_id, r.prediction_datetime, r.habitat_id, r.is_promoted, r.num_orders_pred
  • Recheck Cond: r.is_promoted
  • Heap Blocks: exact=920
  • Buffers: shared hit=1,376
21. 2.103 2.103 ↓ 1.1 53,138 1

Bitmap Index Scan on unique_promotion (cost=0.00..2,042.86 rows=48,488 width=0) (actual time=2.103..2.103 rows=53,138 loops=1)

  • Buffers: shared hit=456
22. 0.004 0.009 ↑ 1.4 13 1

Hash (cost=3.18..3.18 rows=18 width=16) (actual time=0.009..0.009 rows=13 loops=1)

  • Output: h_1.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
23. 0.005 0.005 ↑ 1.4 13 1

Seq Scan on public.habitats h_1 (cost=0.00..3.18 rows=18 width=16) (actual time=0.002..0.005 rows=13 loops=1)

  • Output: h_1.id
  • Buffers: shared hit=3
24. 1,288.960 1,641.863 ↓ 195.5 187,078 1

Sort (cost=6,859.05..6,861.44 rows=957 width=240) (actual time=1,588.334..1,641.863 rows=187,078 loops=1)

  • Output: w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.territory_id, w.observed_weather_id, w.forecast_weather_id, w.habitat_id, (date_trunc('hour'::text, w.weather_datetime))
  • Sort Key: w.habitat_id, (date_trunc('hour'::text, w.weather_datetime))
  • Sort Method: external sort Disk: 43,216kB
  • Buffers: shared hit=3,979, temp read=15,860 written=15,887
25. 63.396 352.903 ↓ 195.5 187,093 1

Subquery Scan on w (cost=6,799.70..6,811.66 rows=957 width=240) (actual time=256.240..352.903 rows=187,093 loops=1)

  • Output: w.weather_datetime, w.summary, w.icon, w.precip_intensity, w.precip_probability, w.temperature, w.apparent_temperature, w.dew_point, w.humidity, w.pressure, w.wind_speed, w.wind_gust, w.wind_bearing, w.cloud_cover, w.uv_index, w.visibility, w.extraction_datetime, w.territory_id, w.observed_weather_id, w.forecast_weather_id, w.habitat_id, date_trunc('hour'::text, w.weather_datetime)
  • Buffers: shared hit=3,979, temp read=5,061 written=5,074
26. 148.726 289.507 ↓ 195.5 187,093 1

Sort (cost=6,799.70..6,802.09 rows=957 width=240) (actual time=256.232..289.507 rows=187,093 loops=1)

  • Output: habitats.id, (COALESCE(t3.weather_datetime, t1.weather_datetime)), (COALESCE(t3.summary, t1.summary)), (COALESCE(t3.icon, t1.icon)), (COALESCE(t3.precip_intensity, t1.precip_intensity)), (COALESCE(t3.precip_probability, t1.precip_probability)), (COALESCE(t3.temperature, t1.temperature)), (COALESCE(t3.apparent_temperature, t1.apparent_temperature)), (COALESCE(t3.dew_point, t1.dew_point)), (COALESCE(t3.humidity, t1.humidity)), (COALESCE(t3.pressure, t1.pressure)), (COALESCE(t3.wind_speed, t1.wind_speed)), (COALESCE(t3.wind_gust, t1.wind_gust)), (COALESCE(t3.wind_bearing, t1.wind_bearing)), (COALESCE(t3.cloud_cover, t1.cloud_cover)), (COALESCE(t3.uv_index, t1.uv_index)), (COALESCE(t3.visibility, t1.visibility)), (COALESCE(t3.extraction_datetime, t1.extraction_datetime)), (COALESCE(t3.territory_id, t1.territory_id)), t3.id, t1.id
  • Sort Key: (COALESCE(t3.weather_datetime, t1.weather_datetime)) DESC
  • Sort Method: external merge Disk: 40,488kB
  • Buffers: shared hit=3,979, temp read=5,061 written=5,074
27. 47.007 140.781 ↓ 195.5 187,093 1

Hash Join (cost=5,190.20..6,752.32 rows=957 width=240) (actual time=60.737..140.781 rows=187,093 loops=1)

  • Output: habitats.id, COALESCE(t3.weather_datetime, t1.weather_datetime), COALESCE(t3.summary, t1.summary), COALESCE(t3.icon, t1.icon), COALESCE(t3.precip_intensity, t1.precip_intensity), COALESCE(t3.precip_probability, t1.precip_probability), COALESCE(t3.temperature, t1.temperature), COALESCE(t3.apparent_temperature, t1.apparent_temperature), COALESCE(t3.dew_point, t1.dew_point), COALESCE(t3.humidity, t1.humidity), COALESCE(t3.pressure, t1.pressure), COALESCE(t3.wind_speed, t1.wind_speed), COALESCE(t3.wind_gust, t1.wind_gust), COALESCE(t3.wind_bearing, t1.wind_bearing), COALESCE(t3.cloud_cover, t1.cloud_cover), COALESCE(t3.uv_index, t1.uv_index), COALESCE(t3.visibility, t1.visibility), COALESCE(t3.extraction_datetime, t1.extraction_datetime), COALESCE(t3.territory_id, t1.territory_id), t3.id, t1.id
  • Hash Cond: (territories.id = retreat_points.territory_id)
  • Buffers: shared hit=3,979
28. 11.476 93.735 ↓ 33.9 32,453 1

Hash Join (cost=5,185.37..6,734.33 rows=957 width=400) (actual time=60.684..93.735 rows=32,453 loops=1)

  • Output: t1.weather_datetime, t1.summary, t1.icon, t1.precip_intensity, t1.precip_probability, t1.temperature, t1.apparent_temperature, t1.dew_point, t1.humidity, t1.pressure, t1.wind_speed, t1.wind_gust, t1.wind_bearing, t1.cloud_cover, t1.uv_index, t1.visibility, t1.extraction_datetime, t1.territory_id, t1.id, t3.weather_datetime, t3.summary, t3.icon, t3.precip_intensity, t3.precip_probability, t3.temperature, t3.apparent_temperature, t3.dew_point, t3.humidity, t3.pressure, t3.wind_speed, t3.wind_gust, t3.wind_bearing, t3.cloud_cover, t3.uv_index, t3.visibility, t3.extraction_datetime, t3.territory_id, t3.id, territories.id
  • Inner Unique: true
  • Hash Cond: (COALESCE(t3.territory_id, t1.territory_id) = territories.id)
  • Buffers: shared hit=3,975
29. 17.452 82.253 ↓ 1.0 32,453 1

Hash Full Join (cost=5,183.23..6,646.65 rows=31,909 width=368) (actual time=60.671..82.253 rows=32,453 loops=1)

  • Output: t1.weather_datetime, t1.summary, t1.icon, t1.precip_intensity, t1.precip_probability, t1.temperature, t1.apparent_temperature, t1.dew_point, t1.humidity, t1.pressure, t1.wind_speed, t1.wind_gust, t1.wind_bearing, t1.cloud_cover, t1.uv_index, t1.visibility, t1.extraction_datetime, t1.territory_id, t1.id, t3.weather_datetime, t3.summary, t3.icon, t3.precip_intensity, t3.precip_probability, t3.temperature, t3.apparent_temperature, t3.dew_point, t3.humidity, t3.pressure, t3.wind_speed, t3.wind_gust, t3.wind_bearing, t3.cloud_cover, t3.uv_index, t3.visibility, t3.extraction_datetime, t3.territory_id, t3.id
  • Hash Cond: ((t3.weather_datetime = prd_forecast_weather_hourly.weather_datetime) AND (t3.territory_id = prd_forecast_weather_hourly.territory_id))
  • Buffers: shared hit=3,973
30. 4.147 4.147 ↓ 1.0 31,910 1

Seq Scan on insights.prd_observed_weather_hourly t3 (cost=0.00..1,224.09 rows=31,909 width=182) (actual time=0.008..4.147 rows=31,910 loops=1)

  • Output: t3.id, t3.weather_datetime, t3.summary, t3.icon, t3.precip_intensity, t3.precip_probability, t3.temperature, t3.apparent_temperature, t3.dew_point, t3.humidity, t3.pressure, t3.wind_speed, t3.wind_gust, t3.wind_bearing, t3.cloud_cover, t3.uv_index, t3.visibility, t3.extraction_datetime, t3.territory_id
  • Buffers: shared hit=905
31. 4.259 60.654 ↓ 4,488.0 8,976 1

Hash (cost=5,183.20..5,183.20 rows=2 width=231) (actual time=60.654..60.654 rows=8,976 loops=1)

  • Output: t1.weather_datetime, t1.summary, t1.icon, t1.precip_intensity, t1.precip_probability, t1.temperature, t1.apparent_temperature, t1.dew_point, t1.humidity, t1.pressure, t1.wind_speed, t1.wind_gust, t1.wind_bearing, t1.cloud_cover, t1.uv_index, t1.visibility, t1.extraction_datetime, t1.territory_id, t1.id, prd_forecast_weather_hourly.weather_datetime, prd_forecast_weather_hourly.territory_id
  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,538kB
  • Buffers: shared hit=3,068
32. 19.118 56.395 ↓ 4,488.0 8,976 1

Hash Join (cost=2,682.83..5,183.20 rows=2 width=231) (actual time=30.662..56.395 rows=8,976 loops=1)

  • Output: t1.weather_datetime, t1.summary, t1.icon, t1.precip_intensity, t1.precip_probability, t1.temperature, t1.apparent_temperature, t1.dew_point, t1.humidity, t1.pressure, t1.wind_speed, t1.wind_gust, t1.wind_bearing, t1.cloud_cover, t1.uv_index, t1.visibility, t1.extraction_datetime, t1.territory_id, t1.id, prd_forecast_weather_hourly.weather_datetime, prd_forecast_weather_hourly.territory_id
  • Inner Unique: true
  • Hash Cond: ((t1.weather_datetime = prd_forecast_weather_hourly.weather_datetime) AND (t1.territory_id = prd_forecast_weather_hourly.territory_id) AND (t1.extraction_datetime = (max(prd_forecast_weather_hourly.extraction_datetime))))
  • Buffers: shared hit=3,068
33. 6.637 6.637 ↓ 1.0 54,131 1

Seq Scan on insights.prd_forecast_weather_hourly t1 (cost=0.00..2,074.63 rows=54,063 width=186) (actual time=0.004..6.637 rows=54,131 loops=1)

  • Output: t1.id, t1.weather_datetime, t1.summary, t1.icon, t1.precip_intensity, t1.precip_probability, t1.temperature, t1.apparent_temperature, t1.dew_point, t1.humidity, t1.pressure, t1.wind_speed, t1.wind_gust, t1.wind_bearing, t1.cloud_cover, t1.uv_index, t1.visibility, t1.extraction_datetime, t1.territory_id
  • Buffers: shared hit=1,534
34. 2.278 30.640 ↓ 1.7 8,976 1

Hash (cost=2,588.22..2,588.22 rows=5,406 width=53) (actual time=30.640..30.640 rows=8,976 loops=1)

  • Output: prd_forecast_weather_hourly.weather_datetime, prd_forecast_weather_hourly.territory_id, (max(prd_forecast_weather_hourly.extraction_datetime))
  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 900kB
  • Buffers: shared hit=1,534
35. 21.935 28.362 ↓ 1.7 8,976 1

HashAggregate (cost=2,480.10..2,534.16 rows=5,406 width=53) (actual time=26.234..28.362 rows=8,976 loops=1)

  • Output: prd_forecast_weather_hourly.weather_datetime, prd_forecast_weather_hourly.territory_id, max(prd_forecast_weather_hourly.extraction_datetime)
  • Group Key: prd_forecast_weather_hourly.weather_datetime, prd_forecast_weather_hourly.territory_id
  • Buffers: shared hit=1,534
36. 6.427 6.427 ↓ 1.0 54,131 1

Seq Scan on insights.prd_forecast_weather_hourly (cost=0.00..2,074.63 rows=54,063 width=53) (actual time=0.001..6.427 rows=54,131 loops=1)

  • Output: prd_forecast_weather_hourly.id, prd_forecast_weather_hourly.weather_datetime, prd_forecast_weather_hourly.summary, prd_forecast_weather_hourly.icon, prd_forecast_weather_hourly.precip_intensity, prd_forecast_weather_hourly.precip_probability, prd_forecast_weather_hourly.temperature, prd_forecast_weather_hourly.apparent_temperature, prd_forecast_weather_hourly.dew_point, prd_forecast_weather_hourly.humidity, prd_forecast_weather_hourly.pressure, prd_forecast_weather_hourly.wind_speed, prd_forecast_weather_hourly.wind_gust, prd_forecast_weather_hourly.wind_bearing, prd_forecast_weather_hourly.cloud_cover, prd_forecast_weather_hourly.uv_index, prd_forecast_weather_hourly.visibility, prd_forecast_weather_hourly.extraction_datetime, prd_forecast_weather_hourly.territory_id
  • Buffers: shared hit=1,534
37. 0.001 0.006 ↑ 2.0 3 1

Hash (cost=2.06..2.06 rows=6 width=32) (actual time=0.006..0.006 rows=3 loops=1)

  • Output: territories.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
38. 0.005 0.005 ↑ 2.0 3 1

Seq Scan on public.territories (cost=0.00..2.06 rows=6 width=32) (actual time=0.003..0.005 rows=3 loops=1)

  • Output: territories.id
  • Buffers: shared hit=2
39. 0.003 0.039 ↓ 1.1 13 1

Hash (cost=4.68..4.68 rows=12 width=48) (actual time=0.039..0.039 rows=13 loops=1)

  • Output: retreat_points.territory_id, habitats.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=4
40. 0.013 0.036 ↓ 1.1 13 1

Hash Join (cost=1.27..4.68 rows=12 width=48) (actual time=0.027..0.036 rows=13 loops=1)

  • Output: retreat_points.territory_id, habitats.id
  • Hash Cond: (habitats.retreat_point_id = (retreat_points.id)::text)
  • Buffers: shared hit=4
41. 0.008 0.008 ↑ 1.4 13 1

Seq Scan on public.habitats (cost=0.00..3.18 rows=18 width=48) (actual time=0.005..0.008 rows=13 loops=1)

  • Output: habitats.id, habitats.name, habitats.zone, habitats.retreat_point_id
  • Buffers: shared hit=3
42. 0.008 0.015 ↓ 1.1 13 1

Hash (cost=1.12..1.12 rows=12 width=48) (actual time=0.015..0.015 rows=13 loops=1)

  • Output: retreat_points.territory_id, retreat_points.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
43. 0.007 0.007 ↓ 1.1 13 1

Seq Scan on public.retreat_points (cost=0.00..1.12 rows=12 width=48) (actual time=0.004..0.007 rows=13 loops=1)

  • Output: retreat_points.territory_id, retreat_points.id
  • Buffers: shared hit=1
44. 6.556 730.650 ↑ 4.8 59,267 1

Materialize (cost=38,671.58..40,108.13 rows=287,310 width=32) (actual time=717.534..730.650 rows=59,267 loops=1)

  • Output: orders.num_orders, orders.habitat_id, orders.order_datetime, (date_trunc('hour'::text, orders.order_datetime))
  • Buffers: shared hit=60,286, temp read=2,708 written=3,022
45. 39.909 724.094 ↑ 4.8 59,267 1

Sort (cost=38,671.58..39,389.86 rows=287,310 width=32) (actual time=717.530..724.094 rows=59,267 loops=1)

  • Output: orders.num_orders, orders.habitat_id, orders.order_datetime, (date_trunc('hour'::text, orders.order_datetime))
  • Sort Key: orders.habitat_id, (date_trunc('hour'::text, orders.order_datetime))
  • Sort Method: external merge Disk: 2,904kB
  • Buffers: shared hit=60,286, temp read=2,708 written=3,022
46. 684.185 684.185 ↑ 4.8 59,267 1

CTE Scan on orders (cost=0.00..5,746.20 rows=287,310 width=32) (actual time=566.405..684.185 rows=59,267 loops=1)

  • Output: orders.num_orders, orders.habitat_id, orders.order_datetime, date_trunc('hour'::text, orders.order_datetime)
  • Buffers: shared hit=60,286, temp read=2,345 written=2,658
47. 0.006 0.017 ↑ 1.4 13 1

Hash (cost=3.18..3.18 rows=18 width=48) (actual time=0.017..0.017 rows=13 loops=1)

  • Output: h.retreat_point_id, h.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
48. 0.011 0.011 ↑ 1.4 13 1

Seq Scan on public.habitats h (cost=0.00..3.18 rows=18 width=48) (actual time=0.005..0.011 rows=13 loops=1)

  • Output: h.retreat_point_id, h.id
  • Buffers: shared hit=3
49. 0.011 0.021 ↓ 1.1 13 1

Hash (cost=1.12..1.12 rows=12 width=48) (actual time=0.021..0.021 rows=13 loops=1)

  • Output: rp.name, rp.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
50. 0.010 0.010 ↓ 1.1 13 1

Seq Scan on public.retreat_points rp (cost=0.00..1.12 rows=12 width=48) (actual time=0.007..0.010 rows=13 loops=1)

  • Output: rp.name, rp.id
  • Buffers: shared hit=1