explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bKIl

Settings
# exclusive inclusive rows x rows loops node
1. 0.124 214.386 ↑ 17.4 288 1

Merge Left Join (cost=28,788.54..28,965.62 rows=5,000 width=41) (actual time=213.022..214.386 rows=288 loops=1)

  • Merge Cond: ((tt.time_segment = tt_2.time_segment) AND (f.id = rs1_1.facility_id))
2. 0.192 113.073 ↑ 17.4 288 1

Merge Left Join (cost=16,383.91..16,506.96 rows=5,000 width=48) (actual time=111.825..113.073 rows=288 loops=1)

  • Merge Cond: ((tt.time_segment = tt_1.time_segment) AND (f.id = rs1.facility_id))
3. 0.170 6.728 ↑ 17.4 288 1

Sort (cost=571.24..583.74 rows=5,000 width=40) (actual time=6.673..6.728 rows=288 loops=1)

  • Sort Key: tt.time_segment, f.id
  • Sort Method: quicksort Memory: 47kB
4. 0.105 6.558 ↑ 17.4 288 1

Nested Loop Left Join (cost=120.16..264.05 rows=5,000 width=40) (actual time=6.202..6.558 rows=288 loops=1)

5. 0.082 6.309 ↑ 34.7 144 1

Merge Left Join (cost=120.16..200.16 rows=5,000 width=16) (actual time=6.189..6.309 rows=144 loops=1)

  • Merge Cond: (tt.time_segment = ott.time_segment)
6. 0.077 2.238 ↑ 6.9 144 1

Sort (cost=60.08..62.58 rows=1,000 width=8) (actual time=2.211..2.238 rows=144 loops=1)

  • Sort Key: tt.time_segment
  • Sort Method: quicksort Memory: 31kB
7. 2.161 2.161 ↑ 6.9 144 1

Function Scan on get_extended_operating_hours_with_tz_2 tt (cost=0.25..10.25 rows=1,000 width=8) (actual time=2.136..2.161 rows=144 loops=1)

8. 0.040 3.989 ↑ 12.3 81 1

Sort (cost=60.08..62.58 rows=1,000 width=8) (actual time=3.974..3.989 rows=81 loops=1)

  • Sort Key: ott.time_segment
  • Sort Method: quicksort Memory: 28kB
9. 3.949 3.949 ↑ 12.3 81 1

Function Scan on get_operating_hours_by_location_id_5 ott (cost=0.25..10.25 rows=1,000 width=8) (actual time=3.934..3.949 rows=81 loops=1)

10. 0.132 0.144 ↓ 2.0 2 144

Materialize (cost=0.00..1.40 rows=1 width=24) (actual time=0.000..0.001 rows=2 loops=144)

11. 0.012 0.012 ↓ 2.0 2 1

Seq Scan on facilities f (cost=0.00..1.39 rows=1 width=24) (actual time=0.010..0.012 rows=2 loops=1)

  • Filter: ((id = ANY ('{24,25}'::integer[])) AND (location_id = 54))
  • Rows Removed by Filter: 25
12. 0.639 106.153 ↑ 10.2 204 1

GroupAggregate (cost=15,812.67..15,864.52 rows=2,074 width=20) (actual time=105.118..106.153 rows=204 loops=1)

  • Group Key: tt_1.time_segment, rs1.facility_id
13. 1.305 105.514 ↓ 1.0 2,168 1

Sort (cost=15,812.67..15,817.85 rows=2,074 width=24) (actual time=105.105..105.514 rows=2,168 loops=1)

  • Sort Key: tt_1.time_segment, rs1.facility_id
  • Sort Method: quicksort Memory: 264kB
14. 22.662 104.209 ↓ 1.0 2,168 1

Nested Loop Left Join (cost=0.25..15,698.41 rows=2,074 width=24) (actual time=65.846..104.209 rows=2,168 loops=1)

  • Join Filter: (((tt_1.time_segment >= rs1.start_time) AND (tt_1.time_segment < rs1.end_time) AND (rs1.start_time >= (tt_1.time_segment)::date) AND (rs1.end_time <= ((tt_1.time_segment)::date + '1 day'::interval)) AND (rs1.is_all_day_event IS NOT TRUE)) OR ((rs1.is_all_day_event IS TRUE) AND ((rs1.all_day_date)::date = (tt_1.time_segment)::date)))
  • Rows Removed by Join Filter: 82,867
15. 1.627 1.627 ↑ 6.9 144 1

