title: Slow Select Distinct with persistence
body: <p>using 2.13.0., we are trying to generalize the use of ignite persistence but somehow we are doing something wrong. When the number of objects in the cache increases linearly the result of a &quot;SELECT DISTINCT&quot; using indexes increases exponentially. I prepared a small test to reproduce the problem <a href="https://github.com/hostettler/ignite-slow-query-persistence" rel="nofollow noreferrer">https://github.com/hostettler/ignite-slow-query-persistence</a>.</p>
  <p>Without persistence:</p>
  <ul>
  <li>655,360 objects in the caches =&gt; select distinct : 472s ---</li>
  <li>x5 :  3,276,750 objects in the caches =&gt; select distinct : 2,141ms</li>
  <li>x10 : 6,553,500 objects in the caches =&gt; select distinct : 4,354ms ---</li>
  <li>x20 : Cannot run because I did not give enough off heap (objective is to test persistence)</li>
  </ul>
  <p>With Persistence</p>
  <ul>
  <li>655,360 objects in the caches =&gt; select distinct : 561 ms</li>
  <li>x5 :  3,276,750 objects in the caches =&gt; select distinct : 3,056ms</li>
  <li>x10 : 6,553,500 objects in the caches =&gt; select distinct : 6,381ms</li>
  <li>x20 : 13,107,000 object =&gt; select distinct : 284,617 ms</li>
  </ul>
  <p>0.5s for a select distinct on an index on 655k records is not fast at any rate anyway but the degradation is exponential between x10 and x20. The query is using the index as described by the explain plan.</p>
  <pre><code>SELECT DISTINCT
      __Z0.DATAGROUPID AS __C0_0
  FROM &quot;cache&quot;.CONTRACTDTOSIMPLE __Z0
      /* &quot;cache&quot;.CONTRACTDTOSIMPLE_DATAGROUPID_IDX */
  </code></pre>
  <p>Already tried to tune all the persistence parameters but it seems the indexes are used.</p>
  <p>The difference between x10 and x20 is around x50 slower. So relying on the persistence has a crazy impact on performance. Any idea of what I am missing?</p>
  <p>Thanks a lot in advance</p>
  <p>P.S: I tried the calcite engine and the results are even worse.</p>
  <p>----- EDIT -----</p>
  <p>I did more test on an Azure  D8ds v5 (same repository), Let me put the results here:</p>
  <p>offheap is set to 4GB, on heap is set to 4GB</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Volume</th>
  <th>Persistence enabled</th>
  <th>WAL MODE</th>
  <th>Throttling enabled</th>
  <th>Page Replacement</th>
  <th>Insert time</th>
  <th>SQL Query</th>
  <th>Partitioned SQL</th>
  <th>Partitioned Scan Query</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6.5M</td>
  <td>false</td>
  <td>N/A</td>
  <td>N/A</td>
  <td>N/A</td>
  <td>96.5s</td>
  <td>3.4s</td>
  <td>2.6s</td>
  <td>0.7s</td>
  </tr>
  <tr>
  <td>6.5M</td>
  <td>true</td>
  <td>NONE</td>
  <td>false</td>
  <td>CLOCK</td>
  <td>192s</td>
  <td>5.6s</td>
  <td>3.9s</td>
  <td>1.1s</td>
  </tr>
  <tr>
  <td>13M</td>
  <td>true</td>
  <td>NONE</td>
  <td>false</td>
  <td>CLOCK</td>
  <td>1063s</td>
  <td>197s</td>
  <td>188s</td>
  <td>94s</td>
  </tr>
  <tr>
  <td>13M</td>
  <td>true</td>
  <td>NONE</td>
  <td>true</td>
  <td>CLOCK</td>
  <td>1047s</td>
  <td>196s</td>
  <td>102s</td>
  <td>14s</td>
  </tr>
  <tr>
  <td>13M</td>
  <td>true</td>
  <td>NONE</td>
  <td>true</td>
  <td>SEGMENTED_LRU</td>
  <td>1042s</td>
  <td>211s</td>
  <td>205s</td>
  <td>14s</td>
  </tr>
  <tr>
  <td>13M</td>
  <td>true</td>
  <td>LOG_ONLY</td>
  <td>false</td>
  <td>CLOCK</td>
  <td>1575s</td>
  <td>225s</td>
  <td>188s</td>
  <td>14s</td>
  </tr>
  <tr>
  <td>13M</td>
  <td>true</td>
  <td>LOG_ONLY</td>
  <td>true</td>
  <td>CLOCK</td>
  <td>1586s</td>
  <td>185s</td>
  <td>102s</td>
  <td>15s</td>
  </tr>
  <tr>
  <td>13M</td>
  <td>true</td>
  <td>LOG_ONLY</td>
  <td>true</td>
  <td>SEGMENTED_LRU</td>
  <td>1550s</td>
  <td>259s</td>
  <td>150s</td>
  <td>15s</td>
  </tr>
  </tbody>
  </table>
  </div>