explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2XE5

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.009 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.12..4,668,125.87 rows=117 width=489) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: me.id, me.created_at, me.name, me.email, me.document, me.training_id, me.training_ts, me.training_level, me.smartfit_enrolled, me.updated_at, me.satisfaction_text_ts, me.satisfaction_texts, me.anamnese_updated_at, me.active, me.smartsys_login_data_id, me.gender, me.birthdate, me.birthyear, me.address_city, me.address_state, me.disabled_reason, me.created_from_smartsys, me.has_lesion, me.age_is_acceptable, me.user_public_id, me.has_completed_anamnese, me.has_imported_custom_training, me.smartsys_location_id, me.pinned_messages_ts, me.pinned_messages_id, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 0 ELSE 1 END, u.id, date_part('epoch'::text, u.updated_at), (GREATEST(date_part('epoch'::text, u.training_ts), (ts.training_ts)::double precision)), (GREATEST(date_part('epoch'::text, u.satisfaction_text_ts), (ts.satisfaction_text_ts)::double precision)), (ts.running_training_ts)::double precision, date_part('epoch'::text, u.pinned_messages_ts), u_1.id, (date_part('epoch'::text, CASE WHEN ut.is_custom THEN CASE WHEN ((ut.phase)::text = 'main'::text) THEN ut.custom_finish_date WHEN ((ut.phase)::text = 'challenge'::text) THEN (ut.custom_finish_date + tp.challenge_duration) WHEN ((ut.phase)::text = 'waiting-challenge-acknowledgment'::text) THEN (ut.custom_finish_date + tp.challenge_window) ELSE NULL::timestamp without time zone END ELSE (ut.training_started_at + CASE WHEN ((ut.phase)::text = 'main'::text) THEN tp.duration WHEN ((ut.phase)::text = 'challenge'::text) THEN tp.challenge_duration WHEN ((ut.phase)::text = 'waiting-challenge-acknowledgment'::text) THEN (tp.duration + tp.challenge_window) ELSE NULL::interval END) END)), date_part('epoch'::text, ut.last_activity), ((SubPlan 3)), ut.phase, (CASE WHEN ut.is_custom THEN ut.custom_max_execution ELSE tp.max_freq END), (CASE WHEN ut.is_custom THEN ut.custom_min_execution ELSE tp.min_freq_to_autoincrement END), (CASE WHEN ut.is_custom THEN ut.custom_min_to_challenge ELSE tp.min_freq_to_challenge END), tp.level_order, ut.training_started_at, (date_part('epoch'::text, replaceable_now())), tp.challenge_freq, tp.challenge_window, tp.code, tp.level_order, up.goal, up.frequency, up.focus, ut.is_custom, ut.custom_weight_training_id, ut.custom_training_level
  • Join Filter: (u.id = me.id)
  • Buffers: shared hit=3
2. 0.000 0.009 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.70..35.51 rows=1 width=427) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: me.id, me.created_at, me.name, me.email, me.document, me.training_id, me.training_ts, me.training_level, me.smartfit_enrolled, me.updated_at, me.satisfaction_text_ts, me.satisfaction_texts, me.anamnese_updated_at, me.active, me.smartsys_login_data_id, me.gender, me.birthdate, me.birthyear, me.address_city, me.address_state, me.disabled_reason, me.created_from_smartsys, me.has_lesion, me.age_is_acceptable, me.user_public_id, me.has_completed_anamnese, me.has_imported_custom_training, me.smartsys_location_id, me.pinned_messages_ts, me.pinned_messages_id, u_1.id, ut.last_activity, ut.phase, ut.training_started_at, ut.is_custom, ut.custom_weight_training_id, ut.custom_training_level, up.goal, up.frequency, up.focus, tp.level_order, tp.challenge_freq, tp.challenge_window, tp.code, (date_part('epoch'::text, CASE WHEN ut.is_custom THEN CASE WHEN ((ut.phase)::text = 'main'::text) THEN ut.custom_finish_date WHEN ((ut.phase)::text = 'challenge'::text) THEN (ut.custom_finish_date + tp.challenge_duration) WHEN ((ut.phase)::text = 'waiting-challenge-acknowledgment'::text) THEN (ut.custom_finish_date + tp.challenge_window) ELSE NULL::timestamp without time zone END ELSE (ut.training_started_at + CASE WHEN ((ut.phase)::text = 'main'::text) THEN tp.duration WHEN ((ut.phase)::text = 'challenge'::text) THEN tp.challenge_duration WHEN ((ut.phase)::text = 'waiting-challenge-acknowledgment'::text) THEN (tp.duration + tp.challenge_window) ELSE NULL::interval END) END)), ((SubPlan 3)), (CASE WHEN ut.is_custom THEN ut.custom_max_execution ELSE tp.max_freq END), (CASE WHEN ut.is_custom THEN ut.custom_min_execution ELSE tp.min_freq_to_autoincrement END), (CASE WHEN ut.is_custom THEN ut.custom_min_to_challenge ELSE tp.min_freq_to_challenge END), (date_part('epoch'::text, replaceable_now()))
  • Join Filter: (u_1.id = me.id)
  • Buffers: shared hit=3
