Why is this Django query annotation with float division so slow?

Viewed 52

I have a query with subqueries and annotated with derived calculations:

base_query = PlayerReport.objects.filter(
    # ... some filter conditions
)

# Create a sum on the base query, to join later
points = base_query.filter(pk=OuterRef('pk')).annotate(
    points=Sum('player_match__score')
)

prices = PlayerPrice.objects.filter(
    player_report=OuterRef('pk')
).order_by('-date')

# The final_query to evaluate
final_query = base_query.annotate(
    # Sum of total points for the player
    points=Subquery(
        points.values('points')
        output_field=FloatField()
    )
    # Latest player price
    price=Subquery(
        price.values('price')[:1]
        output_field=FloatField()
    )
    # This causes a big performance hit
    price_per_point = _division_exp('points', 'price')

    # _division_exp is used for other derived calcs
    # these do not cause noticeable performance problems
    derived_field2 = _division_exp('field1', 'field2')
    derived_field3 = _division_exp('field3', 'field4')
)

The _division_exp is a convenience helper method that helps us create an expression that guards from zero division.

def _division_exp(num_key, denom_key):
    return Case(
        When(**{denom_key: 0, 'then': 0}),
        default=ExpressionWrapper(
            F(num_key) / F(denom_key),
            output_field=FloatField()
        )
    )

There are many uses of this division helper, but only the price_per_point causes any sort of problem.

Observed query performance on my local machine is:

without price_per_point: ~9s

with price_per_point: ~16s

I've repeated this many times, and I'm sure I've singled it out as what's causing the problem.

I've also tried pasting raw queries in a postgres console and the same effects can be observed.

The difference between this and other divisions among my annotations is that this one uses the results of two subqueries, whereas the others don't (with the exception of one, which uses points in the numerand)

Why does this occur, and can the query be optimized?

0 Answers
Related