Efficient Version query on a subset of tracked objects

[airguru]

Hello,

I have encountered a use case, where I need to query for all changes in some subset of tracked objects. Consider this example:

content_type = ContentType.objects.get_for_model(Model)
relevant_object_ids = Model.objects.filter( <some_condition> ).annotate(pk_str=Cast('pk', CharField())).values('pk_str')

relevant_versions = Version.objects.filter(content_type=content_type, object_id__in=Subquery(relevant_object_ids))

My Versions table is fairly big and contains over 8-10M versions for this content_type. This query is very slow, unless an database index for (content_type, object_id) is added.

Do you think adding such an index might be considered as a feature?

Actually, my exact use case is a level more complicated. I need to produce an audit report, where I list all objects that have been changed. Then go through all the changes and filter out only the relevant ones.

My approximate real code looks like this:

deals_in_range = Deal.objects.filter(date__gte=start_date, date__lt=end_date, company=company).exclude(counterparty=company).annotate(pk_str=Cast('pk', CharField())).values('pk_str')

content_type = ContentType.objects.get_for_model(Deal)

relevant_object_ids = Version.objects.filter(
        content_type=content_type, object_id__in=Subquery(deals_in_month),
    ).values('object_id').annotate(
        version_count=Count('object_id')
    ).filter(version_count__gt=1).values('object_id')

relevant_versions = Version.objects.filter(content_type=content_type, object_id__in=Subquery(relevant_object_ids))

I wonder if there is a better approach to this.

[stianjensen]

Also seeing slow performance using get_for_object to list out all versions of a specific object. I think adding an index for content_type,object_id (possibly including db as well, as I see that's being filtered on in those queries) would make a lot of sense, and really help make listing out version history in the context of a specific object a lot more performant.

[etianen]

We already have an index on "db", "content_type", "object_id", "revision". I wonder whether this wouldn't work for your query if you were also filtering on db="default"

[stianjensen]

Good point. Switching queries to order by revision_id (which is part of the index) instead of the default order_by('pk') seems to avoid an extra sort and then rely only on that unique index, which does help quite a bit!