explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a8Uq : Optimization for: plan #XHKo

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.986 13.577 ↓ 1.5 350 1

WindowAgg (cost=1.85..1,426.48 rows=239 width=248) (actual time=0.152..13.577 rows=350 loops=1)

  • Output: row_number() OVER (?), class.tutor_id, user_student.id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id, user_student_location.province, user_student_location.city, curriculum.curriculum_id, curriculum.title, curriculum.lecturer_id, curriculum.arrangement_start, curriculum.arrangement_end, curriculum.semester_id, class.alias, curriculum.id
  • Buffers: shared hit=6,070
2. 0.153 12.591 ↓ 1.5 350 1

Nested Loop (cost=1.85..1,422.30 rows=239 width=240) (actual time=0.058..12.591 rows=350 loops=1)

  • Output: user_student.id, class.tutor_id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id, user_student_location.province, user_student_location.city, curriculum.curriculum_id, curriculum.title, curriculum.lecturer_id, curriculum.arrangement_start, curriculum.arrangement_end, curriculum.semester_id, class.alias, curriculum.id
  • Buffers: shared hit=6,070
3. 0.577 10.854 ↓ 1.9 528 1

Nested Loop (cost=1.57..1,333.48 rows=279 width=240) (actual time=0.052..10.854 rows=528 loops=1)

  • Output: user_student.id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id, user_student_location.province, user_student_location.city, class.tutor_id, class.alias, curriculum.curriculum_id, curriculum.title, curriculum.lecturer_id, curriculum.arrangement_start, curriculum.arrangement_end, curriculum.semester_id, curriculum.id
  • Inner Unique: true
  • Buffers: shared hit=4,618
4. 0.323 7.109 ↓ 1.9 528 1

Nested Loop (cost=1.29..1,238.41 rows=279 width=160) (actual time=0.044..7.109 rows=528 loops=1)

  • Output: user_student.id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id, user_student_location.province, user_student_location.city, class.tutor_id, class.alias, class.curriculum_id
  • Inner Unique: true
  • Buffers: shared hit=3,034
5. 0.262 2.562 ↓ 1.9 528 1

Nested Loop (cost=1.00..1,153.41 rows=279 width=127) (actual time=0.036..2.562 rows=528 loops=1)

  • Output: user_student.id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id, user_student_location.province, user_student_location.city, curriculum_record.class_id
  • Buffers: shared hit=1,450
6. 0.099 0.600 ↑ 1.0 100 1

Nested Loop Left Join (cost=0.58..403.37 rows=100 width=103) (actual time=0.026..0.600 rows=100 loops=1)

  • Output: user_student.id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id, user_student_location.province, user_student_location.city
  • Inner Unique: true
  • Buffers: shared hit=636
7. 0.301 0.301 ↑ 1.0 100 1

Index Scan Backward using user_student_pkey on common.user_student (cost=0.29..172.42 rows=100 width=89) (actual time=0.019..0.301 rows=100 loops=1)

  • Output: user_student.id, user_student.user_student_id, user_student.name, user_student.remark_name, user_student.grade_id, user_student.phone, user_student.zb_open_id
  • Index Cond: (user_student.id = ANY ('{158065,158063,158061,158060,158059,158058,158057,158056,158055,157784,157723,157721,157720,157195,157190,151148,150067,150057,150055,150046,149956,149865,149864,149863,149862,149861,149860,149859,149858,149857,149856,149855,149854,149853,149852,149851,149850,149849,149848,149847,149846,149845,149844,149843,149842,149841,149840,149839,149838,149837,149836,149835,149834,149833,149832,149831,149830,149829,149828,149827,149826,149825,149824,149823,149822,149821,149820,149819,149818,149817,149816,149815,149814,149813,149812,149811,149810,149809,149808,149807,149806,149805,149804,149803,149802,149801,149800,149799,149798,149797,149796,149795,149794,149793,149792,149791,149790,149789,149788,149787}'::bigint[]))
  • Buffers: shared hit=336
