explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h3RK

Settings
# exclusive inclusive rows x rows loops node
1. 0.400 94.020 ↓ 16.3 522 1

Sort (cost=2,906.95..2,907.03 rows=32 width=102) (actual time=93.973..94.020 rows=522 loops=1)

  • Sort Key: t1.item_scheduled_start_time
  • Sort Method: quicksort Memory: 70kB
2.          

CTE cte_reserved_slots_base

3. 1.281 47.256 ↓ 40.3 1,291 1

Nested Loop (cost=0.00..2,629.25 rows=32 width=218) (actual time=0.704..47.256 rows=1,291 loops=1)

  • Join Filter: (rs_1.facility_id = f_1.id)
  • Rows Removed by Join Filter: 14,747
4. 44.684 44.684 ↓ 5.2 1,291 1

Seq Scan on reserved_slots rs_1 (cost=0.00..2,617.10 rows=246 width=218) (actual time=0.673..44.684 rows=1,291 loops=1)

  • Filter: ((COALESCE(timezone('America/Los_Angeles'::text, item_scheduled_start_time), timezone('America/Los_Angeles'::text, all_day_date)) >= '2020-09-01 00:00:00+00'::timestamp with time zone) AND (COALESCE(timezone('America/Los_Angeles'::text, item_scheduled_start_time), timezone('America/Los_Angeles'::text, all_day_date)) <= '2020-09-30 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 47,879
5. 1.265 1.291 ↓ 4.0 12 1,291

Materialize (cost=0.00..1.33 rows=3 width=4) (actual time=0.000..0.001 rows=12 loops=1,291)

6. 0.026 0.026 ↓ 7.3 22 1

Seq Scan on facilities f_1 (cost=0.00..1.32 rows=3 width=4) (actual time=0.018..0.026 rows=22 loops=1)

  • Filter: (location_id = ANY ('{1,2,3}'::integer[]))
7. 0.598 93.620 ↓ 16.3 522 1

Nested Loop Left Join (cost=8.55..276.91 rows=32 width=102) (actual time=63.982..93.620 rows=522 loops=1)

8. 19.748 91.456 ↓ 16.3 522 1

Nested Loop Left Join (cost=8.26..260.03 rows=32 width=29) (actual time=63.973..91.456 rows=522 loops=1)

  • Join Filter: ((((COALESCE(timezone('America/Los_Angeles'::text, reserved_slots_base.item_scheduled_start_time), timezone('America/Los_Angeles'::text, reserved_slots_base.all_day_date)))::date) = max_per_line_item.item_scheduled_start_date) AND (reserved_slots_base.order_id = max_per_line_item.order_id))
  • Rows Removed by Join Filter: 135,981
9. 0.259 63.356 ↓ 16.3 522 1

Nested Loop Left Join (cost=6.02..256.61 rows=32 width=25) (actual time=61.796..63.356 rows=522 loops=1)

10. 0.942 62.053 ↓ 16.3 522 1

HashAggregate (cost=5.73..6.45 rows=32 width=25) (actual time=61.765..62.053 rows=522 loops=1)

  • Group Key: reserved_slots_base.order_id, NULL::integer, reserved_slots_base.primary_customer_id, (COALESCE(timezone('America/Los_Angeles'::text, reserved_slots_base.item_scheduled_start_time), timezone('America/Los_Angeles'::text, reserved_slots_base.all_day_date)))::date, t1.item_scheduled_start_time, CASE WHEN ((timezone('America/Los_Angeles'::text, all_day_reserved_slots.all_day_date))::date IS NULL) THEN false ELSE true END
11. 1.632 61.111 ↓ 40.3 1,291 1

Hash Left Join (cost=3.31..5.25 rows=32 width=25) (actual time=57.029..61.111 rows=1,291 loops=1)

  • Hash Cond: ((reserved_slots_base.order_id = all_day_reserved_slots.order_id) AND ((COALESCE(timezone('America/Los_Angeles'::text, reserved_slots_base.item_scheduled_start_time), timezone('America/Los_Angeles'::text, reserved_slots_base.all_day_date)))::date = (timezone('America/Los_Angeles'::text, all_day_reserved_slots.all_day_date))::date))
12. 2.371 59.238 ↓ 40.3 1,291 1

Hash Left Join (cost=2.19..3.28 rows=32 width=32) (actual time=56.765..59.238 rows=1,291 loops=1)

  • Hash Cond: ((reserved_slots_base.order_id = t1.order_id) AND ((COALESCE(timezone('America/Los_Angeles'::text, reserved_slots_base.item_scheduled_start_time), timezone('America/Los_Angeles'::text, reserved_slots_base.all_day_date)))::date = (timezone('America/Los_Angeles'::text, t1.item_scheduled_start_time))::date))
13. 0.826 0.826 ↓ 40.3 1,291 1

CTE Scan on cte_reserved_slots_base reserved_slots_base (cost=0.00..0.64 rows=32 width=24) (actual time=0.706..0.826 rows=1,291 loops=1)

14. 0.392 56.041 ↓ 522.0 522 1

Hash (cost=2.18..2.18 rows=1 width=12) (actual time=56.041..56.041 rows=522 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
15. 6.630 55.649 ↓ 522.0 522 1

Hash Left Join (cost=1.12..2.18 rows=1 width=12) (actual time=48.753..55.649 rows=522 loops=1)

  • Hash Cond: ((t1.order_id = t2.order_id) AND ((timezone('America/Los_Angeles'::text, t1.item_scheduled_start_time))::date = (timezone('America/Los_Angeles'::text, t2.item_scheduled_start_time))::date))
  • Join Filter: ((t1.item_scheduled_start_time > t2.item_scheduled_start_time) OR ((t1.item_scheduled_start_time = t2.item_scheduled_start_time) AND (t1.id < t2.id)))
  • Rows Removed by Join Filter: 2,762
  • Filter: (t2.id IS NULL)
  • Rows Removed by Filter: 1,471
16. 0.330 0.330 ↓ 40.3 1,291 1

CTE Scan on cte_reserved_slots_base t1 (cost=0.00..0.64 rows=32 width=16) (actual time=0.001..0.330 rows=1,291 loops=1)

  • Filter: (item_scheduled_start_time IS NOT NULL)
17. 1.069 48.689 ↓ 40.3 1,291 1

Hash (cost=0.64..0.64 rows=32 width=16) (actual time=48.689..48.689 rows=1,291 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 82kB
18. 47.620 47.620 ↓ 40.3 1,291 1

CTE Scan on cte_reserved_slots_base t2 (cost=0.00..0.64 rows=32 width=16) (actual time=0.001..47.620 rows=1,291 loops=1)

19. 0.000 0.241 ↓ 0.0 0 1

Hash (cost=0.64..0.64 rows=32 width=12) (actual time=0.241..0.241 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
20. 0.241 0.241 ↓ 0.0 0 1

CTE Scan on cte_reserved_slots_base all_day_reserved_slots (cost=0.00..0.64 rows=32 width=12) (actual time=0.241..0.241 rows=0 loops=1)

  • Filter: (all_day_date IS NOT NULL)
  • Rows Removed by Filter: 1,291
21. 1.044 1.044 ↑ 1.0 1 522

Index Scan using orders_pkey on orders o (cost=0.29..7.81 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=522)

  • Index Cond: (id = reserved_slots_base.order_id)
22. 6.083 8.352 ↓ 130.5 261 522

Materialize (cost=2.24..2.31 rows=2 width=16) (actual time=0.004..0.016 rows=261 loops=522)

23. 0.172 2.269 ↓ 260.5 521 1

GroupAggregate (cost=2.24..2.28 rows=2 width=16) (actual time=2.078..2.269 rows=521 loops=1)

  • Group Key: max_per_line_item.item_scheduled_start_date, max_per_line_item.order_id
24. 0.248 2.097 ↓ 347.5 695 1

Sort (cost=2.24..2.24 rows=2 width=12) (actual time=2.075..2.097 rows=695 loops=1)

  • Sort Key: max_per_line_item.item_scheduled_start_date, max_per_line_item.order_id
  • Sort Method: quicksort Memory: 57kB
25. 0.046 1.849 ↓ 347.5 695 1

Subquery Scan on max_per_line_item (cost=2.14..2.23 rows=2 width=12) (actual time=1.450..1.849 rows=695 loops=1)

26. 0.313 1.803 ↓ 347.5 695 1

GroupAggregate (cost=2.14..2.21 rows=2 width=16) (actual time=1.449..1.803 rows=695 loops=1)

  • Group Key: (COALESCE((timezone('America/Los_Angeles'::text, rs.item_scheduled_start_time))::date, (timezone('America/Los_Angeles'::text, rs.all_day_date))::date)), rs.order_item_id, rs.order_id
27. 0.515 1.490 ↓ 614.0 1,228 1

Sort (cost=2.14..2.14 rows=2 width=20) (actual time=1.446..1.490 rows=1,228 loops=1)

  • Sort Key: (COALESCE((timezone('America/Los_Angeles'::text, rs.item_scheduled_start_time))::date, (timezone('America/Los_Angeles'::text, rs.all_day_date))::date)), rs.order_item_id, rs.order_id
  • Sort Method: quicksort Memory: 144kB
28. 0.818 0.975 ↓ 614.0 1,228 1

Hash Join (cost=1.37..2.13 rows=2 width=20) (actual time=0.080..0.975 rows=1,228 loops=1)

  • Hash Cond: (rs.facility_id = f.id)
29. 0.099 0.099 ↓ 40.3 1,291 1

CTE Scan on cte_reserved_slots_base rs (cost=0.00..0.64 rows=32 width=36) (actual time=0.000..0.099 rows=1,291 loops=1)

30. 0.010 0.058 ↓ 9.0 18 1

Hash (cost=1.34..1.34 rows=2 width=4) (actual time=0.058..0.058 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.048 0.048 ↓ 9.0 18 1

Seq Scan on facilities f (cost=0.00..1.34 rows=2 width=4) (actual time=0.044..0.048 rows=18 loops=1)

  • Filter: ((facility_type = 0) OR (facility_type = 3))
  • Rows Removed by Filter: 5
32. 1.566 1.566 ↑ 1.0 1 522

Index Scan using customers_pkey on customers order_c (cost=0.29..0.52 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=522)

  • Index Cond: (id = o.customer_id)