explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vSee

Settings
# exclusive inclusive rows x rows loops node
1. 17,546.256 274,734.076 ↓ 0.0 0 1

Insert on pg_temp_5._answerdata (cost=2,571,680.26..2,786,053.54 rows=919,620 width=163) (actual time=274,734.076..274,734.076 rows=0 loops=1)

  • Buffers: local hit=13288820 read=433145 dirtied=217833 written=217834, temp read=381193 written=381414
2. 147,124.106 257,187.820 ↓ 14.0 12,856,380 1

Merge Left Join (cost=2,571,680.26..2,786,053.54 rows=919,620 width=163) (actual time=65,708.300..257,187.820 rows=12,856,380 loops=1)

  • Output: pad.survey_response_id, sd.course_section_id, sd.survey_template_id, sd.survey_schedule_id, sd.survey_instance_id, sd.survey_question_map_id, sd.metric_id, COALESCE(pad.institution_id, sd.institution_id), COALESCE(pad.college_id, sd.college_id), COALESCE(pad.department_id, sd.department_id), COALESCE(pad.course_id, sd.course_version_uuid), COALESCE(pad.course_section_id, sd.course_section_id), COALESCE(pad.faculty_user_id, sd.faculty_user_id), pad.user_id, pad.value, COALESCE(pad.question_id, sd.question_id), COALESCE(pad.response_object_id, sd.response_object_id), pad.criteria_flag
  • Merge Cond: ((sd.survey_template_id = pad.survey_template_id) AND (sd.survey_schedule_id = pad.survey_schedule_id) AND (sd.survey_instance_id = pad.survey_instance_id) AND (sd.question_id = pad.question_id) AND ((COALESCE(sd.response_object_id, 0)) = (COALESCE(pad.response_object_id, 0))))
  • Join Filter: ((sd.course_section_id = pad.survey_response_course_section_id) OR (sd.survey_type_id = ANY ('{4,5}'::integer[])))
  • Rows Removed by Join Filter: 469705592
  • Buffers: local read=215307 written=1024, temp read=381193 written=381414
3. 2,611.244 2,901.266 ↓ 1.0 932,643 1

Sort (cost=141,389.33..143,688.38 rows=919,620 width=84) (actual time=2,083.197..2,901.266 rows=932,643 loops=1)

  • Output: sd.course_section_id, sd.survey_template_id, sd.survey_schedule_id, sd.survey_instance_id, sd.survey_question_map_id, sd.metric_id, sd.institution_id, sd.college_id, sd.department_id, sd.course_version_uuid, sd.faculty_user_id, sd.question_id, sd.response_object_id, sd.survey_type_id, (COALESCE(sd.response_object_id, 0))
  • Sort Key: sd.survey_template_id, sd.survey_schedule_id, sd.survey_instance_id, sd.question_id, (COALESCE(sd.response_object_id, 0))
  • Sort Method: external merge Disk: 90216kB
  • Buffers: local read=15327 written=1024, temp read=11277 written=11292
4. 290.022 290.022 ↓ 1.0 932,643 1

Seq Scan on pg_temp_5._metadata sd (cost=0.00..24,523.20 rows=919,620 width=84) (actual time=0.061..290.022 rows=932,643 loops=1)

  • Output: sd.course_section_id, sd.survey_template_id, sd.survey_schedule_id, sd.survey_instance_id, sd.survey_question_map_id, sd.metric_id, sd.institution_id, sd.college_id, sd.department_id, sd.course_version_uuid, sd.faculty_user_id, sd.question_id, sd.response_object_id, sd.survey_type_id, COALESCE(sd.response_object_id, 0)
  • Buffers: local read=15327 written=1024
5. 26,258.870 107,162.448 ↓ 43.1 482,352,504 1

Materialize (cost=2,430,290.93..2,486,285.33 rows=11,198,880 width=117) (actual time=63,625.074..107,162.448 rows=482,352,504 loops=1)

  • Output: pad.survey_response_id, pad.institution_id, pad.college_id, pad.department_id, pad.course_id, pad.course_section_id, pad.faculty_user_id, pad.user_id, pad.value, pad.question_id, pad.response_object_id, pad.criteria_flag, pad.survey_template_id, pad.survey_schedule_id, pad.survey_instance_id, pad.survey_response_course_section_id, (COALESCE(pad.response_object_id, 0))
  • Buffers: local read=199980, temp read=369916 written=370122
6. 76,971.037 80,903.578 ↓ 1.1 12,744,375 1

Sort (cost=2,430,290.93..2,458,288.13 rows=11,198,880 width=117) (actual time=63,625.064..80,903.578 rows=12,744,375 loops=1)

  • Output: pad.survey_response_id, pad.institution_id, pad.college_id, pad.department_id, pad.course_id, pad.course_section_id, pad.faculty_user_id, pad.user_id, pad.value, pad.question_id, pad.response_object_id, pad.criteria_flag, pad.survey_template_id, pad.survey_schedule_id, pad.survey_instance_id, pad.survey_response_course_section_id, (COALESCE(pad.response_object_id, 0))
  • Sort Key: pad.survey_template_id, pad.survey_schedule_id, pad.survey_instance_id, pad.question_id, (COALESCE(pad.response_object_id, 0))
  • Sort Method: external merge Disk: 1403360kB
  • Buffers: local read=199980, temp read=369916 written=370122
7. 3,932.541 3,932.541 ↓ 1.1 12,744,375 1

Seq Scan on pg_temp_5._pre_answerdata pad (cost=0.00..311,968.80 rows=11,198,880 width=117) (actual time=0.053..3,932.541 rows=12,744,375 loops=1)

  • Output: pad.survey_response_id, pad.institution_id, pad.college_id, pad.department_id, pad.course_id, pad.course_section_id, pad.faculty_user_id, pad.user_id, pad.value, pad.question_id, pad.response_object_id, pad.criteria_flag, pad.survey_template_id, pad.survey_schedule_id, pad.survey_instance_id, pad.survey_response_course_section_id, COALESCE(pad.response_object_id, 0)
  • Buffers: local read=199980