8. 0.200 0.200 ↑ 1.0 1 100

Index Scan using user_student_location_user_student_id_idx on common.user_student_location (cost=0.29..2.31 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=100)

  • Output: user_student_location.id, user_student_location.created_at, user_student_location.updated_at, user_student_location.user_student_id, user_student_location.province, user_student_location.city, user_student_location.source, user_student_location.comment, user_student_location.location_id
  • Index Cond: (user_student_location.user_student_id = user_student.id)
  • Buffers: shared hit=300
9. 1.700 1.700 ↑ 1.2 5 100

Index Scan using curriculum_record_student_id_status_idx on multi.curriculum_record (cost=0.42..7.44 rows=6 width=49) (actual time=0.013..0.017 rows=5 loops=100)

  • Output: curriculum_record.student_id, curriculum_record.class_id
  • Index Cond: ((curriculum_record.student_id)::text = (user_student.user_student_id)::text)
  • Filter: (curriculum_record.status = ANY ('{2,5}'::integer[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=814
10. 4.224 4.224 ↑ 1.0 1 528

Index Scan using class_class_id_idx on multi.class (cost=0.29..0.30 rows=1 width=82) (actual time=0.008..0.008 rows=1 loops=528)

  • Output: class.id, class.class_id, class.created_at, class.updated_at, class.alias, class.curriculum_id, class.tutor_id, class.num_of_students, class.paid_num, class.tutor_extra_role, class.order_num, class.left_num
  • Index Cond: ((class.class_id)::text = (curriculum_record.class_id)::text)
  • Buffers: shared hit=1,584
11. 3.168 3.168 ↑ 1.0 1 528

Index Scan using curriculum_curriculum_id_idx on multi.curriculum (cost=0.28..0.34 rows=1 width=105) (actual time=0.006..0.006 rows=1 loops=528)

  • Output: curriculum.id, curriculum.created_at, curriculum.updated_at, curriculum.title, curriculum.curriculum_id, curriculum.lecturer_id, curriculum.arrangement_start, curriculum.arrangement_end, curriculum.price, curriculum.total_lesson_num, curriculum.last_lesson_id, curriculum.hit_shelf_time, curriculum.remove_off_shelf_time, curriculum.end_class_time, curriculum.topped_status, curriculum.limit_num, curriculum.curr_registration_num, curriculum.paid_num, curriculum.left_num, curriculum.inflated_num, curriculum.cover_img_url, curriculum.publisher, curriculum.training_goal, curriculum.broadcast_status, curriculum.live_lesson_id, curriculum.cover_video, curriculum.cover_video_uploading_status, curriculum.apply_to, curriculum.semester_id, curriculum.pre_hit_time, curriculum.pre_off_time, curriculum.course_status, curriculum.auto_hit_shelf_time, curriculum.auto_off_shelf_time, curriculum.cover_img, curriculum.curriculum_time_remark, curriculum.send_purchase_email, curriculum.publisher_id, curriculum.sale_status, curriculum.miniprogram_putaway, curriculum.miniprogram_lecture_introduce, curriculum.miniprogram_lecture_cover, curriculum.display_left_num, curriculum.current_display_left_num, curriculum.allow_not_remind_tutor, curriculum.miniprogram_lecture_share_content
  • Index Cond: ((curriculum.curriculum_id)::text = (class.curriculum_id)::text)
  • Buffers: shared hit=1,584
12. 1.584 1.584 ↑ 1.0 1 528

Index Only Scan using curriculum_genre_curriculum_id_genre_idx on multi.curriculum_genre (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=528)

  • Output: curriculum_genre.curriculum_id, curriculum_genre.genre
  • Index Cond: (curriculum_genre.curriculum_id = curriculum.id)
  • Filter: (curriculum_genre.genre = ANY ('{1,8}'::bigint[]))
  • Rows Removed by Filter: 0
  • Heap Fetches: 395
  • Buffers: shared hit=1,452
Planning time : 2.175 ms
Execution time : 13.681 ms