Significant Slowdown in Django Query Performance with Large Datasets
I'm migrating some code and I'm trying to debug I'm experiencing a significant slowdown in my Django application when querying large datasets using Django ORM with PostgreSQL... Specifically, when I'm trying to filter and annotate a queryset on a model with over 1 million records, the query takes several seconds to execute, even with indexing applied. Here's an example of the code that I'm using: ```python from myapp.models import MyModel from django.db.models import Count # Trying to count related objects for filtering results = MyModel.objects.filter(active=True).annotate(num_related=Count('related_model')).filter(num_related__gt=5) ``` I've already indexed the `active` field and the foreign key in `related_model`, but the performance is still not acceptable. Running the equivalent SQL query directly in pgAdmin returns results in under a second, which makes me suspect that the overhead is on the ORM side. Additionally, I've tried using `select_related` and `prefetch_related` to optimize the query: ```python results = (MyModel.objects.filter(active=True) .select_related('related_model') .annotate(num_related=Count('related_model')) .filter(num_related__gt=5)) ``` However, the performance hasn't improved significantly. I also checked the query plan using `EXPLAIN ANALYZE` and noticed that the database uses sequential scans instead of index scans, which seems inefficient for this size of data. What strategies can I use to improve the performance of these queries in Django? Are there specific best practices I might be missing, or do I need to consider raw SQL for this particular case? Any advice would be greatly appreciated! This is part of a larger microservice I'm building. Is this even possible? The stack includes Python and several other technologies.