The index that is missing

A college placement dashboard runs one query on every page load:

sql SELECT s.name, s.department, COUNT(a.id) AS applications FROM students s LEFT JOIN applications a ON a.student_id = s.id WHERE s.batch_year = 2026 GROUP BY s.id, s.name, s.department ORDER BY applications DESC LIMIT 20;

It returned in 40 ms when the table held 800 students. It now takes 9 seconds with 24,000 students and 310,000 applications, and the dashboard is unusable.

The team checks the plan and finds a sequential scan on students, then a hash join against a sequential scan of applications.

There is one index on students(id) (the primary key) and one on applications(id). Nothing else. Statistics were last collected when the table held 800 rows.