explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ENpF

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 158.373 ↑ 1.0 50 1

Limit (cost=28,404.95..28,405.07 rows=50 width=124) (actual time=158.361..158.373 rows=50 loops=1)

2.          

CTE cte_root

3. 9.031 10.464 ↓ 3.0 1,724 1

HashAggregate (cost=1,363.50..1,369.25 rows=575 width=8) (actual time=9.817..10.464 rows=1,724 loops=1)

  • Group Key: o_1.id, o_1.customer_id
4. 0.268 1.433 ↓ 3.0 1,724 1

Append (cost=14.17..1,360.62 rows=575 width=8) (actual time=0.110..1.433 rows=1,724 loops=1)

5. 0.530 0.621 ↓ 3.0 1,724 1

Bitmap Heap Scan on orders o_1 (cost=14.17..1,351.96 rows=573 width=8) (actual time=0.110..0.621 rows=1,724 loops=1)

  • Recheck Cond: ((create_stamp > '2020-07-30 00:00:00+00'::timestamp with time zone) AND (create_stamp < '2020-08-02 00:00:00+00'::timestamp with time zone))
  • Filter: (location_id = ANY ('{51,52,53,54,55}'::integer[]))
  • Heap Blocks: exact=122
6. 0.091 0.091 ↓ 3.1 1,768 1

Bitmap Index Scan on orders_create_stamp (cost=0.00..14.02 rows=573 width=0) (actual time=0.091..0.091 rows=1,768 loops=1)

  • Index Cond: ((create_stamp > '2020-07-30 00:00:00+00'::timestamp with time zone) AND (create_stamp < '2020-08-02 00:00:00+00'::timestamp with time zone))
7. 0.001 0.003 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=8) (actual time=0.002..0.003 rows=0 loops=1)

  • Group Key: order_id, customer_id
8. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: false
9. 0.001 0.541 ↓ 0.0 0 1

Limit (cost=0.01..0.02 rows=1 width=8) (actual time=0.541..0.541 rows=0 loops=1)

10. 0.539 0.540 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=8) (actual time=0.540..0.540 rows=0 loops=1)

  • Sort Key: id DESC
  • Sort Method: quicksort Memory: 25kB
11. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
12.          

CTE cte_reserved_slots_grouped_by_start_time

13. 0.000 0.000 ↓ 0.0 0

Unique (cost=7,388.04..7,392.04 rows=200 width=37) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Sort (cost=7,388.04..7,388.54 rows=200 width=37) (never executed)

  • Sort Key: reserved_slots_grouped_by_item_id.order_id, ((sum(reserved_slots_grouped_by_item_id.total_gross_slots))::integer), ((sum(reserved_slots_grouped_by_item_id.count_reserved))::integer), reserved_slots_grouped_by_item_id.item_scheduled_start_time, (max(reserved_slots_grouped_by_item_id.end_time)), (bool_or(reserved_slots_grouped_by_item_id.is_all_day_event)), (min(reserved_slots_grouped_by_item_id.all_day_date))
15. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=7,351.88..7,380.40 rows=200 width=37) (never executed)

  • Group Key: reserved_slots_grouped_by_item_id.order_id, reserved_slots_grouped_by_item_id.item_scheduled_start_time
16. 0.000 0.000 ↓ 0.0 0

Sort (cost=7,351.88..7,355.07 rows=1,276 width=37) (never executed)

  • Sort Key: reserved_slots_grouped_by_item_id.order_id, reserved_slots_grouped_by_item_id.item_scheduled_start_time
17. 0.000 0.000 ↓ 0.0 0

Subquery Scan on reserved_slots_grouped_by_item_id (cost=7,244.58..7,286.05 rows=1,276 width=37) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Unique (cost=7,244.58..7,273.29 rows=1,276 width=57) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Sort (cost=7,244.58..7,247.77 rows=1,276 width=57) (never executed)

  • Sort Key: o_2.id, (max(COALESCE((rs.qty_people_included * rs.qty), 0))), ((min(COALESCE((COALESCE(count(*), '0'::bigint)), '0'::bigint)))::integer), rs.order_item_id, (min(rs.item_scheduled_start_time)), (max(rs.end_time)), (bool_or(rs.is_all_day_event)), (min(rs.all_day_date))
20. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=7,121.34..7,178.76 rows=1,276 width=57) (never executed)

  • Group Key: o_2.id, rs.order_item_id, rs.qty_people_included, rs.qty, (COALESCE(count(*), '0'::bigint))
21. 0.000 0.000 ↓ 0.0 0

Sort (cost=7,121.34..7,124.53 rows=1,276 width=49) (never executed)

  • Sort Key: o_2.id, rs.order_item_id, rs.qty_people_included, rs.qty, (COALESCE(count(*), '0'::bigint))
22. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=6,242.15..7,055.51 rows=1,276 width=49) (never executed)

  • Hash Cond: (oi.id = poat.order_item_id)
23. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=6,241.08..7,049.64 rows=1,276 width=53) (never executed)

  • Hash Cond: (oi.sku = p.id)
24. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=6,147.24..6,952.44 rows=1,276 width=57) (never executed)

  • Hash Cond: (crspl.reserved_slot_id = rs.id)
25. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=1,741.52..2,079.45 rows=33,793 width=12) (never executed)

  • Group Key: crspl.reserved_slot_id
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on customer_reserved_slot_participation_link crspl (cost=0.00..1,382.01 rows=71,901 width=4) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=4,389.77..4,389.77 rows=1,276 width=53) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.72..4,389.77 rows=1,276 width=53) (never executed)

  • Hash Cond: (rs.facility_id = f.id)
29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.13..4,383.36 rows=1,580 width=57) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..3,463.65 rows=1,592 width=12) (never executed)

  • Join Filter: (cdf.order_id = oi.order_id)
31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..3,057.75 rows=575 width=8) (never executed)

32. 0.000 0.000 ↓ 0.0 0

