2

I'm paginating a list view for a model with many fields, so it takes a lot of time to execute MyModel.objects.filter('some filter').count(), because on SQL level it runs

SELECT COUNT(*) FROM mytable

instead of:

SELECT COUNT(id) FROM mytable

even if I write explicitly

MyModel.objects.only('id').count()

How can I make Django run COUNT(id) on .count()?

Update:

I'm using PostgreSQL.

cansadadeserfeliz
  • 3,033
  • 5
  • 34
  • 50
  • are you sure that it will be faster? – gavriel Sep 06 '16 at 14:52
  • 2
    It's unlikely that there's any real performance difference between `count(*)` and `count(id)`. Don't bother changing that, you won't gain anything. `count` is simply slow on postgresql due to MVCC model. However you can quickly estimate the number of rows: https://wiki.postgresql.org/wiki/Count_estimate Or you can keep track of numbers of rows in a separate table/cache. – freakish Sep 06 '16 at 15:05
  • i guess you won't find much difference in terms of speed but obviously something new to learn . – Pavneet_Singh Sep 06 '16 at 15:06
  • Are you sure it is slow because of `*` vs `id`, not because of a missing index on the field you are filtering? – Daniel Hepper Sep 06 '16 at 15:07
  • @freakish You are right, looks like I have to use endless pagination or some other trick to avoid executing `count()` – cansadadeserfeliz Sep 06 '16 at 15:43

3 Answers3

2

Try using:

MyModel.objects.filter('some filter').values_list("id).count()

This will do this query:

select count(id) from MyModel
helvete
  • 2,455
  • 13
  • 33
  • 37
Aadit Shah
  • 46
  • 3
1

Try using an aggregate query:

from django.db import models
MyObject.objects.all().aggregate(models.Count('id'))['id__count']
Exelian
  • 5,749
  • 1
  • 30
  • 49
0

COUNT(id) is roughly the equivalent of:

MyModel.objects.exclude(id=None).count()

this will add extra step to count the table fields, which is not the case for COUNT(*)

ahmed
  • 5,430
  • 1
  • 20
  • 36