I have users who create (or receive) transactions. The transaction hierarchy I have is a multi-table inheritance, with Transaction as the base model containing the common fields between all transaction types, such as User (FK), amount, etc. I have several transaction types, which extend the Transaction model with type specific data.
For the sake of this example, a simplified structure illustrating my problem can be found below.
from model_utils.managers import InheritanceManager
class User(models.Model):
pass
class Transaction(models.Model):
DEPOSIT = 'deposit'
WITHDRAWAL = 'withdrawal'
TRANSFER = 'transfer'
TYPES = (
(DEPOSIT, DEPOSIT),
(WITHDRAWAL, WITHDRAWAL),
(TRANSFER, TRANSFER),
)
type = models.CharField(max_length=24, choices=TYPES)
user = models.ForeignKey(User)
amount = models.PositiveIntegerField()
objects = InheritanceManager()
class Meta:
indexes = [
models.Index(fields=['user']),
models.Index(fields=['type'])
]
class Withdrawal(Transaction):
TYPE = Transaction.WITHDRAWAL
bank_account = models.ForeignKey(BankAccount)
class Deposit(Transaction):
TYPE = Transaction.DEPOSIT
card = models.ForeignKey(Card)
class Transfer(Transaction):
TYPE = Transaction.Transfer
recipient = models.ForeignKey(User)
class Meta:
indexes = [
models.Index(fields=['recipient'])
]
I then set each transaction's type in the inherited model's .save() method. This is all fine and well.
The problem comes in when I would like to fetch the a user's transactions. Specifically, I require the sub-model instances (deposits, transfers and withdrawals), rather than the base model (transactions). I also require transactions that the user both created themselves AND transfers they have received. For the former I use django-model-utils's fantastic IneritanceManager, which works great. Except that when I include the filtering on the transfer submodel's recipient FK field the DB query increases by an order of magnitude.
As illustrated above I have placed indexes on the Transaction user column and the Transfer recipient column. But it appeared to me that what I may need is an index on the Transaction subtype, if that is at all possible. I have attempted to achieve this effect by putting an index on the Transaction type field and including it in the query, as you will see below, but this appears to have no effect. Furthermore, I use .select_related() for the user objects since they are required in the serializations.
The query is structured as such:
from django.db.models import Q
queryset = Transaction.objects.select_related(
'user',
'transfer__recipient'
).select_subclasses().filter(
Q(user=request.user) |
Q(type=Transaction.TRANSFER, transfer__recipient=request.user)
).order_by('-id')
So my question is, why is there an order of magnitude difference on the DB query when including the Transfer.recipient in the query? Have I missed something? Am I doing something silly? Or is there a way I can optimise this further?
