explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hS5G

Settings
# exclusive inclusive rows x rows loops node
1. 1.839 5,395.737 ↑ 12.8 381 1

GroupAggregate (cost=39,741.36..40,610.22 rows=4,895 width=52) (actual time=5,393.817..5,395.737 rows=381 loops=1)

  • Group Key: l.user_id, hour.hour, l.campaign_id, l.team_id
2. 0.875 5,393.898 ↑ 3.7 1,337 1

Sort (cost=39,741.36..39,753.60 rows=4,895 width=57) (actual time=5,393.794..5,393.898 rows=1,337 loops=1)

  • Sort Key: l.user_id, hour.hour, l.campaign_id, l.team_id
  • Sort Method: quicksort Memory: 237kB
3. 4,462.484 5,393.023 ↑ 3.7 1,337 1

Nested Loop (cost=111.86..39,441.37 rows=4,895 width=57) (actual time=4,512.136..5,393.023 rows=1,337 loops=1)

  • Join Filter: (l.range && tsrange(hour.hour, (hour.hour + '01:00:00'::interval)))
  • Rows Removed by Join Filter: 15,923,023
4. 3.059 3.059 ↓ 15.7 15,720 1

Function Scan on generate_series hour (cost=0.01..30.01 rows=1,000 width=8) (actual time=1.067..3.059 rows=15,720 loops=1)

5. 925.343 927.480 ↓ 1.0 1,013 15,720

Materialize (cost=111.85..253.81 rows=979 width=49) (actual time=0.000..0.059 rows=1,013 loops=15,720)

6. 0.087 2.137 ↓ 1.0 1,013 1

Subquery Scan on l (cost=111.85..248.91 rows=979 width=49) (actual time=0.957..2.137 rows=1,013 loops=1)

7. 1.049 2.050 ↓ 1.0 1,013 1

WindowAgg (cost=111.85..219.54 rows=979 width=133) (actual time=0.957..2.050 rows=1,013 loops=1)

8. 0.393 1.001 ↓ 1.0 1,013 1

Sort (cost=111.85..114.30 rows=979 width=45) (actual time=0.945..1.001 rows=1,013 loops=1)

  • Sort Key: ((o.event_start)::date), o.user_id
  • Sort Method: quicksort Memory: 139kB
9. 0.337 0.608 ↓ 1.0 1,013 1

Hash Left Join (cost=1.88..63.22 rows=979 width=45) (actual time=0.042..0.608 rows=1,013 loops=1)

  • Hash Cond: (o.break_id = b.break_id)
10. 0.257 0.257 ↓ 1.0 1,013 1

Index Scan using occupancy_history_eventstart_index on occupancy_history o (cost=0.42..53.69 rows=979 width=44) (actual time=0.022..0.257 rows=1,013 loops=1)

  • Index Cond: ((event_start >= '2020-07-03 00:00:00+01'::timestamp with time zone) AND (event_start <= '2020-07-05 00:00:00+01'::timestamp with time zone))
11. 0.004 0.014 ↑ 1.0 9 1

Hash (cost=1.17..1.17 rows=9 width=5) (actual time=0.014..0.014 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↑ 1.0 9 1

Seq Scan on break b (cost=0.00..1.17 rows=9 width=5) (actual time=0.009..0.010 rows=9 loops=1)

Planning time : 0.557 ms
Execution time : 5,395.988 ms