explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fXFN

Settings
# exclusive inclusive rows x rows loops node
1. 0.973 148.683 ↓ 1,369.7 4,109 1

Nested Loop (cost=351.86..411.45 rows=3 width=663) (actual time=40.266..148.683 rows=4,109 loops=1)

  • Output: upae.district_id, upae.school_id, upae.section_id, upae.student_id, upae.discipline_id, upae.activity_id, upae.application_code, upae.event_time, ds.standard_guid, ds.standard_level, (max((s1.region)::text)), (sum(CASE WHEN (ai.score < 0) THEN 0 ELSE ai.score END)), (sum(CASE WHEN (ai.max_score < 0) THEN 0 ELSE ai.max_score END)), (count(ai.item_ref_id)), ds.has_children, upae.audit_created_datetime, upae.audit_modified_datetime, d.standard_set_code, d.standard_set_name, d.standard_set_order, d.domain_id, d.domain_name, d.domain_grade, d.state, upae.program_id, act.component_type, ds.standard_name, ds.standard_description, ds.standard_sequence, d.domain_sequence
  • Inner Unique: true
2. 2.493 127.165 ↓ 1,369.7 4,109 1

Nested Loop (cost=351.58..410.50 rows=3 width=523) (actual time=40.251..127.165 rows=4,109 loops=1)

  • Output: upae.district_id, upae.school_id, upae.section_id, upae.student_id, upae.discipline_id, upae.activity_id, upae.application_code, upae.event_time, upae.audit_created_datetime, upae.audit_modified_datetime, upae.program_id, act.component_type, (max((s1.region)::text)), (sum(CASE WHEN (ai.score < 0) THEN 0 ELSE ai.score END)), (sum(CASE WHEN (ai.max_score < 0) THEN 0 ELSE ai.max_score END)), (count(ai.item_ref_id)), ds.standard_guid, ds.standard_level, ds.has_children, ds.standard_name, ds.standard_description, ds.standard_sequence, ds.domain_id
  • Inner Unique: true
3. 3.190 90.328 ↓ 1,431.0 4,293 1

Nested Loop (cost=351.16..385.19 rows=3 width=351) (actual time=40.176..90.328 rows=4,293 loops=1)

  • Output: upae.district_id, upae.school_id, upae.section_id, upae.student_id, upae.discipline_id, upae.activity_id, upae.application_code, upae.event_time, upae.audit_created_datetime, upae.audit_modified_datetime, upae.program_id, act.component_type, (max((s1.region)::text)), (sum(CASE WHEN (ai.score < 0) THEN 0 ELSE ai.score END)), (sum(CASE WHEN (ai.max_score < 0) THEN 0 ELSE ai.max_score END)), (count(ai.item_ref_id)), s1.standard_guid
  • Inner Unique: true
4. 1.770 65.673 ↓ 1,073.2 4,293 1

Nested Loop (cost=350.89..383.97 rows=4 width=366) (actual time=40.166..65.673 rows=4,293 loops=1)

  • Output: upae.district_id, upae.school_id, upae.section_id, upae.student_id, upae.discipline_id, upae.activity_id, upae.application_code, upae.event_time, upae.audit_created_datetime, upae.audit_modified_datetime, upae.program_id, upae.assignment_ref_id, (max((s1.region)::text)), (sum(CASE WHEN (ai.score < 0) THEN 0 ELSE ai.score END)), (sum(CASE WHEN (ai.max_score < 0) THEN 0 ELSE ai.max_score END)), (count(ai.item_ref_id)), s1.standard_guid
  • Inner Unique: true
5. 8.790 51.024 ↓ 1,073.2 4,293 1

GroupAggregate (cost=350.61..350.74 rows=4 width=130) (actual time=40.140..51.024 rows=4,293 loops=1)

  • Output: upae1.activity_id, s1.standard_guid, max((s1.region)::text), sum(CASE WHEN (ai.score < 0) THEN 0 ELSE ai.score END), sum(CASE WHEN (ai.max_score < 0) THEN 0 ELSE ai.max_score END), count(ai.item_ref_id)
  • Group Key: upae1.activity_id, s1.standard_guid
6. 29.941 42.234 ↓ 4,615.5 18,462 1

Sort (cost=350.61..350.62 rows=4 width=103) (actual time=40.124..42.234 rows=18,462 loops=1)

  • Output: upae1.activity_id, s1.standard_guid, s1.region, ai.score, ai.max_score, ai.item_ref_id
  • Sort Key: upae1.activity_id, s1.standard_guid
  • Sort Method: quicksort Memory: 3,376kB
7. 2.952 12.293 ↓ 4,615.5 18,462 1

Nested Loop (cost=0.71..350.57 rows=4 width=103) (actual time=1.497..12.293 rows=18,462 loops=1)

  • Output: upae1.activity_id, s1.standard_guid, s1.region, ai.score, ai.max_score, ai.item_ref_id
8. 0.168 2.653 ↓ 836.0 836 1

Nested Loop (cost=0.42..350.01 rows=1 width=59) (actual time=1.486..2.653 rows=836 loops=1)

  • Output: upae1.activity_id, ai.score, ai.max_score, ai.item_ref_id
9. 1.523 1.523 ↓ 74.0 74 1

