explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y7lE

Settings
# exclusive inclusive rows x rows loops node
1. 5.230 17,947.931 ↑ 9.8 1,343 1

GroupAggregate (cost=107,466.37..110,308.67 rows=13,220 width=52) (actual time=17,942.462..17,947.931 rows=1,343 loops=1)

  • Group Key: l.user_id, hour.hour, l.campaign_id, l.team_id
  • Functions: 34
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 5.561 ms, Inlining 0.000 ms, Optimization 1.931 ms, Emission 39.000 ms, Total 46.492 ms
2. 3.001 17,942.701 ↑ 3.2 4,129 1

Sort (cost=107,466.37..107,499.42 rows=13,220 width=57) (actual time=17,942.428..17,942.701 rows=4,129 loops=1)

  • Sort Key: l.user_id, hour.hour, l.campaign_id, l.team_id
  • Sort Method: quicksort Memory: 773kB
3. 14,716.725 17,939.700 ↑ 3.2 4,129 1

Nested Loop (cost=315.34..106,561.43 rows=13,220 width=57) (actual time=10,869.192..17,939.700 rows=4,129 loops=1)

  • Join Filter: (l.range && tsrange(hour.hour, (hour.hour + '01:00:00'::interval)))
  • Rows Removed by Join Filter: 46,385,591
4. 47.535 47.535 ↓ 15.7 15,720 1

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

5. 3,167.545 3,175.440 ↓ 1.1 2,951 15,720

Materialize (cost=315.33..778.03 rows=2,644 width=49) (actual time=0.000..0.202 rows=2,951 loops=15,720)

6. 0.307 7.895 ↓ 1.1 2,951 1

Subquery Scan on l (cost=315.33..764.81 rows=2,644 width=49) (actual time=3.883..7.895 rows=2,951 loops=1)

7. 1.756 7.588 ↓ 1.1 2,951 1

Subquery Scan on occupancyhistorystats (cost=315.33..685.49 rows=2,644 width=89) (actual time=3.881..7.588 rows=2,951 loops=1)

8. 1.698 5.832 ↓ 1.1 2,951 1

WindowAgg (cost=315.33..427.70 rows=2,644 width=57) (actual time=3.875..5.832 rows=2,951 loops=1)

9. 1.951 4.134 ↓ 1.1 2,951 1

Sort (cost=315.33..321.94 rows=2,644 width=45) (actual time=3.859..4.134 rows=2,951 loops=1)

  • Sort Key: ((o.event_start)::date), o.user_id
  • Sort Method: quicksort Memory: 511kB
10. 1.215 2.183 ↓ 1.1 2,951 1

Hash Left Join (cost=1.88..165.04 rows=2,644 width=45) (actual time=0.084..2.183 rows=2,951 loops=1)

  • Hash Cond: (o.break_id = b.break_id)
11. 0.949 0.949 ↓ 1.1 2,951 1

Index Scan using occupancy_history_eventstart_index on occupancy_history o (cost=0.42..141.81 rows=2,644 width=44) (actual time=0.055..0.949 rows=2,951 loops=1)

  • Index Cond: ((event_start >= '2020-02-03 00:00:00+00'::timestamp with time zone) AND (event_start <= '2020-02-05 00:00:00+00'::timestamp with time zone))
12. 0.005 0.019 ↑ 1.0 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.014 0.014 ↑ 1.0 9 1

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

Planning time : 0.520 ms
Execution time : 17,954.056 ms