Queryset with primary key sort takes 10+ seconds

[MaartendeRuyter]

When using DRF PageNumberPagination class you are encouraged to add a ordering key to your model.

I experience very long response times for the paginated get requests on a model with 4 million + entries in the database when using the models ID field as ordering key. But when I changed the ordering key to another field in the model it all worked fine. That seems weird to me. See sql query result retrieved using django-silk

USING 'id' as ordering key

SELECT "weather_weatherobservation"."id",
"weather_weatherobservation"."weather_station_id",
"weather_weatherobservation"."observation_type_id",
"weather_weatherobservation"."search_key",
"weather_weatherobservation"."search_key_type",
"weather_weatherobservation"."data",
"weather_weatherobservation"."observation_date"
FROM "weather_weatherobservation"
WHERE "weather_weatherobservation"."weather_station_id" IN (917)
ORDER BY "weather_weatherobservation"."id" ASC
LIMIT 11
12706.724ms
0 joins

USING 'search_key' as ordering keys

SELECT "weather_weatherobservation"."id",
"weather_weatherobservation"."weather_station_id",
"weather_weatherobservation"."observation_type_id",
"weather_weatherobservation"."search_key",
"weather_weatherobservation"."search_key_type",
"weather_weatherobservation"."data",
"weather_weatherobservation"."observation_date"
FROM "weather_weatherobservation"
WHERE "weather_weatherobservation"."weather_station_id" IN (917)
ORDER BY "weather_weatherobservation"."search_key" ASC
LIMIT 11
4.637ms
0 joins

Total nr of weatherobservation object in DB > 4 million

Other INFO

• database postgress based upon docker postgis:latest image
• Django version 3.1.8
• DRF version 3.11.2
• psycopg2 2.8.6

Code used:

class WeatherObservation(models.Model):
"""
Model defines weather observation for a weather station
"""
.... other fields not shown to keep thing brief

search_key = models.CharField(max_length=128)

class Meta:
    ordering = ['id']
    indexes = [
        models.Index(fields=['search_key', 'search_key_type']),
    ]