explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rBla

Settings
# exclusive inclusive rows x rows loops node
1. 67.324 2,462.976 ↓ 23.0 92 1

GroupAggregate (cost=18,536.51..57,377.04 rows=4 width=1,098) (actual time=2,206.145..2,462.976 rows=92 loops=1)

  • Group Key: place_supervision.id, region.id
  • Execution time: 2,466.354 ms(45 rows) (edited)
2. 115.310 2,150.318 ↓ 19,173.5 76,694 1

Sort (cost=18,536.51..18,536.52 rows=4 width=1,067) (actual time=2,140.042..2,150.318 rows=76,694 loops=1)

  • Sort Key: place_supervision.id, region.id
  • Sort Method: quicksort Memory: 40,016kB
3. 68.801 2,035.008 ↓ 19,173.5 76,694 1

Nested Loop Left Join (cost=1,421.81..18,536.47 rows=4 width=1,067) (actual time=116.263..2,035.008 rows=76,694 loops=1)

4. 0.595 1,506.043 ↓ 25,564.7 76,694 1

Nested Loop Left Join (cost=1,420.98..18,518.65 rows=3 width=990) (actual time=116.223..1,506.043 rows=76,694 loops=1)

5. 25.300 968.590 ↓ 25,564.7 76,694 1

Nested Loop (cost=1,420.55..18,513.03 rows=3 width=986) (actual time=116.141..968.590 rows=76,694 loops=1)

6. 14.727 145.570 ↓ 3,626.0 7,252 1

Hash Join (cost=1,420.12..18,483.68 rows=2 width=1,005) (actual time=116.068..145.570 rows=7,252 loops=1)

  • Hash Cond: ((point.ancestry)::integer = region.id)
7. 15.598 129.800 ↓ 1.0 7,252 1

Bitmap Heap Scan on places point (cost=1,060.48..18,079.05 rows=7,195 width=24) (actual time=115.004..129.800 rows=7,252 loops=1)

  • Recheck Cond: ((ege_place_type)::text = 'ppe'::text)
  • Filter: is_active
  • Rows Removed by Filter: 753
  • Heap Blocks: exact=4,707
8. 114.202 114.202 ↓ 1.0 8,044 1

Bitmap Index Scan on index_places_on_ege_place_type (cost=0.00..1,058.68 rows=7,768 width=0) (actual time=114.202..114.202 rows=8,044 loops=1)

  • Index Cond: ((ege_place_type)::text = 'ppe'::text)
9. 0.074 1.043 ↓ 1.0 86 1

Hash (cost=358.61..358.61 rows=83 width=981) (actual time=1.043..1.043 rows=86 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
10. 0.969 0.969 ↓ 1.0 86 1

Index Scan using index_places_on_ege_place_type on places region (cost=0.42..358.61 rows=83 width=981) (actual time=0.056..0.969 rows=86 loops=1)

  • Index Cond: ((ege_place_type)::text = 'region'::text)
  • Filter: is_active
11. 797.720 797.720 ↓ 5.5 11 7,252

Index Scan using index_places_on_ancestry_varchar_ops on places auditorium (cost=0.43..14.66 rows=2 width=25) (actual time=0.028..0.110 rows=11 loops=7,252)

  • Index Cond: ((ancestry)::text = (((point.ancestry)::text || '/'::text) || (point.id)::text))
  • Filter: (is_active AND ((ege_place_type)::text = 'auditorium'::text))
  • Rows Removed by Filter: 0
12. 536.858 536.858 ↓ 0.0 0 76,694

Index Scan using index_schedules_on_place_id_and_broadcast_hidden on schedules schedule (cost=0.42..1.86 rows=1 width=8) (actual time=0.006..0.007 rows=0 loops=76,694)

  • Index Cond: (auditorium.id = place_id)
  • Filter: (has_live AND (date = '2020-04-03'::date))
  • Rows Removed by Filter: 4
13. 0.000 460.164 ↓ 0.0 0 76,694

Nested Loop (cost=0.83..5.93 rows=1 width=77) (actual time=0.006..0.006 rows=0 loops=76,694)

14. 460.164 460.164 ↓ 0.0 0 76,694

Index Scan using supervision_stat_index on place_supervision_logs place_supervision (cost=0.42..1.14 rows=1 width=77) (actual time=0.006..0.006 rows=0 loops=76,694)

  • Index Cond: (auditorium.id = place_id)
  • Filter: ((total_viewed_seconds > 0) AND (exam_date = '2020-04-03'::date))
  • Rows Removed by Filter: 3
15. 0.132 0.132 ↑ 1.0 1 6

Index Only Scan using index_users_on_id_and_user_group_id on users supervision_user (cost=0.41..4.78 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=6)

  • Index Cond: (id = place_supervision.user_id)
  • Filter: (user_group_id = ANY ('{19,26,20,1,161,188,24,59,7,180,201,175,198,190,203,189,208}'::integer[]))
  • Heap Fetches: 5
16.          

SubPlan (for GroupAggregate)

17. 245.334 245.334 ↓ 0.0 0 6

Seq Scan on place_supervision_logs f2 (cost=0.00..9,710.10 rows=1 width=0) (actual time=40.889..40.889 rows=0 loops=6)

  • Filter: ((id <> place_supervision.id) AND (user_id = place_supervision.user_id) AND (tsrange(start_time, end_time, '[]'::text) && tsrange(place_supervision.start_time, place_supervision.end_time, '[]'::text)))
  • Rows Removed by Filter: 162,168
Planning time : 55.599 ms