title: Django slow query performance improve
body: <p>I'm using Django with Postgres and have the following tables:</p>
  
  <pre><code>class Person(models.Model):
      person_id=models.IntegerField(primary_key=True)
      name = models.CharField(max_length=500, blank=True)
      description = models.ManyToManyField('descriptions.Description', through='DescriptionPersonUser')
  
  class DescriptionPersonUser(models.Model):
      person = models.ForeignKey(Person)
      description = models.ForeignKey('descriptions.Description')
      user = models.ForeignKey(User)
  
      class Meta:
          managed = True
          unique_together = ('person', 'description', 'user')
  
  class Description(models.Model):
      description_id=models.AutoField (primary_key=True)
      description_word=models.CharField(max_length=50, blank=True, unique=True)
  
  class AuthUser(models.Model):
      id = models.IntegerField(primary_key=True)  # AutoField?
     ...
      username = models.CharField(unique=True, max_length=30)
  </code></pre>
  
  <p>The Person table has more than 1.5 mio rows and other tables have no more than 100 rows each. As I understand this should still be reasonable to perform queries that perform "normally". I want to order the Person table by count annotating count from the DescriptionPersonUser table. </p>
  
  <pre><code>person_list = Person.objects.annotate(count=Count('descriptionpersonuser')).order_by('-count')[:10]
  </code></pre>
  
  <p>this query took cca 50000 ms to load. Than I tried to execute it in raw sql and improved a lot to cca 1900 ms. </p>
  
  <pre><code>person_list= Person.objects.raw('SELECT "person"."person_id", COUNT("persons_descriptionpersonuser"."id") AS "count" FROM "person" LEFT OUTER JOIN "persons_descriptionpersonuser" ON ( "person"."person_id" = "persons_descriptionspersonuser"."person_id" ) GROUP BY "person"."person_id" ORDER BY "count" DESC, "person"."person_id" ASC LIMIT 10'),
  </code></pre>
  
  <p>I also created the index on persons_descriptionpersonuser:</p>
  
  <pre><code>CREATE INDEX index_descriptionpersonuser ON persons_descriptionpersonuser (person_id, description_id, id);
  </code></pre>
  
  <p>So my questions are: </p>
  
  <ol>
  <li>Is there still margin to speed up the query? Or is 1900 ms for 1+ mio rows query decent?</li>
  <li>Since I don't see any difference in query speed with the created index, how can I check if the index is working or if is influencing the query?</li>
  </ol>
  
  <p>edited (as per <a href="https://stackoverflow.com/users/5493302/tomasz-jakub-rup">Tomasz Jakub Rup</a> suggestion adding EXPLAIN ANALYZE results):</p>
  
  <p>without index_descriptionpersonuser:</p>
  
  <pre><code>Limit  (cost=138185.30..138185.33 rows=10 width=8) (actual time=2470.974..2470.976 rows=10 loops=1)
     -&gt;  Sort  (cost=138185.30..142177.82 rows=1597006 width=8) (actual time=2470.973..2470.975 rows=10 loops=1)
           Sort Key: (count(persons_descriptionpersonuser.id)), person.person_id
           Sort Method: top-N heapsort  Memory: 25kB
           -&gt;  GroupAggregate  (cost=0.56..103674.58 rows=1597006 width=8) (actual time=0.402..1945.107 rows=1597006 loops=1)
                 Group Key: person.person_id
                 -&gt;  Merge Left Join  (cost=0.56..79719.49 rows=1597006 width=8) (actual time=0.378..1014.179 rows=1597016 loops=1)
                       Merge Cond: (person.person_id = persons_descriptionpersonuse.person_id)
                       -&gt;  Index Only Scan using person_pkey on person  (cost=0.43..75718.86 rows=1597006 width=4) (actual time=0.359..610.272 rows=1597006 loops=1)
                             Heap Fetches: 235898
                       -&gt;  Index Scan using persons_descriptionpersonuse_person_id on persons_descriptionpersonuser  (cost=0.14..12.42 rows=19 width=8) (actual time=0.014..0.025 rows=20 loops=1)
   Planning time: 17.879 ms
   Execution time: 2472.821 ms
  (13 rows) 
  </code></pre>
  
  <p>with index_descriptionpersonuser:</p>
  
  <pre><code>Limit  (cost=138185.55..138185.58 rows=10 width=8) (actual time=2341.349..2341.352 rows=10 loops=1)
     -&gt;  Sort  (cost=138185.55..142178.07 rows=1597006 width=8) (actual time=2341.325..2341.325 rows=10 loops=1)
           Sort Key: (count(persons_descriptionpersonuser.id)), person.person_id
           Sort Method: top-N heapsort  Memory: 25kB
           -&gt;  GroupAggregate  (cost=0.56..103674.83 rows=1597006 width=8) (actual time=0.106..1819.330 rows=1597006 loops=1)
                 Group Key: person.person_id
                 -&gt;  Merge Left Join  (cost=0.56..79719.74 rows=1597006 width=8) (actual time=0.092..877.874 rows=1597016 loops=1)
                       Merge Cond: (person.person_id = persons_descriptionpersonuser.person_id)
                       -&gt;  Index Only Scan using person_pkey on person  (cost=0.43..75718.86 rows=1597006 width=4) (actual time=0.023..473.046 rows=1597006 loops=1)
                             Heap Fetches: 235898
                       -&gt;  Index Only Scan using index_descriptionpersonuser on persons_descriptionpersonuser  (cost=0.14..12.44 rows=20 width=8) (actual time=0.059..0.085 rows=20 loops=1)
                             Heap Fetches: 20
   Planning time: 0.715 ms
   Execution time: 2343.815 ms
  (14 rows)
  </code></pre>
  
  <p>as <a href="https://stackoverflow.com/users/5493302/tomasz-jakub-rup">Tomasz Jakub Rup</a> suggested the optimised sql query now takes cca 40 ms. Here are the results:</p>
  
  <pre><code>Limit  (cost=1.50..1.52 rows=8 width=8) (actual time=0.061..0.064 rows=10 loops=1)
     -&gt;  Sort  (cost=1.50..1.52 rows=8 width=8) (actual time=0.060..0.061 rows=10 loops=1)
           Sort Key: (count(id)), person_id
           Sort Method: quicksort  Memory: 25kB
           -&gt;  HashAggregate  (cost=1.30..1.38 rows=8 width=8) (actual time=0.039..0.044 rows=10 loops=1)
                 Group Key: person_id
                 -&gt;  Seq Scan on persons_descriptionpersonuser  (cost=0.00..1.20 rows=20 width=8) (actual time=0.011..0.018 rows=20 loops=1)
   Planning time: 0.175 ms
   Execution time: 0.129 ms
  (9 rows)
  </code></pre>
  
  <p>Thanks</p>