Postgres 10 - Query slows down with order by
PostgreSQL thinks that it will be faster avoiding the sort for the ORDER BY by scanning the rows in the sort order and discarding rows until it finds one with the right school_id.

There can be two reasons why this takes longer than expected:


The table statistics is off, and PostgreSQL overestimates the number of rows with that school_id.

Calculate new statistics, possibly with a higher value for default_statistics_target, to verify if that is the problem:

ANALYZE students;

The (many) rows with the correct school_id all happen to have a rather high id, so PostgreSQL has to scan way more rows than it bargained for until it finds a match.

In that case, you should modify the ORDER BY clause so that PostgreSQL cannot use the wrong index:

... ORDER BY id + 0