title: Django Filtering Query slow on Large row dataset
body: <ul>
  <li>I have been facing a slow performance for my site on performing filtering over large rows from the following data of field. Type of data I'm storing in the field is <code>RAW HTTP Response Body</code> in the <code>body</code> field which has large content in it, I have 32k objects for below <code>Project</code> models.</li>
  </ul>
  
  <pre><code>class Project(models.Model):
      body = models.TextField(null=True,blank=True)
  </code></pre>
  
  <ul>
  <li>Performing filtering over <code>body</code> takes few seconds.</li>
  </ul>
  
  <pre><code>&gt;&gt;&gt; Project.objects.filter(body__icontains='x').count() ### 5-6 seconds
  20472
  </code></pre>
  
  <hr>
  
  <blockquote>
    <p>I use Django paginator for slicing the data for each page.</p>
  </blockquote>
  
  <pre><code>&gt;&gt;&gt; from django.core.paginator import Paginator
  &gt;&gt;&gt; data = Project.objects.filter(body__icontains='x')
  &gt;&gt;&gt; p = Paginator(data,10)
  &gt;&gt;&gt; p.page(1) ### takes 5-6 seconds here again because this function counts the total number of pages based on data.count()
  &lt;Page 1 of 2048&gt;
  </code></pre>
  
  <ul>
  <li>And then i pass the paginator data to the template which just loop over and shows the body.</li>
  </ul>
  
  <pre><code>{% for each_data in data %}
      {{ each_data.body }}
  {% endfor %}
  </code></pre>
  
  <ul>
  <li>For every page of paginator it would take 5-6 seconds wait because paginator function performs the count on the total dataset and return the page range. which is the biggest drawback.</li>
  </ul>
  
  <hr>
  
  <blockquote>
    <p>Using Normal Python Slicing :</p>
  </blockquote>
  
  <pre><code>&gt;&gt;&gt; data = Project.objects.filter(body__icontains='x')
  &gt;&gt;&gt; for each_data in data[0:10]:  ### 0.1 seconds
          print(each_data.body)
  </code></pre>
  
  <ul>
  <li>As you can see if we don't hit the database at all and just slice the data and pass it over the template then it would just show the data immediately.</li>
  </ul>
  
  <hr>
  
  <ul>
  <li>I Believe query becomes slow when <code>Paginator</code> function counts the total number of pages for our filtered results for each page.</li>
  <li>I Believe paginator is not suitable for large dataset.</li>
  </ul>
  
  <p>drawbacks of using slicing function are:</p>
  
  <ul>
  <li>We can't count the total number of results objects returned by the filter.</li>
  <li>can't see the number of pages paginator should have as we are not counting the total dataset.</li>
  </ul>
  
  <blockquote>
    <p>Is there any way to tackle this problem for faster results along with pagination?</p>
  </blockquote>