explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yBfyl : Is universities_and_university_matches filtered before joining

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.888 ↑ 3.0 1 1

Limit (cost=48.53..48.53 rows=3 width=8) (actual time=0.888..0.888 rows=1 loops=1)

2. 0.021 0.888 ↑ 3.0 1 1

Sort (cost=48.53..48.53 rows=3 width=8) (actual time=0.888..0.888 rows=1 loops=1)

  • Sort Key: (count(DISTINCT university_countries.id)) DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.014 0.867 ↑ 3.0 1 1

GroupAggregate (cost=48.51..48.52 rows=3 width=8) (actual time=0.867..0.867 rows=1 loops=1)

  • Group Key: applications.id
4. 0.025 0.853 ↓ 4.0 12 1

Sort (cost=48.51..48.51 rows=3 width=8) (actual time=0.851..0.853 rows=12 loops=1)

  • Sort Key: applications.id
  • Sort Method: quicksort Memory: 25kB
5. 0.007 0.828 ↓ 4.0 12 1

Nested Loop Left Join (cost=0.65..48.50 rows=3 width=8) (actual time=0.269..0.828 rows=12 loops=1)

6. 0.001 0.773 ↓ 4.0 12 1

Nested Loop Left Join (cost=0.62..48.39 rows=3 width=7) (actual time=0.249..0.773 rows=12 loops=1)

7. 0.008 0.664 ↓ 4.0 12 1

Nested Loop Left Join (cost=0.56..47.85 rows=3 width=8) (actual time=0.221..0.664 rows=12 loops=1)

8. 0.007 0.344 ↓ 3.0 12 1

Nested Loop (cost=0.33..41.41 rows=4 width=8) (actual time=0.160..0.344 rows=12 loops=1)

9. 0.001 0.069 ↑ 1.0 1 1

Nested Loop (cost=0.22..12.24 rows=1 width=4) (actual time=0.068..0.069 rows=1 loops=1)

10. 0.001 0.047 ↑ 1.0 1 1

Nested Loop (cost=0.17..8.18 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=1)

11. 0.028 0.028 ↑ 1.0 1 1

Index Scan using index_users_on_email on users students (cost=0.08..4.09 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: ((email)::text = 'charlieliu2001@yahoo.com'::text)
  • Filter: (role = 1)
12. 0.018 0.018 ↑ 1.0 1 1

Index Scan using index_applications_on_student_id on applications (cost=0.08..4.09 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (student_id = students.id)
13. 0.021 0.021 ↑ 1.0 1 1

Index Scan using schools_pkey on schools (cost=0.06..4.06 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (id = students.school_id)
  • Filter: (account_type = 0)
14. 0.268 0.268 ↑ 1.1 12 1

Index Scan using index_university_matches_on_application_id on university_matches (cost=0.11..29.13 rows=13 width=8) (actual time=0.091..0.268 rows=12 loops=1)

  • Index Cond: (application_id = applications.id)
  • Filter: (list_status = 1)
  • Rows Removed by Filter: 82
15. 0.012 0.312 ↑ 1.0 1 12

Nested Loop Left Join (cost=0.23..1.61 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=12)

  • Join Filter: ((university_matches_1.matchable_type)::text = 'BasicMatchable'::text)
  • Rows Removed by Join Filter: 0
16. 0.024 0.264 ↑ 1.0 1 12

Nested Loop Left Join (cost=0.17..1.54 rows=1 width=27) (actual time=0.022..0.022 rows=1 loops=12)

  • Join Filter: ((university_matches_1.matchable_type)::text = 'UniversityCourse'::text)
  • Rows Removed by Join Filter: 0
17. 0.084 0.084 ↑ 1.0 1 12

Index Scan using university_matches_pkey on university_matches university_matches_1 (cost=0.09..1.45 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=12)

  • Index Cond: (university_matches.id = id)
18. 0.156 0.156 ↑ 1.0 1 12

Index Scan using university_courses_pkey on university_courses matched_courses (cost=0.08..0.09 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=12)

  • Index Cond: (university_matches_1.matchable_id = id)
19. 0.036 0.036 ↓ 0.0 0 12

Index Scan using basic_matchables_pkey on basic_matchables matched_basic_matchables (cost=0.06..0.06 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=12)

  • Index Cond: (university_matches_1.matchable_id = id)
20. 0.108 0.108 ↑ 1.0 1 12

Index Scan using universities_pkey on universities (cost=0.06..0.18 rows=1 width=7) (actual time=0.007..0.009 rows=1 loops=12)

  • Index Cond: ((COALESCE(matched_courses.university_id, matched_basic_matchables.university_id, university_matches_1.matchable_id)) = id)
21. 0.048 0.048 ↑ 1.0 1 12

Index Scan using index_countries_on_alpha2 on countries university_countries (cost=0.03..0.03 rows=1 width=7) (actual time=0.004..0.004 rows=1 loops=12)

  • Index Cond: ((universities.country_code)::text = (alpha2)::text)
Planning time : 5.749 ms
Execution time : 1.134 ms