N+1 queries due to queryset.count() if cache is empty

[mronian]

Describe the Bug

In a nested ListConnectionWithTotalCount, if the child has total_count in the query, if there is a parent node with no related child nodes, there is a queryset.count() triggered. This leads to N+1 queries like scenario if very few parent nodes have child nodes.

strawberry-django/strawberry_django/pagination.py

Lines 269 to 298 in 269954c

	def get_total_count(queryset: QuerySet) -> int:
	"""Get the total count of a queryset.
	Try to get the total count from the queryset cache, if it's optimized by
	prefetching. Otherwise, fallback to the `QuerySet.count()` method.
	"""
	from strawberry_django.optimizer import is_optimized_by_prefetching
	if is_optimized_by_prefetching(queryset):
	results = queryset._result_cache # type: ignore
	if results:
	try:
	return results[0]._strawberry_total_count
	except AttributeError:
	warnings.warn(
	(
	"Pagination annotations not found, falling back to QuerySet resolution. "
	"This might cause n+1 issues..."
	),
	RuntimeWarning,
	stacklevel=2,
	)
	# If we have no results, we can't get the total count from the cache.
	# In this case we will remove the pagination filter to be able to `.count()`
	# the whole queryset with its original filters.
	queryset = remove_window_pagination(queryset)
	return queryset.count()

Instead, if the queryset is optimized (is_optimized_by_prefetching is True) and there is no offset / limit mentioned then it should just return 0 assuming no nodes were found, calling the count() method only for unoptimized querysets.

[bellini666]

Hi @mronian ,

I tried here adding a:

elif results is not None:
    return 0

And an issue can be seen in this line: https://github.com/strawberry-graphql/strawberry-django/blob/fix-count/tests/test_optimizer.py#L1622-L1622

If we find a way to prevent that (i.e. that test to not fail), then we can improve this