explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F9Jf

Settings
# exclusive inclusive rows x rows loops node
1. 593.157 148,470.877 ↑ 1.6 29 1

GroupAggregate (cost=39.01..523.87 rows=45 width=311) (actual time=3,916.039..148,470.877 rows=29 loops=1)

  • Group Key: global_schedule.id
2. 52,188.882 147,877.720 ↓ 111.7 35,526 1

Nested Loop Left Join (cost=39.01..451.07 rows=318 width=96) (actual time=153.140..147,877.720 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. 21,580.285 48,110.428 ↓ 807.7 254,430 1

Nested Loop Left Join (cost=28.04..423.11 rows=315 width=79) (actual time=0.453..48,110.428 rows=254,430 loops=1)

  • Join Filter: (global_schedule.date = schedule.date)
  • Rows Removed by Join Filter: 1811680
4. 4,085.817 6,430.173 ↓ 807.7 254,430 1

Nested Loop (cost=27.75..119.08 rows=315 width=79) (actual time=0.337..6,430.173 rows=254,430 loops=1)

5. 0.486 0.486 ↑ 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.018..0.486 rows=45 loops=1)

6. 2,062.689 2,343.870 ↓ 807.7 5,654 45

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

7. 124.501 281.181 ↓ 807.7 5,654 1

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

8. 39.558 66.415 ↓ 644.8 2,579 1

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

9. 1.521 1.698 ↓ 34.8 139 1

Bitmap Heap Scan on places point (cost=27.02..42.22 rows=4 width=24) (actual time=0.198..1.698 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.023 0.177 ↓ 0.0 0 1

BitmapAnd (cost=27.02..27.02 rows=4 width=0) (actual time=0.170..0.177 rows=0 loops=1)

11. 0.050 0.050 ↑ 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.043..0.050 rows=139 loops=1)

  • Index Cond: ((ancestry)::text = '1058973'::text)
12. 0.104 0.104 ↑ 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.097..0.104 rows=648 loops=1)

  • Index Cond: ((ege_place_type)::text = ANY ('{ppe,rcoi}'::text[]))
13. 25.159 25.159 ↓ 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.017..0.181 rows=19 loops=139)

  • Index Cond: ((ancestry)::text = (((point.ancestry)::text || '/'::text) || (point.id)::text))
  • Filter: ((ege_place_type)::text = 'auditorium'::text)
14. 90.265 90.265 ↑ 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.017..0.035 rows=2 loops=2,579)

  • Index Cond: ((ancestry)::text = (((auditorium.ancestry)::text || '/'::text) || (auditorium.id)::text))
  • Filter: ((ege_place_type)::text = 'camera'::text)
15. 20,099.970 20,099.970 ↑ 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.012..0.079 rows=7 loops=254,430)

  • Index Cond: (auditorium.id = place_id)
16. 47,575.297 47,578.410 ↓ 23.0 23 254,430

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

17. 0.914 3.113 ↓ 23.0 23 1

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

  • Merge Cond: (marker_user.id = marker.user_id)
18. 0.123 0.123 ↑ 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.022..0.123 rows=11 loops=1)

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

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

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

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

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