explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6mRJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 7,893.535 ↑ 1.0 3 1

Limit (cost=666,572.58..666,572.58 rows=3 width=12) (actual time=7,893.534..7,893.535 rows=3 loops=1)

2. 0.589 7,893.534 ↑ 195,573.3 3 1

Sort (cost=666,572.58..666,865.94 rows=586,720 width=12) (actual time=7,893.533..7,893.534 rows=3 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 61.103 7,892.945 ↑ 131.9 4,447 1

GroupAggregate (cost=662,415.69..665,055.93 rows=586,720 width=12) (actual time=7,708.478..7,892.945 rows=4,447 loops=1)

  • Group Key: (COALESCE(matched_courses.university_id, matched_basic_matchables.university_id, university_matches_1.matchable_id))
4. 643.972 7,831.842 ↓ 1.0 592,550 1

Sort (cost=662,415.69..662,709.05 rows=586,720 width=12) (actual time=7,707.822..7,831.842 rows=592,550 loops=1)

  • Sort Key: (COALESCE(matched_courses.university_id, matched_basic_matchables.university_id, university_matches_1.matchable_id))
  • Sort Method: external merge Disk: 8096kB
5. 238.376 7,187.870 ↓ 1.0 592,550 1

Hash Left Join (cost=327,035.55..651,172.77 rows=586,720 width=12) (actual time=2,453.434..7,187.870 rows=592,550 loops=1)

  • Hash Cond: (university_matches_1.matchable_id = matched_basic_matchables.id)
  • Join Filter: ((university_matches_1.matchable_type)::text = 'BasicMatchable'::text)
  • Rows Removed by Join Filter: 357286
6. 280.879 6,940.597 ↓ 1.0 592,550 1

Hash Left Join (cost=326,103.48..647,747.14 rows=586,720 width=23) (actual time=2,444.357..6,940.597 rows=592,550 loops=1)

  • Hash Cond: (university_matches_1.matchable_id = matched_courses.id)
  • Join Filter: ((university_matches_1.matchable_type)::text = 'UniversityCourse'::text)
  • Rows Removed by Join Filter: 99868
7. 2,546.000 6,579.044 ↓ 1.0 592,550 1

Hash Join (cost=316,876.14..636,026.24 rows=586,720 width=19) (actual time=2,362.475..6,579.044 rows=592,550 loops=1)

  • Hash Cond: (university_matches_1.id = university_matches.id)
8. 1,675.321 1,675.321 ↓ 1.0 10,281,629 1

Seq Scan on university_matches university_matches_1 (cost=0.00..309,687.96 rows=10,269,319 width=23) (actual time=0.016..1,675.321 rows=10,281,629 loops=1)

9. 161.693 2,357.723 ↓ 1.0 592,550 1

Hash (cost=314,822.62..314,822.62 rows=586,720 width=4) (actual time=2,357.723..2,357.723 rows=592,550 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 29024kB
10. 2,196.030 2,196.030 ↓ 1.0 592,550 1

Seq Scan on university_matches (cost=0.00..314,822.62 rows=586,720 width=4) (actual time=0.011..2,196.030 rows=592,550 loops=1)

  • Filter: (list_status = 1)
  • Rows Removed by Filter: 9689079
11. 39.100 80.674 ↓ 1.0 241,611 1

Hash (cost=8,382.85..8,382.85 rows=241,283 width=8) (actual time=80.674..80.674 rows=241,611 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11486kB
12. 41.574 41.574 ↓ 1.0 241,611 1

Seq Scan on university_courses matched_courses (cost=0.00..8,382.85 rows=241,283 width=8) (actual time=0.005..41.574 rows=241,611 loops=1)

13. 4.462 8.897 ↓ 1.0 28,578 1

Hash (cost=833.50..833.50 rows=28,165 width=8) (actual time=8.897..8.897 rows=28,578 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1373kB
14. 4.435 4.435 ↓ 1.0 28,578 1

Seq Scan on basic_matchables matched_basic_matchables (cost=0.00..833.50 rows=28,165 width=8) (actual time=0.005..4.435 rows=28,578 loops=1)

Planning time : 1.755 ms
Execution time : 7,897.556 ms