What is the best index for a "last modified by user" query

I have a Django query like

Posts.objects.filter(user=user).order_by('-last_modified')

What is the best type of index to support this query? I see two options:

  1. Index(fields=['user_id', '-last_modified'])
  2. Index(fields=['-last_modified'])

Given that Django automatically indexes foreign keys such as user_id, is there any benefit to 1) vs 2)? It seems like 1) would result in fewer items being traversed by the DB, but maybe DBs are "smart enough" to join two separate indexes together and as a result the advantage will be minimal.

My question is theoretical in nature: which index is the better option, in principle?

Back to Top