explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5kyY

Settings
# exclusive inclusive rows x rows loops node
1. 6.019 726.733 ↓ 98.5 6,697 1

Nested Loop Left Join (cost=95,763.30..98,109.82 rows=68 width=65) (actual time=289.640..726.733 rows=6,697 loops=1)

2. 4.278 573.380 ↓ 98.5 6,697 1

Nested Loop Left Join (cost=95,762.74..97,524.68 rows=68 width=28) (actual time=289.606..573.380 rows=6,697 loops=1)

3. 13.129 394.980 ↓ 98.5 6,697 1

Nested Loop Left Join (cost=95,753.34..96,065.03 rows=68 width=24) (actual time=289.580..394.980 rows=6,697 loops=1)

4. 8.432 321.578 ↓ 98.5 6,697 1

Finalize GroupAggregate (cost=95,752.91..95,761.24 rows=68 width=20) (actual time=289.540..321.578 rows=6,697 loops=1)

  • Group Key: user_course.school_id, user_course.crs_dept, user_course.crs_num
5. 0.000 313.146 ↓ 156.0 8,734 1

Gather Merge (cost=95,752.91..95,760.00 rows=56 width=20) (actual time=289.528..313.146 rows=8,734 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 8.313 820.860 ↓ 104.0 2,911 3

Partial GroupAggregate (cost=94,752.88..94,753.51 rows=28 width=20) (actual time=268.086..273.620 rows=2,911 loops=3)

  • Group Key: user_course.school_id, user_course.crs_dept, user_course.crs_num
7. 97.110 812.547 ↓ 153.1 4,288 3

Sort (cost=94,752.88..94,752.95 rows=28 width=12) (actual time=268.073..270.849 rows=4,288 loops=3)

  • Sort Key: user_course.school_id, user_course.crs_dept, user_course.crs_num
  • Sort Method: quicksort Memory: 276kB
8. 482.108 715.437 ↓ 153.1 4,288 3

Parallel Bitmap Heap Scan on user_course (cost=48,419.78..94,752.21 rows=28 width=12) (actual time=225.536..238.479 rows=4,288 loops=3)

  • Recheck Cond: ((school_id = ANY ('{100005,100082,100143,100151,100211,100267,100326,100332,100347,100418,100431,100476,100496,100722,100832,100900,100911,100966,100979,100991,101023,101048,101056,101135,101161,101172,101209,101215,101261,101312,101472,101487,101507,101589,101628,101768,101831,101854,101861,101875,101893,101900,101935,101968,102063,102178,102203,102261,102313,102364,102390,102404,102412,102464,102495,102517,102526,102542,102710,102785,102792,102806,102815,102849,102866,102871,102876,102993,103018,103110,103271,103284,103461,103478,103514,103533,103536,103542,103616,103637,103678,103694,103748,103821,103847,103943,103994,104164,104187,104193,104214,104244,104312,104327,104338,104346,104348,104387,104398,104409,104468,104531,104641,104642,104673,104686,104759,104881,104888,104894,104939,105014,105029,105034,105038,105054,105062,105076,105087,105159,105205,105211,105222,105235,105262,105350,105394,105497,105509,105652,105761,105967,106303,106326,106343,106410,106413,106415,106533,106543,106567,106683,106727,106779,106796,106833,106906,106948,106982,107016,107057,107065,107093,107119,107138,107166,107187,107380,107415,107455,107562,107626,107670,107723,107726,107742,107781,107797,107820,107838,107889,107906,107968,107975,107987,107997,108018,108040,108098,108112,108141,108163,108190,108191,108225,108249,108284,108302,108310,108338,108351,108360,108385,108391,108426,108468,108533,108703,108808,108832,108839,108840,108845,108860,108893,108897,108929,108943,108962,108963,109080,1000000,1000001,1000002}'::integer[])) AND (last_mod_date >= '2019-02-13 00:00:00'::timestamp without time zone) AND (last_mod_date <= '2019-03-14 00:00:00'::timestamp without time zone))
  • Filter: ((course_type = 0) AND (COALESCE(bundle_seq_no, 0) = 0))
  • Rows Removed by Filter: 1353
  • Heap Blocks: exact=513
9. 233.329 233.329 ↓ 1.2 17,029 1

Bitmap Index Scan on ix_user_course_latest_guid (cost=0.00..48,419.76 rows=14,633 width=0) (actual time=233.329..233.329 rows=17,029 loops=1)

  • Index Cond: ((school_id = ANY ('{100005,100082,100143,100151,100211,100267,100326,100332,100347,100418,100431,100476,100496,100722,100832,100900,100911,100966,100979,100991,101023,101048,101056,101135,101161,101172,101209,101215,101261,101312,101472,101487,101507,101589,101628,101768,101831,101854,101861,101875,101893,101900,101935,101968,102063,102178,102203,102261,102313,102364,102390,102404,102412,102464,102495,102517,102526,102542,102710,102785,102792,102806,102815,102849,102866,102871,102876,102993,103018,103110,103271,103284,103461,103478,103514,103533,103536,103542,103616,103637,103678,103694,103748,103821,103847,103943,103994,104164,104187,104193,104214,104244,104312,104327,104338,104346,104348,104387,104398,104409,104468,104531,104641,104642,104673,104686,104759,104881,104888,104894,104939,105014,105029,105034,105038,105054,105062,105076,105087,105159,105205,105211,105222,105235,105262,105350,105394,105497,105509,105652,105761,105967,106303,106326,106343,106410,106413,106415,106533,106543,106567,106683,106727,106779,106796,106833,106906,106948,106982,107016,107057,107065,107093,107119,107138,107166,107187,107380,107415,107455,107562,107626,107670,107723,107726,107742,107781,107797,107820,107838,107889,107906,107968,107975,107987,107997,108018,108040,108098,108112,108141,108163,108190,108191,108225,108249,108284,108302,108310,108338,108351,108360,108385,108391,108426,108468,108533,108703,108808,108832,108839,108840,108845,108860,108893,108897,108929,108943,108962,108963,109080,1000000,1000001,1000002}'::integer[])) AND (last_mod_date >= '2019-02-13 00:00:00'::timestamp without time zone) AND (last_mod_date <= '2019-03-14 00:00:00'::timestamp without time zone))
10. 60.273 60.273 ↓ 0.0 0 6,697

Index Only Scan using popular_course_match_pkey on popular_course_match pc (cost=0.43..4.46 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=6,697)

  • Index Cond: ((source_id = user_course.school_id) AND (crs_dept = (user_course.crs_dept)::text) AND (crs_num = (user_course.crs_num)::text) AND (target_id = 1000000))
  • Heap Fetches: 0
11. 6.697 174.122 ↓ 0.0 0 6,697

Limit (cost=9.40..21.45 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=6,697)

12. 4.676 167.425 ↓ 0.0 0 6,697

Nested Loop (cost=9.40..21.45 rows=1 width=4) (actual time=0.025..0.025 rows=0 loops=6,697)

13. 20.091 154.031 ↑ 1.0 1 6,697

Bitmap Heap Scan on sherpa_evaluation_request ser (cost=9.11..13.13 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=6,697)

  • Recheck Cond: ((source_school_id = user_course.school_id) AND (target_school_id = 1000000))
  • Heap Blocks: exact=2276
14. 14.836 133.940 ↓ 0.0 0 6,697

BitmapAnd (cost=9.11..9.11 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=6,697)

15. 73.667 73.667 ↓ 9.6 134 6,697

Bitmap Index Scan on ix_eval_req_source (cost=0.00..4.39 rows=14 width=0) (actual time=0.011..0.011 rows=134 loops=6,697)

  • Index Cond: (source_school_id = user_course.school_id)
16. 45.437 45.437 ↓ 3.0 71 6,491

Bitmap Index Scan on ix_eval_req_target (cost=0.00..4.47 rows=24 width=0) (actual time=0.007..0.007 rows=71 loops=6,491)

  • Index Cond: (target_school_id = 1000000)
17. 8.718 8.718 ↓ 0.0 0 4,359

Index Scan using ix_eval_req_course_req on sherpa_evaluation_request_course serc (cost=0.29..8.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=4,359)

  • Index Cond: (request_seq_no = ser.int_seq_no)
  • Filter: (((course_dept)::text = (user_course.crs_dept)::text) AND ((course_code)::text = (user_course.crs_num)::text))
  • Rows Removed by Filter: 1
18. 6.697 147.334 ↑ 1.0 1 6,697

Limit (cost=0.56..8.58 rows=1 width=45) (actual time=0.022..0.022 rows=1 loops=6,697)

19. 140.637 140.637 ↑ 1.0 1 6,697

Index Scan using ix_user_course_latest_guid on user_course uc2 (cost=0.56..8.58 rows=1 width=45) (actual time=0.021..0.021 rows=1 loops=6,697)

  • Index Cond: ((school_id = user_course.school_id) AND ((crs_dept)::text = (user_course.crs_dept)::text) AND ((crs_num)::text = (user_course.crs_num)::text) AND (data_source = 'S'::bpchar))