title: Django/Postgres: Performance issues with LIMIT querysets
body: <p>I have came across a strange query performance issue that I am struggling to understand.</p>
  
  <p>The following is a simplified version of the model structure I have, hopefully it will be enough to illustrate the issue:</p>
  
  <pre><code>class Note(models.Model):
     ...
     name = models.CharField(max_length=50)
     parentNote = models.ForeignKey('self', null=True)
     form = models.ForeignKey('NoteForm', null=True)
     ...
  
  class Event(Note):
     ...
     startDate = models.DateField()
     ...
  
  class Activity(Event):
     ...
  </code></pre>
  
  <p>The <code>Activity</code> model is the source of the issue I am facing. It has an extensive inheritance heirarchy, none of which is abstract. I do not know if this contributes to the issue. <code>Activity</code> has ~280000 records and, obviously, its parents have at least that many, if not more.</p>
  
  <p>The NoteForm model is not described above - it is only necessary to know that it is external to the <code>Activity</code> model's hierarchy and contains less than 100 records.</p>
  
  <p>I am using Django version 1.3.</p>
  
  <p>The problem occurs when querying for the latest "child" Activity of some parent Activity. The query filters by the <code>parentNote</code> field, orders by the 'startDate' field (descending) and uses Python's index notation to select the first result (which, by my understanding, simply adds <code>LIMIT 1</code> to the generated SQL). See below for the code.</p>
  
  <p>This query runs unexpectedly slowly <em>when no results are found</em> - 10+ seconds. If results are found, it runs as expected - well under 1 second.</p>
  
  <p>Further investigation revealed the following:</p>
  
  <ul>
  <li>It is the limit causing the issue. Just doing the filter, without limiting to the first result, is not slow - whether results are found or not.</li>
  <li>Ordering is partially a culprit. Removing the ordering removes the issue.</li>
  <li>The <code>parentNote</code> filter is partially a culprit. Changing the filter to use the <code>form</code> or <code>name</code> field removes the issue.</li>
  </ul>
  
  <p>In code:</p>
  
  <pre><code># Original - SLOW
  try:
      latest = Activity.objects.filter(
          parentNote=activity.pk
      ).order_by('-startDate')[0]
  except IndexError:
      latest = None
  
  # FAST
  
  # No limit
  Activity.objects.filter(
      parentNote=activity.pk
  ).order_by('-startDate')
  
  # No ordering
  try:
      latest = Activity.objects.filter(
          parentNote=activity.pk
      )[0]
  except IndexError:
      latest = None
  
  # Different filter
  try:
      latest = Activity.objects.filter(
          form=activity.pk
      ).order_by('-startDate')[0]
  except IndexError:
      latest = None
  
  # Different filter
  try:
      latest = Activity.objects.filter(
          name=activity.pk
      ).order_by('-startDate')[0]
  except IndexError:
      latest = None
  </code></pre>
  
  <p>If the issue is at the database level, I can't see it. I've run the "Original" and "No Limit" examples from above in the <code>django-debug-toolbar</code>'s <code>debugsqlshell</code>. The "Original" took 16 seconds and "No Limit" took 59ms. I copied both queries printed by the <code>debugsqlshell</code> and ran them in pgAdmin. "Original" took 1375ms and "No Limit" took 94ms. So it is slower, but not by the amount I'm seeing using the ORM. <code>EXPLAIN ANALYZE</code> definitely shows the query analyzer taking different paths, which I completely understand. But I cannot reproduce the 16 second query using SQL directly.</p>
  
  <p>So, in summary:</p>
  
  <ul>
  <li>I am seeing LIMIT queries running far slower than identical queries without the LIMIT, but only when no results are found.</li>
  <li>Queries that return results do not run slowly - and they are identical apart from the values of the filters.</li>
  <li>It appears to be a function of which fields are included in the filters, and whether or not the queryset is ordered.</li>
  <li>It does NOT appear to be a database level issue as running the SQL directly does not run slowly.</li>
  </ul>
  
  <p><strong>Update:</strong></p>
  
  <p>While trying suggestions made in the comments, the above examples suddenly ceased suffering from this issue - before I found any evidence as to the cause, let alone implemented a fix. I still have no idea what the problem was, but now I do not have a means to reproduce it in order to further investigate.</p>