HistoricForeignKey is very slow

[gabn88]

Describe the bug
We have tens of thousands of instances (not millions), but the historic foreignkey loads already very slow (+60 seconds)

To Reproduce
Create a Model (in our case with 2 historicforeignkeys).
Load a few thousand Models and RelatedModels.
Load the historic Model instance. Load the related model. Sometimes it takes a few seconds, but it could also take a full minute.

Expected behavior
It should load within a few ms.

Environment (please complete the following information):

• OS: Debian Bookworm
• Django Simple History Version: 3.5.0
• Django Version: 4.2.9
• Database Version: mariadb 10.11

Additional context
I think the reason is the number of historical items.
We have two historicalforeignkeys on the Model. The one with +/- 30.000 historic rows takes about 5-6 seconds and the one with +/- 600.000 historic rows takes 60+ seconds.

[gabn88]

After digging some more into this, it seems that the HistoricForeignKey first creates a queryset of all possible historic instances (all 600.000) in HistoricForwardManyToOneDescriptor and then filters it down. This seems to be the root cause.

For our model the pk relation will stay the same, so I can take a shortcut -> select the pk and then get the historical object. This reduced the time to get the historic instance from 60+ seconds to a few milliseconds.

[tim-schilling]

@gabn88 thanks for the report! Do you have the time to go the extra step of creating a forkable project that would allow us to reproduce this?