title: PostgreSQL: Retain Sort Order / Temporary Index / Paging
body: <p>I'm using PostgreSQL and I intend to paging. The target table contains 1M+ rows. In principle, this is straight forward</p>
  
  <pre><code>SELECT * FROM myTable ORDER BY orderCol LIMIT &lt;pageSize&gt; OFFSET &lt;offset&gt;;
  </code></pre>
  
  <p>Now, this is fast when the orderCol is indexed, but an order of magnitude slower when orderCol has no index. Obviously, the dbms is forced to perform a full table scan in the worst case and has to sort the data for each page requested.</p>
  
  <p>[<strong>Edit:</strong> More specifically, <code>orderCol</code> might change, i.e., is determined at runtime.]</p>
  
  <p>[<strong>Edit2:</strong> The general assumption that indexing <code>orderCol</code> improves sorting performance seems to be wrong. If I add an index to <code>orderCol</code>, query time increases about 70%.]</p>
  
  <p>One obvious solution would be to create a temporary table as necessary with an appropriate index and fill the table with the appropriate data (…I think). But that duplicates all the data.</p>
  
  <p>Is there a way how one can "retain" a sort order between requests? Or create a temporary index?</p>
  
  <p>Many thanks for your answers in advance.</p>