'Django Rest Framework - Doesn't appear to be paginating queryset correctly causing a long wait time before displaying on front end
I am experiencing an issue where it is taking upwards of 30 seconds to receive 25 items in a paginated response. This happens on original load of the page as well as any subsequent page requests via the datatables previous and next buttons.
The model in question has roughly 600k records in it with the request to the api providing a query parameter to filter this by listing type (Letting or Sale). There are other filters applied to the queryset to ignore items that don't fall into the queue leaving 37k records on the Sales side and 90k on lettings that are returned.
My model structue is as follows:
class Listing(models.Model):
date_added = models.DateField(verbose_name='Date Added', auto_now_add=True, auto_now=False)
listing_id = models.CharField(max_length=15, blank=False, primary_key=True)
portal = models.CharField(max_length=30, blank=False)
url = models.URLField(max_length=200, blank=False)
date = models.DateField(verbose_name='Listing Date', auto_now_add=False, auto_now=False, null=True, blank=True)
listing_status = models.CharField(max_length=30, blank=False)
outcode = models.ForeignKey(
'postcode_data.Outcode', blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='+')
sector = models.ForeignKey(
'postcode_data.Sector', blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='+')
address = models.CharField(max_length=200, blank=False)
postcode = models.CharField(max_length=15, blank=False)
description = models.TextField(blank=True)
price = models.DecimalField(max_digits=9, decimal_places=1, default=Decimal(0.0))
property_type = models.CharField(max_length=30, blank=False)
beds = models.PositiveIntegerField(default=0)
baths = models.PositiveIntegerField(default=0)
agent = models.CharField(max_length=150, blank=False)
agent_company = models.CharField(max_length=100, blank=False)
agent_branch = models.CharField(max_length=50, blank=False)
available = models.BooleanField(default=True)
to_buy = models.BooleanField(default=True)
development = models.BooleanField(default=False)
withdrawn = models.BooleanField(default=False)
in_user_queue = models.BooleanField(default=False)
assigned_user = models.ForeignKey(
User, blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='+')
processed = models.BooleanField(default=False)
processed_notes = models.CharField(verbose_name='Notes', max_length=300, null=True, blank=True)
unprocessable = models.BooleanField(default=False)
processed_method = models.ForeignKey(
ListingProcessedMethod, blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='+',
verbose_name='Method')
unprocessable_reason = models.ForeignKey(
ListingUnprocessableReason, blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='+',
verbose_name='Reason')
unprocessable_notes = models.CharField(verbose_name='Notes', max_length=300, null=True, blank=True)
updated_by = models.ForeignKey(
User, blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='+')
updated_timestamp = models.DateTimeField(auto_now=False, auto_now_add=False, null=True, blank=True)
process_probability = models.PositiveIntegerField(
blank=False, null=False, default=0,
validators=[MinValueValidator(0), MaxValueValidator(5)])
def __str__(self):
return self.listing_id
class Meta:
verbose_name_plural = "Listings"
ordering = ['-date', '-process_probability']
indexes = [
models.Index(fields=['portal', ]),
models.Index(fields=['date', ]),
models.Index(fields=['url', ]),
models.Index(fields=['address', ]),
models.Index(fields=['postcode', ]),
models.Index(fields=['agent', ]),
]
My serializer is:
class ListingSerializer(serializers.ModelSerializer):
type_display = serializers.CharField(read_only=True)
process_display = serializers.CharField(read_only=True)
class Meta:
model = Listing
fields = [
'listing_id',
'url',
'address',
'date',
'agent',
'sector',
'in_user_queue',
'process_probability',
'portal',
'assigned_user',
'updated_by',
'type_display',
'process_display',
'updated_timestamp'
]
depth = 0
Viewset is:
class GlobalQueueVS(viewsets.ReadOnlyModelViewSet):
serializer_class = serializers.ListingSerializer
http_method_names = ['get']
permission_classes = [permissions.IsAuthenticated]
search_fields = ["sector__sector", "assigned_user__username"]
def get_queryset(self):
filters = {'processed': False, 'unprocessable': False, 'available': True}
to_buy = self.request.query_params.get('to_buy', None)
if to_buy is not None:
filters['to_buy'] = to_buy
queryset = Listing.objects.exclude(sector_id=None).order_by('sector_id', '-date').prefetch_related(
'sector', 'assigned_user').only(
'processed', 'unprocessable', 'available', 'to_buy', 'listing_id', 'url', 'address', 'date', 'agent',
'sector', 'in_user_queue', 'process_probability', 'portal', 'assigned_user'
).filter(**filters)
return queryset
How can I go about reducing this wait time so that the data is returned in a more timely manor to the front end.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
