title: Play 2.2 pagination with very large databases
body: <p>I am writing a Play 2.2.1 application to display the content of very large postgres tables (at least <em>O</em>(10^8)). To do this I modified the the <a href="https://github.com/playframework/playframework/tree/master/samples/java/computer-database" rel="nofollow">computer database sample database</a>.</p>
  
  <p>My page method is almost directly taken from the computer class in the example:</p>
  
  <pre><code>public static Page&lt;Lemma&gt; page(int page, int pageSize, String sortBy, String order, String filter) {
      if(filter.isEmpty()) {
              return 
                  find.where()
                      .findPagingList(pageSize)
                      .setFetchAhead(true)
                      .getPage(page);
      } else {
          return 
              find.where()
                  .ilike("name", "%" + filter + "%")
                  .orderBy(sortBy + " " + order)
                  .findPagingList(pageSize)
                  .setFetchAhead(true)
                  .getPage(page);
      }
  } 
  </code></pre>
  
  <p>If the filter is empty, for performance issues I avoid any ordering. I my toy DB with 20,000,000 entries it works well, but with a more realistic example of 200,000,000 documents I always got timed-out connections.    </p>
  
  <p>The "name" column is indexed, and querying directly the database is very fast. I always run with the "-Xmx4096m" option for JVM.</p>
  
  <p>What I am missing? What I am doing wrong? Any suggestion?</p>
  
  <p><strong>Update</strong></p>
  
  <p>The bottleneck seems to be the query <code>SELECT COUNT(*) FROM lemma t0 ...</code>, that is sent to display the number of documents found. Any idea to improve performance? Any suggestion for implementing a cursor in ebean as suggested by Peter?</p>