Function Scan on get_extended_operating_hours_with_tz_2 tt_1 (cost=0.25..10.25 rows=1,000 width=8) (actual time=1.593..1.627 rows=144 loops=1)

16. 16.111 79.920 ↓ 3.5 590 144

Materialize (cost=0.00..9,388.58 rows=168 width=41) (actual time=0.362..0.555 rows=590 loops=144)

17. 63.809 63.809 ↓ 3.5 590 1

Seq Scan on reserved_slots rs1 (cost=0.00..9,387.74 rows=168 width=41) (actual time=52.109..63.809 rows=590 loops=1)

  • Filter: ((facility_id = ANY ('{24,25}'::integer[])) AND (((start_time >= ('2020-07-25 00:00:00+00'::timestamp with time zone - '1 day'::interval)) AND (start_time <= (('2020-07-25 00:00:00+00'::timestamp with time zone + '1 day'::interval) + '1 day'::interval))) OR ((all_day_date >= ('2020-07-25 00:00:00+00'::timestamp with time zone - '1 day'::interval)) AND (all_day_date <= (('2020-07-25 00:00:00+00'::timestamp with time zone + '1 day'::interval) + '1 day'::interval)))))
  • Rows Removed by Filter: 161,859
18. 0.002 101.189 ↓ 0.0 0 1

GroupAggregate (cost=12,404.63..12,406.79 rows=108 width=20) (actual time=101.189..101.189 rows=0 loops=1)

  • Group Key: tt_2.time_segment, rs1_1.facility_id
19. 0.009 101.187 ↓ 0.0 0 1

Sort (cost=12,404.63..12,404.90 rows=108 width=16) (actual time=101.187..101.187 rows=0 loops=1)

  • Sort Key: tt_2.time_segment, rs1_1.facility_id
  • Sort Method: quicksort Memory: 25kB
20. 0.083 101.178 ↓ 0.0 0 1

Nested Loop (cost=9,390.09..12,400.98 rows=108 width=16) (actual time=101.178..101.178 rows=0 loops=1)

  • Join Filter: (((tt_2.time_segment >= rs1_1.start_time) AND (tt_2.time_segment < rs1_1.end_time) AND (rs1_1.start_time >= (tt_2.time_segment)::date) AND (rs1_1.end_time <= ((tt_2.time_segment)::date + '1 day'::interval)) AND (rs1_1.is_all_day_event IS NOT TRUE)) OR ((rs1_1.is_all_day_event IS TRUE) AND ((rs1_1.all_day_date)::date = (tt_2.time_segment)::date)))
21. 2.167 2.167 ↑ 6.9 144 1

Function Scan on get_extended_operating_hours_with_tz_2 tt_2 (cost=0.25..10.25 rows=1,000 width=8) (actual time=2.137..2.167 rows=144 loops=1)

22. 0.000 98.928 ↓ 0.0 0 144

Materialize (cost=9,389.84..12,053.26 rows=9 width=33) (actual time=0.687..0.687 rows=0 loops=144)

23. 2.458 98.933 ↓ 0.0 0 1

Hash Join (cost=9,389.84..12,053.21 rows=9 width=33) (actual time=98.933..98.933 rows=0 loops=1)

  • Hash Cond: (ap.reserved_slot_id = rs1_1.id)
24. 12.827 12.827 ↑ 1.1 11,645 1

Seq Scan on active_participants ap (cost=0.00..2,631.00 rows=12,333 width=8) (actual time=0.008..12.827 rows=11,645 loops=1)

  • Filter: (end_time IS NOT NULL)
  • Rows Removed by Filter: 71,327
25. 0.235 83.648 ↓ 3.5 590 1

Hash (cost=9,387.74..9,387.74 rows=168 width=33) (actual time=83.648..83.648 rows=590 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
26. 83.413 83.413 ↓ 3.5 590 1

Seq Scan on reserved_slots rs1_1 (cost=0.00..9,387.74 rows=168 width=33) (actual time=61.966..83.413 rows=590 loops=1)

  • Filter: ((facility_id = ANY ('{24,25}'::integer[])) AND (((start_time >= ('2020-07-25 00:00:00+00'::timestamp with time zone - '1 day'::interval)) AND (start_time <= (('2020-07-25 00:00:00+00'::timestamp with time zone + '1 day'::interval) + '1 day'::interval))) OR ((all_day_date >= ('2020-07-25 00:00:00+00'::timestamp with time zone - '1 day'::interval)) AND (all_day_date <= (('2020-07-25 00:00:00+00'::timestamp with time zone + '1 day'::interval) + '1 day'::interval)))))
  • Rows Removed by Filter: 161,859