title: Columnstore index - slow performance on offset-fetch query
body: <p>We have Fact table around 35M rows on Azure database (premium tier), this table has cluster columnstore index enabled in order to boost query performance.</p>
  
  <p>We did pagination (to index on Elastic Search) on Fact table using similar below code:</p>
  
  <pre><code>SELECT *
  FROM [SPENDBY].[FactInvoiceDetail]
  ORder by id
  offset 1000000 rows fetch next 1000 rows only
  </code></pre>
  
  <p>But this query performs so slow, even over 10 minutes, it's not finished. If we change to use <code>TOP</code>, it works really well and take around 30 seconds:</p>
  
  <pre><code>SELECT TOP 1000 * 
  FROM [SPENDBY].[FactInvoiceDetail]
  WHERE ID &gt; 1000000 
  ORDER BY Id
  </code></pre>
  
  <p>The estimated execution plan for offset-fetch query:</p>
  
  <p><a href="https://i.stack.imgur.com/hgpbz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hgpbz.png" alt="enter image description here"></a></p>
  
  <p>I am not sure that I understand whether <code>offset-fetch</code> query performs very poorly on cluster columnstore index or not.</p>
  
  <p>This table also have a lot of none-cluster B-tree indexes on foreign keys and one unique index on the <code>Id</code> of Fact table in order to boost performance</p>
  
  <p>This execution plan for offset-fetch query:</p>
  
  <p><a href="https://pastebin.com/BM8MXQMg" rel="nofollow noreferrer">https://pastebin.com/BM8MXQMg</a></p>