title: How does cursor and fetch work in PostgreSQL
body: <p>I want to know how <code>CURSOR</code> and <code>FETCH</code> work internally in <code>PostgreSQL</code>.</p>
  
  <p>At the first, I assumed that </p>
  
  <ol>
  <li><p>When <code>CURSOR</code> is declared with a <code>select</code> statement, DB will execute the <code>select</code> statement and then have the result stored in DB memory.</p></li>
  <li><p>When <code>FETCH</code> is called on the <code>CURSOR</code>, DB will just read the result moving on the <code>CURSOR</code>.</p></li>
  <li><p>When the <code>CURSOR</code> is closed, results stored is removed from memory.</p></li>
  </ol>
  
  <p>If my assumption is correct, <code>FETCH</code> should have short response time regardless of how the <code>select</code> statement is complex.</p>
  
  <p>However, When I test, <code>FETCH</code> show more poor response time than I expected like It has done something I haven't expected.</p>
  
  <p>How do they work?</p>
  
  <p>--------- EDIT ---------</p>
  
  <p>The below is what I get when I test with my actual database table.
  (<code>select</code> statement contains <code>join</code> clause for 3 tables and one of the tables has 3 million rows)</p>
  
  <pre><code>(   8sec) DECLARE “123" NO SCROLL CURSOR WITH HOLD FOR SELECT .....
  (0.04sec) FETCH FORWARD 2 FROM "123";
  (   4sec) FETCH FORWARD 10000 FROM "123";
  </code></pre>
  
  <p>--------- EDIT ---------</p>
  
  <p>The 4sec response time in <code>FETCH FORWARD 10000 FROM "123"</code> seems because of <code>pgcli</code>(PostgreSQL client tool) I used.</p>
  
  <p>I don't know why but It has clearly came to be fast up to 0.04sec after changing client tool.</p>