explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UvOc

Settings
# exclusive inclusive rows x rows loops node
1. 440.648 135,172.084 ↑ 1.6 29 1

GroupAggregate (cost=39.01..523.87 rows=45 width=311) (actual time=3,876.055..135,172.084 rows=29 loops=1)

  • Group Key: global_schedule.id
  • Planning time: 2.075 ms
  • Execution time: 135172.961 ms
2. 47,502.264 134,731.436 ↓ 111.7 35,526 1

Nested Loop Left Join (cost=39.01..451.07 rows=318 width=96) (actual time=154.434..134,731.436 rows=35,526 loops=1)

  • Join Filter: ((camera.id = marker.place_id) AND (global_schedule.date = marker.date))
  • Rows Removed by Join Filter: 5851888
  • Filter: ((schedule.id IS NOT NULL) OR (marker.id IS NOT NULL))
  • Rows Removed by Filter: 218904
3. 19,586.634 43,467.212 ↓ 807.7 254,430 1

Nested Loop Left Join (cost=28.04..423.11 rows=315 width=79) (actual time=0.531..43,467.212 rows=254,430 loops=1)

  • Join Filter: (global_schedule.date = schedule.date)
  • Rows Removed by Join Filter: 1811680
4. 3,669.339 5,816.048 ↓ 807.7 254,430 1

Nested Loop (cost=27.75..119.08 rows=315 width=79) (actual time=0.394..5,816.048 rows=254,430 loops=1)

5. 0.434 0.434 ↑ 1.0 45 1

Index Scan using global_schedules_pkey on global_schedules global_schedule (cost=0.14..13.81 rows=45 width=71) (actual time=0.009..0.434 rows=45 loops=1)

6. 1,876.507 2,146.275 ↓ 807.7 5,654 45

Materialize (cost=27.61..101.34 rows=7 width=8) (actual time=0.015..47.695 rows=5,654 loops=45)

7. 120.812 269.768 ↓ 807.7 5,654 1

Nested Loop (cost=27.61..101.31 rows=7 width=8) (actual time=0.342..269.768 rows=5,654 loops=1)

8. 38.499 63.849 ↓ 644.8 2,579 1

Nested Loop (cost=27.31..97.88 rows=4 width=24) (actual time=0.295..63.849 rows=2,579 loops=1)

9. 1.358 1.581 ↓ 34.8 139 1

Bitmap Heap Scan on places point (cost=27.02..42.22 rows=4 width=24) (actual time=0.250..1.581 rows=139 loops=1)

  • Recheck Cond: (((ancestry)::text = '1058973'::text) AND ((ege_place_type)::text = ANY ('{ppe,rcoi}'::text[])))
  • Heap Blocks: exact=138
10. 0.033 0.223 ↓ 0.0 0 1

BitmapAnd (cost=27.02..27.02 rows=4 width=0) (actual time=0.216..0.223 rows=0 loops=1)

11. 0.054 0.054 ↑ 1.0 139 1

Bitmap Index Scan on index_places_on_ancestry_varchar_ops (cost=0.00..5.33 rows=139 width=0) (actual time=0.047..0.054 rows=139 loops=1)

  • Index Cond: ((ancestry)::text = '1058973'::text)
12. 0.136 0.136 ↑ 1.0 648 1

Bitmap Index Scan on index_places_on_ege_place_type (cost=0.00..21.43 rows=648 width=0) (actual time=0.129..0.136 rows=648 loops=1)

  • Index Cond: ((ege_place_type)::text = ANY ('{ppe,rcoi}'::text[]))
13. 23.769 23.769 ↓ 19.0 19 139

Index Scan using index_places_on_ancestry_varchar_ops on places auditorium (cost=0.30..13.90 rows=1 width=24) (actual time=0.015..0.171 rows=19 loops=139)

  • Index Cond: ((ancestry)::text = (((point.ancestry)::text || '/'::text) || (point.id)::text))
  • Filter: ((ege_place_type)::text = 'auditorium'::text)
14. 85.107 85.107 ↑ 1.0 2 2,579

Index Scan using index_places_on_ancestry_varchar_ops on places camera (cost=0.30..0.84 rows=2 width=24) (actual time=0.016..0.033 rows=2 loops=2,579)

  • Index Cond: ((ancestry)::text = (((auditorium.ancestry)::text || '/'::text) || (auditorium.id)::text))
  • Filter: ((ege_place_type)::text = 'camera'::text)
15. 18,064.530 18,064.530 ↑ 1.0 7 254,430

Index Scan using index_schedules_on_place_id_and_broadcast_hidden on schedules schedule (cost=0.29..0.88 rows=7 width=12) (actual time=0.011..0.071 rows=7 loops=254,430)

  • Index Cond: (auditorium.id = place_id)
16. 43,758.225 43,761.960 ↓ 23.0 23 254,430

Materialize (cost=10.97..22.45 rows=1 width=33) (actual time=0.007..0.172 rows=23 loops=254,430)

17. 1.049 3.735 ↓ 23.0 23 1

Merge Join (cost=10.97..22.44 rows=1 width=33) (actual time=3.003..3.735 rows=23 loops=1)

  • Merge Cond: (marker_user.id = marker.user_id)
18. 0.118 0.118 ↑ 2.5 11 1

Index Scan using users_pkey on users marker_user (cost=0.28..308.53 rows=28 width=4) (actual time=0.019..0.118 rows=11 loops=1)

  • Filter: (user_group_id = ANY ('{10,2,11,17}'::integer[]))
  • Rows Removed by Filter: 18
19. 1.656 2.568 ↓ 1.0 93 1

Sort (cost=10.69..10.91 rows=91 width=37) (actual time=1.789..2.568 rows=93 loops=1)

  • Sort Key: marker.user_id
  • Sort Method: quicksort Memory: 32kB
20. 0.912 0.912 ↓ 1.0 93 1

Seq Scan on markers marker (cost=0.00..7.72 rows=91 width=37) (actual time=0.017..0.912 rows=93 loops=1)

  • Filter: ((moderation_state)::text = 'approved'::text)
  • Rows Removed by Filter: 47