title: Why does not postgresql start returning rows immediately?
body: <p>The following query returns data right away:</p>
  
  <pre><code>SELECT time, value from data order by time limit 100;
  </code></pre>
  
  <p>Without the limit clause, it takes a long time before the server starts returning rows:</p>
  
  <pre><code>SELECT time, value from data order by time;
  </code></pre>
  
  <p>I observe this both by using the query tool (<code>psql</code>) and when querying using an API.</p>
  
  <p>Questions/issues:</p>
  
  <ul>
  <li>The amount of work the server has to do before starting to return rows should be the same for both select statements. Correct?</li>
  <li>If so, why is there a delay in case 2?</li>
  <li>Is there some fundamental RDBMS issue that I do not understand?</li>
  <li>Is there a way I can make postgresql start returning result rows to the client without pause, also for case 2?</li>
  <li><strong>EDIT (see below)</strong>. It looks like <code>setFetchSize</code> is the key to solving this. In my case I execute the query from python, using SQLAlchemy. <strong>How can I set that option for a single query (executed by <code>session.execute</code>)</strong>? I use the psycopg2 driver.</li>
  </ul>
  
  <p>The column <code>time</code> is the primary key, BTW.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I believe this excerpt from the <a href="http://jdbc.postgresql.org/documentation/83/query.html#fetchsize-example" rel="nofollow noreferrer">JDBC driver documentation</a> describes the problem and hints at a solution (I still need help - see the last bullet list item above):</p>
  
  <blockquote>
    <p>By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.</p>
  </blockquote>
  
  <p>and</p>
  
  <blockquote>
    <p>Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour).</p>
  </blockquote>
  
  <pre><code>// make sure autocommit is off
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  
  // Turn use of the cursor on.
  st.setFetchSize(50);
  </code></pre>