# Limit and Offset restrictions

**URL:** <https://forum.opensearch.org/t/limit-and-offset-restrictions/3373>\
**Category:** SQL\
**Created:** [July 23, 2020, 12:26pm UTC](https://forum.opensearch.org/t/limit-and-offset-restrictions/3373 "2020-07-23T12:26:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![pvmagacho](https://avatars.discourse-cdn.com/v4/letter/p/74df32/32.png) [@pvmagacho](https://forum.opensearch.org/u/pvmagacho)\
**Post date:** [July 23, 2020, 12:26pm UTC](https://forum.opensearch.org/t/limit-and-offset-restrictions/3373/1 "2020-07-23T12:26:28Z")

</div>

Hello,  
how can I get more than 10000 in SQL queries?  
When use something like this “LIMIT 10000, 2000” I get an error.  
I am not requesting more than 10000 records at once, but I can’t seem to paginate.  
Thank you

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [July 23, 2020, 8:13pm UTC](https://forum.opensearch.org/t/limit-and-offset-restrictions/3373/2 "2020-07-23T20:13:15Z")

</div>

Thanks for reporting the issue. I tried the query from my side. Basically it reached the `index.max_result_window` limit though the size of resulting record is small.

ES doc explains: “The maximum value of `from + size` for searches to this index. Defaults to `10000` . Search requests take heap memory and time proportional to `from + size` and this limits that memory.”

So I think our LIMIT clause can only work within this window. Alternatively please check out our pagination doc: [Endpoint - Open Distro Documentation](https://opendistro.github.io/for-elasticsearch-docs/docs/sql/endpoints/#cursor). Thanks!

---

<div class="post-metadata">

**Author:** ![pvmagacho](https://avatars.discourse-cdn.com/v4/letter/p/74df32/32.png) [@pvmagacho](https://forum.opensearch.org/u/pvmagacho)\
**Post date:** [July 23, 2020, 8:56pm UTC](https://forum.opensearch.org/t/limit-and-offset-restrictions/3373/3 "2020-07-23T20:56:10Z")

</div>

Hello, thank you for your response. I am using the JDBC driver inside DBeaver. Isn’t that incorporated into the driver or it depends on the application itself?  
Thank you.

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [July 23, 2020, 9:47pm UTC](https://forum.opensearch.org/t/limit-and-offset-restrictions/3373/4 "2020-07-23T21:47:44Z")

</div>

Yes, it is. I think it’s because our pagination feature is disabled by default for now. So you need to enabled it by changing your ES cluster setting: [sql/settings.rst at main · opendistro-for-elasticsearch/sql · GitHub](https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-cursor-enabled)

And then you can change page size (“fetch\_size”) in your JDBC driver by passing fetchSize parameter in connection url or by setFetchSize() method. Please see more details in our README: [sql/sql-jdbc at main · opendistro-for-elasticsearch/sql · GitHub](https://github.com/opendistro-for-elasticsearch/sql/tree/master/sql-jdbc)