Seq Scan on report.unique_pa_events_mv upae1 (cost=0.00..341.56 rows=1 width=82) (actual time=1.463..1.523 rows=74 loops=1)

  • Output: upae1.district_id, upae1.school_id, upae1.section_id, upae1.student_id, upae1.program_id, upae1.application_code, upae1.event_time, upae1.application_name, upae1.discipline_id, upae1.activity_id, upae1.assessment_type, upae1.assessment_id, upae1.assignment_id, upae1.assignment_name, upae1.assignment_title, upae1.assignment_due_date, upae1.assignment_ref_id, upae1.academic_session, upae1.school_year, upae1.session_status, upae1.time_spent, upae1.time_spent_on_questions, upae1.total_score, upae1.total_max_score, upae1.total_question, upae1.grade, upae1.kafka_topic, upae1.kafka_partition, upae1.kafka_offset, upae1.kafka_timestamp, upae1.audit_created_datetime, upae1.audit_modified_datetime
  • Filter: (((upae1.audit_created_datetime >= '2020-09-04 14:58:06'::timestamp without time zone) AND (upae1.audit_created_datetime <= '2020-09-08 15:30:17'::timestamp without time zone)) OR ((upae1.audit_modified_datetime >= '2020-09-04 14:58:06'::timestamp without time zone) AND (upae1.audit_modified_datetime <= '2020-09-08 15:30:17'::timestamp without time zone)))
  • Rows Removed by Filter: 3,104
10. 0.962 0.962 ↓ 11.0 11 74

Index Scan using ai_student_id_event_time_idx on assessment.assessment_item ai (cost=0.42..8.44 rows=1 width=66) (actual time=0.009..0.013 rows=11 loops=74)

  • Output: ai.district_id, ai.school_id, ai.section_id, ai.student_id, ai.application_code, ai.event_time, ai.item_ref_id, ai.item_code, ai.sequence, ai.question_type, ai.attempted, ai.automarkable, ai.time_spent, ai.score, ai.max_score, ai.response_position, ai.answer_choice, ai.depth_of_knowledge, ai.domain_name, ai.difficulty, ai.life_cycle_tag, ai.audit_created_datetime, ai.audit_modified_datetime
  • Index Cond: (((ai.student_id)::text = (upae1.student_id)::text) AND (ai.event_time = upae1.event_time))
  • Filter: ai.attempted
  • Rows Removed by Filter: 1
11. 6.688 6.688 ↓ 2.4 22 836

Index Scan using standard_item_id_idx on standard.standard s1 (cost=0.29..0.47 rows=9 width=54) (actual time=0.004..0.008 rows=22 loops=836)

  • Output: s1.assessment_id, s1.item_id, s1.standard_set_name, s1.region, s1.provider, s1.standard_guid, s1.standard_id, s1.audit_created_datetime, s1.audit_modified_datetime
  • Index Cond: ((s1.item_id)::text = (ai.item_ref_id)::text)
12. 12.879 12.879 ↑ 1.0 1 4,293

Index Scan using unique_pa_events_staging_pkey on report.unique_pa_events_staging upae (cost=0.28..8.30 rows=1 width=273) (actual time=0.003..0.003 rows=1 loops=4,293)

  • Output: upae.district_id, upae.school_id, upae.section_id, upae.student_id, upae.program_id, upae.application_code, upae.event_time, upae.application_name, upae.discipline_id, upae.activity_id, upae.assessment_type, upae.assessment_id, upae.assignment_id, upae.assignment_name, upae.assignment_title, upae.assignment_due_date, upae.assignment_ref_id, upae.academic_session, upae.school_year, upae.session_status, upae.time_spent, upae.time_spent_on_questions, upae.total_score, upae.total_max_score, upae.total_question, upae.grade, upae.kafka_topic, upae.kafka_partition, upae.kafka_offset, upae.kafka_timestamp, upae.audit_created_datetime, upae.audit_modified_datetime
  • Index Cond: ((upae.activity_id)::text = (upae1.activity_id)::text)
13. 21.465 21.465 ↑ 1.0 1 4,293

Index Scan using assignment_component_type_pkey on report.assignment_component_type act (cost=0.27..0.31 rows=1 width=71) (actual time=0.005..0.005 rows=1 loops=4,293)

  • Output: act.assignment_ref_id, act.component_type, act.audit_created_datetime, act.audit_modified_datetime
  • Index Cond: ((act.assignment_ref_id)::text = (upae.assignment_ref_id)::text)
14. 34.344 34.344 ↑ 1.0 1 4,293

Index Scan using domain_standards_pkey on standard.domain_standards ds (cost=0.42..8.44 rows=1 width=209) (actual time=0.008..0.008 rows=1 loops=4,293)

  • Output: ds.standards_set_id, ds.domain_id, ds.standard_guid, ds.standard_level, ds.standard_name, ds.standard_description, ds.standard_sequence, ds.has_children
  • Index Cond: ((ds.standard_guid)::text = (s1.standard_guid)::text)
15. 20.545 20.545 ↑ 1.0 1 4,109

Index Scan using domains_pkey on standard.domains d (cost=0.29..0.31 rows=1 width=177) (actual time=0.005..0.005 rows=1 loops=4,109)

  • Output: d.standard_set_code, d.standard_set_name, d.standard_set_year, d.standard_set_order, d.domain_id, d.domain_name, d.domain_code, d.domain_sequence, d.domain_grade, d.standards_set_id, d.state
  • Index Cond: ((d.domain_id)::text = (ds.domain_id)::text)
Planning time : 2.378 ms
Execution time : 149.006 ms