explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ft7F

Settings
# exclusive inclusive rows x rows loops node
1. 28.079 350.481 ↓ 43.0 86 1

GroupAggregate (cost=11,906.76..11,935.02 rows=2 width=1,105) (actual time=302.869..350.481 rows=86 loops=1)

  • Group Key: place_supervision.id, region.id
2. 80.482 322.402 ↓ 37,007.0 74,014 1

Sort (cost=11,906.76..11,906.77 rows=2 width=1,074) (actual time=302.756..322.402 rows=74,014 loops=1)

  • Sort Key: place_supervision.id, region.id
  • Sort Method: external merge Disk: 21,576kB
3. 40.471 241.920 ↓ 37,007.0 74,014 1

Nested Loop Left Join (cost=421.36..11,906.75 rows=2 width=1,074) (actual time=1.454..241.920 rows=74,014 loops=1)

4. 35.514 127.435 ↓ 37,007.0 74,014 1

Nested Loop Left Join (cost=420.94..11,905.27 rows=2 width=1,070) (actual time=1.449..127.435 rows=74,014 loops=1)

  • Join Filter: (auditorium.id = place_supervision.place_id)
  • Rows Removed by Join Filter: 74,014
  • -> Nested Loop (cost=420.65..11885.48 rows=3 width=992) (actual time=1.368..102.526 rows=74,014loops=1)
5. 5.065 10.357 ↓ 3,398.5 6,797 1

Hash Join (cost=420.22..11,870.06 rows=2 width=1,011) (actual time=1.351..10.357 rows=6,797 loops=1)

  • Hash Cond: ((point.ancestry)::integer = region.id)
6. 4.280 5.182 ↑ 1.1 6,797 1

Bitmap Heap Scan on places point (cost=174.88..11,580.09 rows=7,137 width=24) (actual time=1.221..5.182 rows=6,797 loops=1)

  • Recheck Cond: ((ege_place_type)::text = 'ppe'::text)
  • Filter: is_active
  • Rows Removed by Filter: 1,392
  • Heap Blocks: exact=2,078
7. 0.902 0.902 ↑ 1.1 8,189 1

Bitmap Index Scan on index_places_on_ege_place_type (cost=0.00..173.10 rows=8,624 width=0) (actual time=0.902..0.902 rows=8,189 loops=1)

  • Index Cond: ((ege_place_type)::text = 'ppe'::text)
8. 0.025 0.110 ↓ 1.2 86 1

Hash (cost=244.45..244.45 rows=71 width=987) (actual time=0.110..0.110 rows=86 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
9. 0.085 0.085 ↓ 1.2 86 1

Index Scan using index_places_on_ege_place_type on places region (cost=0.42..244.45 rows=71 width=987) (actual time=0.010..0.085 rows=86 loops=1)

  • Index Cond: ((ege_place_type)::text = 'region'::text)
  • Filter: is_active
10. 81.564 81.564 ↓ 11.0 11 6,797

Index Scan using index_places_on_ancestry_varchar_ops on places auditorium (cost=0.43..7.70 rows=1 width=25) (actual time=0.006..0.012 rows=11 loops=6,797)

  • 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
11. 0.000 0.000 ↑ 1.0 1 74,014

Materialize (cost=0.29..19.74 rows=1 width=78) (actual time=0.000..0.000 rows=1 loops=74,014)

12. 0.005 0.075 ↑ 1.0 1 1

Nested Loop (cost=0.29..19.74 rows=1 width=78) (actual time=0.054..0.075 rows=1 loops=1)

13. 0.065 0.065 ↑ 1.0 1 1

Seq Scan on place_supervision_logs place_supervision (cost=0.00..11.40 rows=1 width=78) (actual time=0.045..0.065 rows=1 loops=1)

  • Filter: ((total_viewed_seconds > 0) AND (exam_date = '2020-04-03'::date))
  • Rows Removed by Filter: 359
14. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using index_users_on_id_and_user_group_id on users supervision_user (cost=0.29..8.33 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • 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: 1
15. 74.014 74.014 ↓ 0.0 0 74,014

Index Scan using index_schedules_on_place_id_and_broadcast_hidden on schedules schedule (cost=0.42..0.73 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=74,014)

  • Index Cond: (auditorium.id = place_id)
  • Filter: (has_live AND (date = '2020-04-03'::date))
  • Rows Removed by Filter: 1
16.          

SubPlan (for GroupAggregate)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on place_supervision_logs f2 (cost=0.00..14.10 rows=1 width=0) (never executed)

  • 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)))
Planning time : 1.296 ms
Execution time : 355.267 ms