CTE Scan on cte_root cdf (cost=0.00..11.50 rows=575 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using orders_pkey on orders o_2 (cost=0.29..5.30 rows=1 width=4) (never executed)

  • Index Cond: (id = cdf.order_id)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Index Scan using order_items_order_id on order_items oi (cost=0.42..0.67 rows=3 width=12) (never executed)

  • Index Cond: (order_id = o_2.id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using reserved_slots_order_item_id on reserved_slots rs (cost=0.42..0.56 rows=2 width=45) (never executed)

  • Index Cond: (order_item_id = oi.id)
  • Filter: (qty > 0)
36. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.32..1.32 rows=21 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Seq Scan on facilities f (cost=0.00..1.32 rows=21 width=4) (never executed)

  • Filter: (facility_type = ANY ('{0,3}'::integer[]))
38. 0.000 0.000 ↓ 0.0 0

Hash (cost=76.77..76.77 rows=1,366 width=4) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Only Scan using products_pkey on products p (cost=0.28..76.77 rows=1,366 width=4) (never executed)

  • Heap Fetches: 0
40. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.05..1.05 rows=2 width=4) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on products_on_account_transactions poat (cost=0.00..1.05 rows=2 width=4) (never executed)

  • Filter: created_as_rain_check
42.          

CTE cte_reserved_slots_grouped_by_order_id

43. 0.238 120.005 ↓ 4.6 929 1

Unique (cost=7,319.03..7,323.03 rows=200 width=37) (actual time=119.726..120.005 rows=929 loops=1)

44. 2.187 119.767 ↓ 4.6 929 1

Sort (cost=7,319.03..7,319.53 rows=200 width=37) (actual time=119.725..119.767 rows=929 loops=1)

  • Sort Key: o_3.id, ((sum((max(COALESCE((rs_1.qty_people_included * rs_1.qty), 0)))))::integer), ((sum(((min(COALESCE((COALESCE(count(*), '0'::bigint)), '0'::bigint)))::integer)))::integer), (min((min(rs_1.item_scheduled_start_time)))), (max((max(rs_1.end_time)))), (bool_or((bool_or(rs_1.is_all_day_event)))), (min((min(rs_1.all_day_date))))
  • Sort Method: quicksort Memory: 97kB
45. 0.440 117.580 ↓ 4.6 929 1

GroupAggregate (cost=7,244.58..7,311.38 rows=200 width=37) (actual time=116.775..117.580 rows=929 loops=1)

  • Group Key: o_3.id
46. 0.307 117.140 ↓ 1.1 1,431 1

Unique (cost=7,244.58..7,273.29 rows=1,276 width=57) (actual time=116.764..117.140 rows=1,431 loops=1)

47. 3.617 116.833 ↓ 1.1 1,431 1

Sort (cost=7,244.58..7,247.77 rows=1,276 width=57) (actual time=116.763..116.833 rows=1,431 loops=1)

  • Sort Key: o_3.id, (max(COALESCE((rs_1.qty_people_included * rs_1.qty), 0))), ((min(COALESCE((COALESCE(count(*), '0'::bigint)), '0'::bigint)))::integer), rs_1.order_item_id, (min(rs_1.item_scheduled_start_time)), (max(rs_1.end_time)), (bool_or(rs_1.is_all_day_event)), (min(rs_1.all_day_date))
  • Sort Method: quicksort Memory: 250kB
48. 1.218 113.216 ↓ 1.1 1,431 1

GroupAggregate (cost=7,121.34..7,178.76 rows=1,276 width=57) (actual time=111.792..113.216 rows=1,431 loops=1)

  • Group Key: o_3.id, rs_1.order_item_id, rs_1.qty_people_included, rs_1.qty, (COALESCE(count(*), '0'::bigint))
49. 7.476 111.998 ↓ 2.2 2,825 1

Sort (cost=7,121.34..7,124.53 rows=1,276 width=49) (actual time=111.778..111.998 rows=2,825 loops=1)

  • Sort Key: o_3.id, rs_1.order_item_id, rs_1.qty_people_included, rs_1.qty, (COALESCE(count(*), '0'::bigint))
  • Sort Method: quicksort Memory: 458kB
50. 0.938 104.522 ↓ 2.2 2,825 1

Hash Left Join (cost=6,242.15..7,055.51 rows=1,276 width=49) (actual time=85.458..104.522 rows=2,825 loops=1)

  • Hash Cond: (oi_1.id = poat_1.order_item_id)
51. 2.656 103.565 ↓ 2.2 2,825 1

Hash Join (cost=6,241.08..7,049.64 rows=1,276 width=53) (actual time=84.944..103.565 rows=2,825 loops=1)

  • Hash Cond: (oi_1.sku = p_1.id)
52. 6.574 97.784 ↓ 2.2 2,825 1

Hash Right Join (cost=6,147.24..6,952.44 rows=1,276 width=57) (actual time=79.877..97.784 rows=2,825 loops=1)

  • Hash Cond: (crspl_1.reserved_slot_id = rs_1.id)
53. 46.591 54.685 ↓ 1.3 42,262 1

HashAggregate (cost=1,741.52..2,079.45 rows=33,793 width=12) (actual time=42.728..54.685 rows=42,262 loops=1)

  • Group Key: crspl_1.reserved_slot_id
54. 8.094 8.094 ↓ 1.0 73,443 1

Seq Scan on customer_reserved_slot_participation_link crspl_1 (cost=0.00..1,382.01 rows=71,901 width=4) (actual time=0.014..8.094 rows=73,443 loops=1)

55. 10.157 36.525 ↓ 2.2 2,825 1

Hash (cost=4,389.77..4,389.77 rows=1,276 width=53) (actual time=36.525..36.525 rows=2,825 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 286kB
56. 1.976 26.368 ↓ 2.2 2,825 1

Hash Join (cost=2.72..4,389.77 rows=1,276 width=53) (actual time=2.537..26.368 rows=2,825 loops=1)

  • Hash Cond: (rs_1.facility_id = f_1.id)
57. 0.346 23.850 ↓ 1.8 2,827 1

Nested Loop (cost=1.13..4,383.36 rows=1,580 width=57) (actual time=1.061..23.850 rows=2,827 loops=1)

58. 0.879 14.060 ↓ 2.0 3,148 1

Nested Loop (cost=0.71..3,463.65 rows=1,592 width=12) (actual time=1.010..14.060 rows=3,148 loops=1)

  • Join Filter: (cdf_1.order_id = oi_1.order_id)
59. 1.547 6.285 ↓ 3.0 1,724 1

Nested Loop (cost=0.29..3,057.75 rows=575 width=8) (actual time=0.520..6.285 rows=1,724 loops=1)

60. 1.290 1.290 ↓ 3.0 1,724 1

CTE Scan on cte_root cdf_1 (cost=0.00..11.50 rows=575 width=4) (actual time=0.001..1.290 rows=1,724 loops=1)

61. 3.448 3.448 ↑ 1.0 1 1,724

Index Only Scan using orders_pkey on orders o_3 (cost=0.29..5.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,724)

  • Index Cond: (id = cdf_1.order_id)
  • Heap Fetches: 1,724
62. 6.896 6.896 ↑ 1.5 2 1,724

Index Scan using order_items_order_id on order_items oi_1 (cost=0.42..0.67 rows=3 width=12) (actual time=0.003..0.004 rows=2 loops=1,724)

  • Index Cond: (order_id = o_3.id)
63. 9.444 9.444 ↑ 2.0 1 3,148

Index Scan using reserved_slots_order_item_id on reserved_slots rs_1 (cost=0.42..0.56 rows=2 width=45) (actual time=0.002..0.003 rows=1 loops=3,148)

  • Index Cond: (order_item_id = oi_1.id)
  • Filter: (qty > 0)
  • Rows Removed by Filter: 0
64. 0.515 0.542 ↑ 1.0 21 1

Hash (cost=1.32..1.32 rows=21 width=4) (actual time=0.542..0.542 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
65. 0.027 0.027 ↑ 1.0 21 1

Seq Scan on facilities f_1 (cost=0.00..1.32 rows=21 width=4) (actual time=0.017..0.027 rows=21 loops=1)

  • Filter: (facility_type = ANY ('{0,3}'::integer[]))
  • Rows Removed by Filter: 6
66. 1.931 3.125 ↓ 1.0 1,378 1

Hash (cost=76.77..76.77 rows=1,366 width=4) (actual time=3.125..3.125 rows=1,378 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 65kB
67. 1.194 1.194 ↓ 1.0 1,378 1

Index Only Scan using products_pkey on products p_1 (cost=0.28..76.77 rows=1,366 width=4) (actual time=0.883..1.194 rows=1,378 loops=1)

  • Heap Fetches: 320
68. 0.001 0.019 ↓ 0.0 0 1

Hash (cost=1.05..1.05 rows=2 width=4) (actual time=0.019..0.019 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
69. 0.018 0.018 ↓ 0.0 0 1

Seq Scan on products_on_account_transactions poat_1 (cost=0.00..1.05 rows=2 width=4) (actual time=0.018..0.018 rows=0 loops=1)

  • Filter: created_as_rain_check
  • Rows Removed by Filter: 5
70.          

CTE cte_count_non_event_items

71. 0.679 15.202 ↑ 1.4 1,111 1

HashAggregate (cost=8,574.15..8,593.85 rows=1,576 width=8) (actual time=15.046..15.202 rows=1,111 loops=1)

  • Group Key: oi_2.order_id
72. 0.546 14.523 ↓ 1.0 1,587 1

Hash Anti Join (cost=4,725.28..8,566.27 rows=1,576 width=8) (actual time=10.242..14.523 rows=1,587 loops=1)

  • Hash Cond: (oi_2.id = oi_3.id)
73. 0.111 3.783 ↓ 2.0 3,148 1

Nested Loop (cost=0.42..3,821.45 rows=1,592 width=12) (actual time=0.024..3.783 rows=3,148 loops=1)

74. 0.224 0.224 ↓ 3.0 1,724 1

CTE Scan on cte_root cdf_2 (cost=0.00..11.50 rows=575 width=4) (actual time=0.001..0.224 rows=1,724 loops=1)

75. 3.448 3.448 ↑ 1.5 2 1,724

Index Scan using order_items_order_id on order_items oi_2 (cost=0.42..6.60 rows=3 width=12) (actual time=0.001..0.002 rows=2 loops=1,724)

  • Index Cond: (order_id = cdf_2.order_id)
76. 0.591 10.194 ↓ 1.9 3,056 1

Hash (cost=4,704.55..4,704.55 rows=1,625 width=4) (actual time=10.194..10.194 rows=3,056 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 140kB
77. 2.286 9.603 ↓ 1.9 3,056 1

Nested Loop (cost=0.84..4,704.55 rows=1,625 width=4) (actual time=0.027..9.603 rows=3,056 loops=1)

78. 0.469 4.169 ↓ 2.0 3,148 1

Nested Loop (cost=0.42..3,821.45 rows=1,592 width=4) (actual time=0.012..4.169 rows=3,148 loops=1)

79. 0.252 0.252 ↓ 3.0 1,724 1

CTE Scan on cte_root cdf_3 (cost=0.00..11.50 rows=575 width=4) (actual time=0.000..0.252 rows=1,724 loops=1)

80. 3.448 3.448 ↑ 1.5 2 1,724

Index Scan using order_items_order_id on order_items oi_3 (cost=0.42..6.60 rows=3 width=8) (actual time=0.002..0.002 rows=2 loops=1,724)

  • Index Cond: (order_id = cdf_3.order_id)
81. 3.148 3.148 ↑ 2.0 1 3,148

Index Only Scan using reserved_slots_order_item_id on reserved_slots rs_2 (cost=0.42..0.53 rows=2 width=4) (actual time=0.001..0.001 rows=1 loops=3,148)

  • Index Cond: (order_item_id = oi_3.id)
  • Heap Fetches: 3,218
82. 1.464 158.363 ↑ 75.5 60 1

Sort (cost=3,726.76..3,738.09 rows=4,531 width=124) (actual time=158.358..158.363 rows=60 loops=1)

  • Sort Key: o.create_stamp
  • Sort Method: top-N heapsort Memory: 40kB
83. 0.729 156.899 ↑ 2.6 1,724 1

Hash Left Join (cost=58.31..3,570.29 rows=4,531 width=124) (actual time=148.172..156.899 rows=1,724 loops=1)

  • Hash Cond: (cte_root.order_id = ccnei.order_id)
84. 1.265 139.429 ↓ 3.0 1,724 1

Hash Left Join (cost=7.09..3,358.76 rows=575 width=125) (actual time=131.405..139.429 rows=1,724 loops=1)

  • Hash Cond: (cte_root.order_id = crsgboi.order_id)
85. 0.433 17.253 ↓ 3.0 1,724 1

Nested Loop Left Join (cost=0.59..3,330.69 rows=575 width=92) (actual time=9.907..17.253 rows=1,724 loops=1)

  • Join Filter: false
86. 0.000 16.820 ↓ 3.0 1,724 1

Nested Loop (cost=0.59..3,324.94 rows=575 width=59) (actual time=9.905..16.820 rows=1,724 loops=1)

87. 1.547 13.404 ↓ 3.0 1,724 1

Nested Loop (cost=0.29..3,057.75 rows=575 width=24) (actual time=9.893..13.404 rows=1,724 loops=1)

88. 10.133 10.133 ↓ 3.0 1,724 1

CTE Scan on cte_root (cost=0.00..11.50 rows=575 width=4) (actual time=9.820..10.133 rows=1,724 loops=1)

89. 1.724 1.724 ↑ 1.0 1 1,724

Index Scan using orders_pkey on orders o (cost=0.29..5.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=1,724)

  • Index Cond: (id = cte_root.order_id)
90. 3.448 3.448 ↑ 1.0 1 1,724

Index Scan using customers_pkey on customers c (cost=0.29..0.46 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=1,724)

  • Index Cond: (id = o.customer_id)
91. 0.000 0.000 ↓ 0.0 0 1,724

Result (cost=0.00..0.00 rows=0 width=33) (actual time=0.000..0.000 rows=0 loops=1,724)

  • One-Time Filter: false
92. 0.710 120.911 ↓ 4.6 929 1

Hash (cost=4.00..4.00 rows=200 width=37) (actual time=120.911..120.911 rows=929 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 70kB
93. 120.201 120.201 ↓ 4.6 929 1

CTE Scan on cte_reserved_slots_grouped_by_order_id crsgboi (cost=0.00..4.00 rows=200 width=37) (actual time=119.730..120.201 rows=929 loops=1)

94. 0.156 16.741 ↑ 1.4 1,111 1

Hash (cost=31.52..31.52 rows=1,576 width=8) (actual time=16.741..16.741 rows=1,111 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 60kB
95. 16.585 16.585 ↑ 1.4 1,111 1

CTE Scan on cte_count_non_event_items ccnei (cost=0.00..31.52 rows=1,576 width=8) (actual time=15.049..16.585 rows=1,111 loops=1)