title: Postgres query performance using row_number()
body: <p>Lets say there is a table customers, it has following columns:</p>
  
  <ul>
  <li><p>customer_id as primary key</p></li>
  <li><p>creation_date</p></li>
  <li><p>with few other columns </p></li>
  </ul>
  
  <p>I want to query all the entries from customers table with creation_date >= to_date('2000/01/01', 'YYYY/MM/DD') and insert into another table clients. 
  The number of entries in table customers around 10 million and both the tables are on the same database. <em>I want to minimize the time for copying this data.</em></p>
  
  <p>There are two approaches:</p>
  
  <ol>
  <li><p>Run following query in parallel for each n, with n = 1, 10001, 20001... so on</p>
  
  <pre><code>insert into clients 
  values ( 
    select * 
    from ( 
      select *, row_number() over (ORDER BY customer_id) as rn 
      from ( 
        select * 
        from customers 
        where creation_date &gt;= to_date('2000/01/01', 'YYYY/MM/DD')
      ) as sub1
    ) as sub2  
    where rn&gt;=n limit 10000
  );
  </code></pre></li>
  <li><p>Run single query</p>
  
  <pre><code>insert into clients 
  values ( 
    select * 
    from customers 
    where creation_date &gt;= to_date('2000/01/01', 'YYYY/MM/DD')
  );
  </code></pre></li>
  </ol>
  
  <p></p>
  
  <p>For 1, following is the execution plan</p>
  
  <pre>
  
      Insert on clients  (cost=0.42..113.55 rows=10000 width=4506)
         ->  Subquery Scan on "*SELECT*"  (cost=0.42..113.55 rows=10000 width=4506)
               ->  Limit  (cost=0.42..112.45 rows=10000 width=616)
                     ->  Subquery Scan on sub2  (cost=0.42..57773.19 rows=1000000 width=616)
                           Filter: (sub2.rn >= 0)
                           ->  WindowAgg  (cost=0.42..57579.79 rows=1000000 width=624)
                                 ->  Index Scan using “customer_id_pkey" on customers (cost=0.42..57347.71 rows=1000000 width=616)
                                       Filter: ((creation_date >= to_date('2000/01/01'::text, 'YYYY/MM/DD'::text)) 
  
  </pre>
  
  <p>For all n execution in parallel, <em>Subquery Scan on sub2</em> will run on the entire data. Hence doing it in parallel has defeated the purpose.  </p>
  
  <p>Is my understanding correct or will approach 1 will take less time than approach 2. </p>
  
  <p>Also Please suggest if there is a way to improve the query performance.</p>
  
  <p><em>[Values of plan node has been modified, so please ignore rows, cost and width values]</em></p>