explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vBXw

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8,193.170 ↑ 1.0 3 1

Limit (cost=673,037.01..673,037.02 rows=3 width=12) (actual time=8,193.169..8,193.170 rows=3 loops=1)

2. 0.590 8,193.168 ↑ 195,573.3 3 1

Sort (cost=673,037.01..673,330.37 rows=586,720 width=12) (actual time=8,193.168..8,193.168 rows=3 loops=1)

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

GroupAggregate (cost=668,880.12..671,520.36 rows=586,720 width=12) (actual time=7,993.785..8,192.578 rows=4,447 loops=1)

  • Group Key: (COALESCE(matched_courses.university_id, matched_basic_matchables.university_id, university_matches_1.matchable_id))
4. 661.354 8,133.089 ↓ 1.0 592,543 1

Sort (cost=668,880.12..669,173.48 rows=586,720 width=12) (actual time=7,993.112..8,133.089 rows=592,543 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. 248.703 7,471.735 ↓ 1.0 592,543 1

Hash Join (cost=331,299.79..657,637.21 rows=586,720 width=12) (actual time=2,491.236..7,471.735 rows=592,543 loops=1)

  • Hash Cond: (university_matches_1.application_id = applications.id)
6. 234.655 7,159.868 ↓ 1.0 592,543 1

Hash Left Join (cost=327,035.55..651,172.77 rows=586,720 width=16) (actual time=2,426.847..7,159.868 rows=592,543 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: 357279
7. 281.642 6,916.054 ↓ 1.0 592,543 1

Hash Left Join (cost=326,103.48..647,747.14 rows=586,720 width=27) (actual time=2,417.517..6,916.054 rows=592,543 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: 99863
8. 2,545.253 6,553.129 ↓ 1.0 592,543 1

Hash Join (cost=316,876.14..636,026.24 rows=586,720 width=23) (actual time=2,334.979..6,553.129 rows=592,543 loops=1)

  • Hash Cond: (university_matches_1.id = university_matches.id)
9. 1,677.754 1,677.754 ↓ 1.0 10,281,547 1

Seq Scan on university_matches university_matches_1 (cost=0.00..309,687.96 rows=10,269,319 width=27) (actual time=0.007..1,677.754 rows=10,281,547 loops=1)

10. 159.747 2,330.122 ↓ 1.0 592,543 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 29024kB
11. 2,170.375 2,170.375 ↓ 1.0 592,543 1

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

  • Filter: (list_status = 1)
  • Rows Removed by Filter: 9689004
12. 39.574 81.283 ↓ 1.0 241,611 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 11486kB
13. 41.709 41.709 ↓ 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.709 rows=241,611 loops=1)

14. 4.586 9.159 ↓ 1.0 28,578 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1373kB
15. 4.573 4.573 ↓ 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.006..4.573 rows=28,578 loops=1)

16. 25.442 63.164 ↓ 1.0 153,580 1

Hash (cost=3,726.72..3,726.72 rows=153,575 width=4) (actual time=63.164..63.164 rows=153,580 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7448kB
17. 37.722 37.722 ↓ 1.0 153,580 1

Seq Scan on applications (cost=0.00..3,726.72 rows=153,575 width=4) (actual time=0.021..37.722 rows=153,580 loops=1)

Planning time : 2.673 ms
Execution time : 8,197.489 ms