Django admin list page takes forever to load after overriding get_queryset method

Question

I have this model admin -

class NewsAdmin(ImageWidgetAdmin):
    image_fields = ['featured_image']
    list_per_page = 20
    list_display = ('heading', 'category', 'status', 'is_active', 'created_at', 'published_at',
                    'created_by', 'published_by')
    list_editable = ('category', 'status', 'is_active')
    list_filter = ('published_at', 'created_at', 'status', 'is_active', 'created_by',
                   'published_by',)
    search_fields = ('heading', 'category', 'tags', 'source')
    actions = [enable_object, disable_object, status_draft, status_private, status_public]
    actions_on_bottom = True

It only takes max 400ms to load. Here's the django-debug-toolbar image -

djdt image without get_queryset

But when I override the get_queryset method for language filtered objects -

    def get_queryset(self, request):
        queryset = super(NewsAdmin, self).get_queryset(request)
        return queryset.filter(language=request.LANGUAGE_CODE)

It takes around 17-18 seconds which is nuts!! Here's the django-debug-toolbar image -

djdt image with get_queryset

Even same this is happening for the front end queries as well! For details - I have database table with around 400k records and here's the model -

class News(BaseEntityBasicAbstract, HitCountMixin):
    NEWS_STATUS = (
        ('draft', _('Draft')),
        ('pending', _('Pending')),
        ('review', _('Review')),
        ('public', _('Public')),
        ('private', _('Private'))
    )
    backup = models.BooleanField(default=False)
    prev_id = models.BigIntegerField(null=True, blank=True)
    language = models.CharField(max_length=10, choices=LANGUAGES, default='bn')
    heading = models.CharField(max_length=255, null=True, blank=True,
                               verbose_name=_('News Heading'),
                               help_text=_('Provide a news heading/caption.'))
    sub_caption = models.TextField(max_length=255, null=True, blank=True,
                                   verbose_name=_('Summary'),
                                   help_text=_('Provide summary of the news.'))
    url = models.CharField(max_length=255, unique=True, verbose_name=_('URL/Slug/Link'),
                           help_text=_('Unique url for the news without whitspace.'))
    content = HTMLField(null=True, blank=True, verbose_name=_('Content'),
                        help_text=_('HTML content with texts, links & images.'))
    featured_image = models.FileField(upload_to=FilePrefix('news/'), null=True, blank=True,
                                      verbose_name=_('Featured Image'),
                                      help_text=_('Upload a featured image for news.'))
    image_caption = models.TextField(max_length=255, null=True, blank=True,
                                     verbose_name=_('Image Caption'),
                                     help_text=_('Provide a image caption.'))
    status = models.CharField(max_length=20, choices=NEWS_STATUS, default='pending',
                              verbose_name=_('News Status'),
                              help_text=_('Only public news can be seen on front end.'))
    source = models.ForeignKey(NewsSource, on_delete=models.SET_NULL, null=True, blank=True,
                               verbose_name=_('News Source'),
                               help_text=_('Select a news source.'))
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=True,
                                 verbose_name=_('Category'),
                                 help_text=_('Select a news category.'))
    tags = tagulous.models.TagField(
        blank=True,
        to=Tags,
        verbose_name=_('News Tags'),
        help_text=_('Provide news tags separated with commas.')
    )
    published_at = models.DateTimeField(null=True, blank=True,
                                        verbose_name=_('Published At'))
    menu_items = GenericRelation(MenuItems, object_id_field='id',
                                 related_query_name='news_as_menu')
    hit_count_generic = GenericRelation(HitCount, object_id_field='object_pk',
                                        related_query_name='news_hit_count')
    created_by = models.ForeignKey(User, related_name='news_created_by',
                                   on_delete=models.SET_NULL, null=True, blank=True,
                                   verbose_name=_('Created By'))
    updated_by = models.ForeignKey(User, related_name='news_updated_by',
                                   on_delete=models.SET_NULL, null=True, blank=True,
                                   verbose_name=_('Last Updated By'))
    published_by = models.ForeignKey(User, related_name='news_published_by',
                                     on_delete=models.SET_NULL, null=True, blank=True,
                                     verbose_name=_('Published By'))
    deleted_by = models.ForeignKey(User, related_name='news_deleted_by',
                                   on_delete=models.SET_NULL, null=True, blank=True,
                                   verbose_name=_('Deleted By'))

I'm lost why is this happening! Please help me figure out the problem here!

score 2 · Accepted Answer · answered Jun 02 '20 at 06:40

2

You are filtering on a field without an index. The bigger the table, the longer it takes as the database needs to scan each row. Alter your field definition to allow for an index and take care of migrations.

language = models.CharField(max_length=10, choices=LANGUAGES, default='bn' db_index=True)

answered Jun 02 '20 at 06:40

OMG brother @Melvyn, you've just saved me from so much hassle. Thank you so much for finding the problem. I want to hold you a bit more, please bear the pain! Should I also add db_index for foreign_key fields and MayToMayFields? because I'm facing performance problem with fk/m2m fields lookup as well. Also order by date is so much slower than the order by id. Thanks in advance! – Mahmud Abdur Rahman Jun 02 '20 at 06:51
Foreign keys (and by extension m2m) are indexed by default. But if you do a lookup like `News.objects.filter(news__source__name='The Virge')`, then NewsSource.name needs db_index. Ordering and filtering suffer from the same problem: full row scan in the absence of an index, so for those, also add db_index. – Jun 02 '20 at 07:05
Thanks a lot for the clearings. I very much appreciate your kind knowledge & response. Please look into this problem also - https://stackoverflow.com/questions/62110378/django-template-query-for-loop-render-taking-too-much-time/62121961 – Mahmud Abdur Rahman Jun 02 '20 at 07:12

Django admin list page takes forever to load after overriding get_queryset method

1 Answers1