Question 236922 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Optimising Django .count() function

Is there a way to optimise the .count() function on a queryset in Django? I have a large table, which I have indexed, however the count function is still incredibly slow.

I am using the Django Rest Framework Datatables package, which uses the count function, so I have no way of avoiding using the function – I was hoping there would be a way to utilise the index for the count or something along those lines?

Thanks for any help anyone can offer!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    For anyone struggling with this issue in the future, it is a known issue with Postgres when counting a large amount of rows. We can instead estimate the number of rows (which seems to be highly accurate and infinitely quicker). Explanation of how to do this in Django is here.

    https://stackoverflow.com/a/64013651/12206099


  2. 0

    You can make simpler version by using something like:

    class CustomCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = models.PositiveIntegerField()

    Usage:

    some_subquery = SomeModel.objects.filter(
    foregin_key=OuterRef("id")
).values("id")

qs.annotate(some_field_count=CustomCount(some_subquery))

    Since this makes the simplest count query then I think it should be faster then default one (need to verify this)

    If this still doesn’t help then issue is somewhere else

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search