title: How to optimize pagination on a SELECT DISTINCT sql?
body: <p>My query is a select distinct on a very large database, and in pgAdmin sql tool the query itself lasts 12 sec.</p>
  
  <pre><code>SELECT DISTINCT ON (city, airport, zip, country, name) city, airport, price, id FROM mytable;
  </code></pre>
  
  <p>The spring-batch reader definition:</p>
  
  <pre><code>JpaPagingItemReader&lt;MyEntity&gt; reader;
  reader.setPageSize(PAGE_SIZE);
  </code></pre>
  
  <p>If I define the <code>PAGE_SIZE</code> to be as large as the database columns, the performance is equal to 12 sec.
  But if I set the size to a lower value (eg pagesize = 100.000 on a 1.000.000 datarows db), the performance is very bad (~ 10x as long).</p>
  
  <p>Spring-batch applies specific pagination on the queries in the background. What is does:</p>
  
  <pre><code>query.setFirstResult();
  query.setMaxResult();
  </code></pre>
  
  <p>if page size is 10, den the queries executed are as follows:</p>
  
  <pre><code>    firstResult, maxResult
      0, 10
     10, 10
     20, 10
     30, 10...
  </code></pre>
  
  <p>This again translates to <code>LIMIT</code> and <code>OFFSET</code> in SQL.</p>
  
  <p>Question: is a <code>SELECT DISTINCT ON</code> not combinable with pagination LIMIT/OFFSET? To me it seems as if the full select distinct query is executed again on each "pagination" run, and thus lasts very long.</p>
  
  <p>So, if the database must anyhow make a full distinct select before applying the maxresults, can I temporary save the "distinct" select and just fetch the next page?</p>
  
  <p>How can I improve this, without having to set the pagination size to 1million. Or is improvement not possible here?</p>