3. 0.009 0.009 ↓ 0.0 0 1

Index Scan using user_pkey on public.""user"" me (cost=0.42..8.44 rows=1 width=297) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: me.id, me.created_at, me.name, me.email, me.document, me.training_id, me.training_ts, me.training_level, me.smartfit_enrolled, me.updated_at, me.satisfaction_text_ts, me.satisfaction_texts, me.anamnese_updated_at, me.active, me.smartsys_login_data_id, me.gender, me.birthdate, me.birthyear, me.address_city, me.address_state, me.disabled_reason, me.created_from_smartsys, me.has_lesion, me.age_is_acceptable, me.user_public_id, me.has_completed_anamnese, me.has_imported_custom_training, me.smartsys_location_id, me.pinned_messages_ts, me.pinned_messages_id
  • Index Cond: (me.id = '32082412-8bd2-11e9-9de5-0242ac110002'::uuid)
  • Buffers: shared hit=3
4. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.27..27.06 rows=1 width=130) (never executed)

  • Output: u_1.id, ut.last_activity, ut.phase, ut.training_started_at, ut.is_custom, ut.custom_weight_training_id, ut.custom_training_level, up.goal, up.frequency, up.focus, tp.level_order, tp.challenge_freq, tp.challenge_window, tp.code, date_part('epoch'::text, CASE WHEN ut.is_custom THEN CASE WHEN ((ut.phase)::text = 'main'::text) THEN ut.custom_finish_date WHEN ((ut.phase)::text = 'challenge'::text) THEN (ut.custom_finish_date + tp.challenge_duration) WHEN ((ut.phase)::text = 'waiting-challenge-acknowledgment'::text) THEN (ut.custom_finish_date + tp.challenge_window) ELSE NULL::timestamp without time zone END ELSE (ut.training_started_at + CASE WHEN ((ut.phase)::text = 'main'::text) THEN tp.duration WHEN ((ut.phase)::text = 'challenge'::text) THEN tp.challenge_duration WHEN ((ut.phase)::text = 'waiting-challenge-acknowledgment'::text) THEN (tp.duration + tp.challenge_window) ELSE NULL::interval END) END), ((SubPlan 3)), CASE WHEN ut.is_custom THEN ut.custom_max_execution ELSE tp.max_freq END, CASE WHEN ut.is_custom THEN ut.custom_min_execution ELSE tp.min_freq_to_autoincrement END, CASE WHEN ut.is_custom THEN ut.custom_min_to_challenge ELSE tp.min_freq_to_challenge END, date_part('epoch'::text, replaceable_now())
  • Join Filter: ((ut.training_program_id)::text = (tp.code)::text)
5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.27..25.35 rows=1 width=102) (never executed)

  • Output: u_1.id, ut.last_activity, ut.phase, ut.training_started_at, ut.is_custom, ut.custom_weight_training_id, ut.custom_training_level, ut.training_program_id, ut.custom_finish_date, ut.custom_max_execution, ut.custom_min_execution, ut.custom_min_to_challenge, up.goal, up.frequency, up.focus, (SubPlan 3)
6. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.85..16.90 rows=1 width=40) (never executed)

  • Output: u_1.id, up.goal, up.frequency, up.focus
  • Join Filter: (up.user_id = u_1.id)
7. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_pkey on public.""user"" u_1 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Output: u_1.id
  • Index Cond: (u_1.id = '32082412-8bd2-11e9-9de5-0242ac110002'::uuid)
  • Heap Fetches: 0
8. 0.000 0.000 ↓ 0.0 0

Index Scan using user_preference_pkey on public.user_preference up (cost=0.42..8.44 rows=1 width=40) (never executed)

  • Output: up.user_id, up.focus, up.goal, up.frequency, up.updated_at
  • Index Cond: (up.user_id = '32082412-8bd2-11e9-9de5-0242ac110002'::uuid)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using user_training_pkey on public.user_training ut (cost=0.42..8.44 rows=1 width=70) (never executed)

  • Output: ut.user_id, ut.training_program_id, ut.phase, ut.last_activity, ut.updated_at, ut.next_check, ut.training_started_at, ut.phase_changed_by, ut.is_custom, ut.custom_ready, ut.custom_weight_training_id, ut.custom_finish_date, ut.custom_min_to_challenge, ut.custom_min_execution, ut.custom_max_execution, ut.custom_training_level
  • Index Cond: (ut.user_id = '32082412-8bd2-11e9-9de5-0242ac110002'::uuid)
10.          

SubPlan (forNested Loop)

11. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=169.50..169.51 rows=1 width=8) (never executed)

  • Output: count(1)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using workout_event_log_pkey on public.workout_event_log me_1 (cost=0.56..169.47 rows=13 width=0) (never executed)

  • Output: me_1.user_id, me_1.event_id, me_1.created_at, me_1.training_id, me_1.event_time, me_1.reference, me_1.category, me_1.data, me_1.event_duration, me_1.is_valid
  • Index Cond: ((me_1.user_id = u_1.id) AND ((me_1.category)::text = 'WorkoutCompleted'::text))
  • Filter: (me_1.is_valid AND (me_1.event_time >= ut.training_started_at))
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.training_program tp (cost=0.00..1.19 rows=19 width=61) (never executed)

  • Output: tp.code, tp.level_order, tp.max_freq, tp.duration, tp.min_freq_to_autoincrement, tp.min_freq_to_challenge, tp.challenge_duration, tp.challenge_freq, tp.challenge_window
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..12.95 rows=117 width=61) (never executed)

  • Output: u.id, u.updated_at, u.pinned_messages_ts, ts.running_training_ts, GREATEST(date_part('epoch'::text, u.training_ts), (ts.training_ts)::double precision), GREATEST(date_part('epoch'::text, u.satisfaction_text_ts), (ts.satisfaction_text_ts)::double precision)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using user_pkey on public.""user"" u (cost=0.42..8.44 rows=1 width=48) (never executed)

  • Output: u.id, u.created_at, u.name, u.email, u.document, u.training_id, u.training_ts, u.training_level, u.smartfit_enrolled, u.updated_at, u.satisfaction_text_ts, u.satisfaction_texts, u.anamnese_updated_at, u.active, u.smartsys_login_data_id, u.gender, u.birthdate, u.birthyear, u.address_city, u.address_state, u.disabled_reason, u.created_from_smartsys, u.has_lesion, u.age_is_acceptable, u.user_public_id, u.has_completed_anamnese, u.has_imported_custom_training, u.smartsys_location_id, u.pinned_messages_ts, u.pinned_messages_id
  • Index Cond: (u.id = '32082412-8bd2-11e9-9de5-0242ac110002'::uuid)
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.global_ts ts (cost=0.00..2.17 rows=117 width=33) (never executed)

  • Output: ts.id, ts.training_ts, ts.satisfaction_text_ts, ts.running_training_ts
17.          

SubPlan (forNested Loop Left Join)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_notification_message unm (cost=0.00..39,898.07 rows=1 width=0) (never executed)

  • Filter: ((unm.notification_message_id = 6) AND (unm.user_id = me.id) AND (unm.created_at > (now() - '24:00:00'::interval)))
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_notification_message unm_1 (cost=0.00..37,040.40 rows=13 width=16) (never executed)

  • Output: unm_1.user_id
  • Filter: ((unm_1.notification_message_id = 6) AND (unm_1.created_at > (now() - '24:00:00'::interval)))