explain.depesz.com

PostgreSQL's explain analyze made readable

Result: frSL

Settings
# exclusive inclusive rows x rows loops node
1. 1.644 5,672.326 ↑ 12.8 381 1

GroupAggregate (cost=39,741.36..40,744.84 rows=4,895 width=64) (actual time=5,670.580..5,672.326 rows=381 loops=1)

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

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

  • Sort Key: l.user_id, hour.hour, l.campaign_id, l.team_id
  • Sort Method: quicksort Memory: 153kB
3. 4,692.229 5,669.752 ↑ 3.7 1,337 1

Nested Loop (cost=111.86..39,441.37 rows=4,895 width=57) (actual time=4,761.934..5,669.752 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. 2.883 2.883 ↓ 15.7 15,720 1

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

5. 972.172 974.640 ↓ 1.0 1,013 15,720

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

6. 0.110 2.468 ↓ 1.0 1,013 1

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

7. 1.383 2.358 ↓ 1.0 1,013 1

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

8. 0.434 0.975 ↓ 1.0 1,013 1

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

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

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

  • Hash Cond: (o.break_id = b.break_id)
10. 0.236 0.236 ↓ 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.020..0.236 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.002 0.011 ↑ 1.0 9 1

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

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

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

Planning time : 0.426 ms
Execution time : 5,672.588 ms