![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/01\/arrows2-300x128.png\")
<\/p>\n<\/p>\n
Read the pagination background in my previous article: <\/span>https:\/\/www.couchbase.com\/blog\/optimizing-database-pagination-using-couchbase-n1ql\/<\/span><\/a><\/p>\n Pagination is the task of dividing the potential result into pages and retrieving the required pages, one by one on demand. \u00a0Using OFFSET and LIMIT is the easy way to write pagination into database queries. Together, OFFSET and LIMIT, make the pagination clause of the SELECT statement. Pagination is a common application job and its implementation has a major impact on the customer experience. Let\u2019s look at the issues and solutions with Couchbase N1QL in detail.<\/span><\/p>\n Markus Winand from <\/span>https:\/\/use-the-index-luke.com\/<\/span><\/a> argues it may not be the most efficient. He also suggests, when possible, using keyset pagination approach instead of OFFSET pagination. For this article, I\u2019m going to use the example queries modeled on his article, <\/span>https:\/\/use-the-index-luke.com\/no-offset<\/span><\/a> to show what OFFSET optimizations we\u2019ve done, when and how to exploit the keyset pagination in N1QL. <\/span><\/p>\n Since Couchbase ships with a travel-sample dataset, we\u2019ll use it to write our pagination queries.<\/span><\/p>\n For this query, using the index ixtopic, the query engine executes in a simple way. The query engine gets all the qualified keys from the index, then gets all the documents, sorts based on the ORDER BY clause and then drops the OFFSET number of documents (in this case zero) and projects LIMIT (in this case 10) number of documents.<\/span><\/p>\n Here\u2019s the plan showing the index and the spans.<\/span><\/p>\n <\/p>\n N1QL chooses the index ixtype and pushes the filter (type = \u201chotel\u201d) to the index scan. To implement the ORDER BY clause, it fetches all the documents. In the sort phase, we recognize LIMIT 10 clause and make the sort efficient by keeping only the top 10 items.<\/span><\/p>\n Let\u2019s look at the index scan operator efficiency with this index:<\/span><\/p>\n There were 917 documentkeys returned by the indexer. The query engine fetched 917 documents. Then the sort (order) operator sorted them and returned the 10 items.<\/span><\/p>\n 2. GET THE SECOND PAGE<\/span><\/p>\n In this case, everything is same as the query1 except:<\/span><\/p>\n As the OFFSET increases, the number of documents scanned by the sort will increase as well, consuming more memory and CPU.<\/span><\/p>\n It uses the right index and creates the right filters (spans) for the index scan.Explain includes the following.<\/span><\/p>\n Let\u2019s look at the index scan operator efficiency with this index:<\/span><\/p>\n <\/p>\n Only the required 10 document keys are returned from the index scan! \u00a0The N1QL optimizer evaluates both the WHERE clause and the pagination (OFFSET, LIMIT) clause. The query optimizer decides to pushdown the pagination clause to the indexer based on the order by clause of the query and the index key order. \u00a0<\/span><\/p>\n In this case, query predicate is: type = \u2018hotel\u2019<\/span><\/p>\n Order by clause is: ORDER BY country, city<\/span><\/p>\n Index key order is: (type, country, city)<\/span><\/p>\n Just by simple comparison, order by clause is not exactly the same as index key order. However, the leading key of the index (type) has an equality predicate (type = \u2018hotel\u2019). Therefore, the optimizer knows the projected document keys will be in the sorted order of (country and city).<\/span><\/p>\n **and the ORDER BY clause to see if it can pushdown the pagination parameters to the index scan. \u00a0In this case, there is a perfect match.<\/span><\/p>\n The optimizer pushes both OFFSET and LIMIT in pagination to index scan and the index scan returns only the 10 document keys required no matter the OFFSET! Therefore, fetch only needs to fetch the 10 documents. Indexer does have to go through the index scan to evaluate the keys.<\/span><\/p>\n This query executed in about 6.81 milliseconds in my environment. Let\u2019s paginate to subsequent pages to see the performance:<\/span><\/p>\n If you want to paginate in the descending order, change the query and index to the following. The collation of the key in the index should match the collation in the query. In this case, both country and city are in DESCending order.<\/span><\/p>\n <\/p>\n Let\u2019s now look at a query that\u2019s not covered by the index. The query we saw earlier, we decide to retrieve the name of the hotel along with country and city. The name isn\u2019t in the index.<\/span><\/p>\n <\/p>\n In this case, again, the index has the data to cover the WHERE clause and the ORDER BY clause. The index scan will apply the predicate. The index order matches query order (note: there is only equality predicate on leading key. So, country and city are in order automatically). Index skips of the OFFSET number of qualified keys and returns the number of keys specified by the LIMIT clause. The engine retrieves the documents in order to maintain the order specified by ORDER BY clause.<\/span><\/p>\n All these little optimizations together ensure the query performance is consistent whether OFFSET is 10, 100 or 1000. The only overhead between higher OFFSET is the index scan skipping over the additional items. Index key evaluation and skipping is significantly faster than getting all the documents and sorting.<\/span><\/p>\n Let\u2019s consider a longer running query with a potential of returning up to 24K documents.<\/span><\/p>\n create index idxtypeaiaid ON `travel-sample`(type, airline, airlineid) <\/span><\/p>\n Even when the pagination query predicates and order by is completely covered by the index, there is a limited number of cases where the pagination LIMIT and OFFSET can be pushed to the index scan in order to make this very efficient.<\/span><\/p>\n Here are general rules and requirements for pushing down OFFSET and LIMIT to the indexer. \u00a0Note these rules are not exhaustive and complete but give you a sense of when this optimization is done.<\/span><\/p>\n Case (a) qualifies, and (b) does not.<\/span><\/p>\n The case (2) is a perfect match of the order.<\/span><\/p>\n The case (3) doesn\u2019t match perfectly, but the leading key has an equality filter. So, we know the results from the scan will be in the order of (b, c). \u00a0Hence the pagination pushdown to the index is possible.<\/span><\/p>\n For the case (4), the leading key has a range predicate (a > 3) and therefore, t pagination pushdown is not possible.<\/span><\/p>\n For the case (5), there is a perfect match of the ORDER BY clause to the index keys. Therefore, we can push down the pagination to the indexer, even if there is range predicate on the leading key.<\/span><\/p>\n For the case (f), we can exploit the index order, but cannot push down the OFFSET and LIMIT because the complete predicate cannot be evaluated by the index scan. Since we get the results in the required order, after the OFFSET and LIMIT number of documents are projected, we terminate the index scan.<\/span><\/p>\n For simplicity, I\u2019ve used one leading key. The statements are generalized and applicable to multiple leading keys.<\/span><\/p>\n Obviously, this optimizer logic is complex! See additional information in the appendix on the requirements.<\/span><\/p>\n OFFSET overhead:<\/b><\/p>\n The initial travel-sample has about 900 documents with (type = \u201chotel\u201d). For experimentation, I simply increase the amount of data by inserting the same data again few times using the following query:<\/span><\/p>\n Now, let\u2019s try this query:<\/span><\/p>\n As the OFFSET increases, the amount of time spent by the indexer to traverse through the index items increases. This affects both latency and throughput. \u00a0<\/span><\/p>\n Can we improve this? Keyset pagination comes to the rescue. All the rules we mentioned for pagination optimization apply here as well. In addition, one of the predicates has to be UNIQUE so that we can clearly navigate from one page to another without duplicate documents appearing in multiple pages. With Couchbase, we\u2019re in luck. Each document in a bucket has a unique document key. It\u2019s referenced by META().id in the N1QL query.<\/span><\/p>\n The basic insight is, customers typically want the NEXT page instead of some random page from the start of the resultset. So, when you fetch each page in order, remember the last set (HIGHEST\/LOWEST depending on the ORDER BY clause). Then, use that information to set the predicates and position the index for the next scan. This will avoid the wasted key processing during OFFSET.<\/span><\/p>\n Let\u2019s look at an example:<\/span><\/p>\n CREATE INDEX ixtypectcy ON `travel-sample`<\/span>\u00a0(type, country, city, META().id);<\/span><\/p>\n Results:<\/p>\n This query ran in 5.14 milliseconds.<\/span><\/p>\n How did we construct this query?<\/span><\/p>\n In this case, it is: (country >= \u201cFrance\u201d AND city >= \u201cAvignon\u201d).<\/p>\n Now, to ensure unique value, we add the META().id predicate.<\/span><\/p>\n (META().id > “038c8a13-e1e7-4848-80ec-8819ff923602”)<\/span><\/p>\n This executes in 5 milliseconds with the following plan. As you go through the subsequent pages, simply repeat the steps to construct the next query and avoid the OFFSET. You can continue to go forever with similar response time!<\/span><\/p>\n SUMMARY:<\/strong> <\/span><\/p>\n You just learned how pagination works. You also learned how the Couchbase N1QL optimizer exploits the index to improve the efficiency of query pagination performance. \u00a0You can use keyset pagination techniques to improve the performance of OFFSET further using the technique written.<\/span><\/b><\/p>\n References:<\/b><\/p>\n Appendix: <\/b>Optimize the query with order, offset, limit to exploit index ordering<\/span><\/p>\n Query ORDER BY evaluation can exploit the Index order with the indexer returning results in the required order and the query does not have to modify the order (e.g. does grouping,join). Whenever the order of record changes we need to use sort data to satisfy the ORDER BY clause.<\/span><\/p>\n <\/p>\n\n
SELECT *\r\nFROM `travel-sample`\r\nWHERE type = \u2018hotel\u2019\r\nORDER BY country, city\r\nOFFSET 0\r\nLIMIT 10;\r\n\r\nCREATE INDEX ixtopic ON `travel-sample`(type);\r\n<\/pre>\n
![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/01\/Screen-Shot-2018-01-09-at-10.34.54-AM-300x169.png\")
<\/p>\n {\r\n \"index\": \"ixtype\",\r\n \"index_id\": \"8630f5f7e05ee113\",\r\n \"index_projection\": {\r\n \"primary_key\": true\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"spans\": [\r\n {\r\n \"exact\": true,\r\n \"range\": [\r\n {\r\n \"high\": \"\\\"hotel\\\"\",\r\n \"inclusion\": 3,\r\n \"low\": \"\\\"hotel\\\"\"\r\n }\r\n\r\n<\/pre>\n {\r\n \"#operator\": \"Order\",\r\n \"limit\": \"10\",\r\n \"sort_terms\": [\r\n {\r\n \"expr\": \"(`travel-sample`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`city`)\"\r\n }\r\n ]\r\n },\r\n<\/pre>\n \"#operator\": \"IndexScan2\",\r\n \"#stats\": {\r\n \"#itemsOut\": 917,\r\n \"#phaseSwitches\": 3671,\r\n \"execTime\": \"2.646892ms\",\r\n \"kernTime\": \"31.095431ms\",\r\n \"servTime\": \"19.781593ms\"\r\n },\r\n\u2026\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 917,\r\n \"#itemsOut\": 917,\r\n \"#phaseSwitches\": 3787,\r\n \"execTime\": \"3.43324ms\",\r\n \"kernTime\": \"20.847541ms\",\r\n \"servTime\": \"69.875698ms\"\r\n },\r\n\u2026\r\n \"#operator\": \"Order\",\r\n \"#stats\": {\r\n \"#itemsIn\": 917,\r\n \"#itemsOut\": 10,\r\n \"#phaseSwitches\": 1849,\r\n \"execTime\": \"6.519061ms\",\r\n \"kernTime\": \"88.307572ms\"\r\n },<\/pre>\nSELECT *\r\nFROM `travel-sample`\r\nWHERE type = \u2018hotel\u2019\r\nORDER BY country, city\r\nOFFSET 10\r\nLIMIT 10;\r\n<\/pre>\n
\n
\"#operator\": \"Order\",\r\n \"#stats\": {\r\n \"#itemsIn\": 917,\r\n \"#itemsOut\": 20,\r\n \"#phaseSwitches\": 1859,\r\n \"execTime\": \"6.485904ms\",\r\n \"kernTime\": \"65.92484ms\"\r\n },\r\n {\r\n \"#operator\": \"Offset\",\r\n \"#stats\": {\r\n \"#itemsOut\": 10,\r\n \"#phaseSwitches\": 32,\r\n \"execTime\": \"5.071503ms\",\r\n \"kernTime\": \"701ns\",\r\n \"state\": \"running\"\r\n },\r\n \"expr\": \"10\",\r\n \"#time_normal\": \"00:00.0050\",\r\n \"#time_absolute\": 0.005071503\r\n },\r\n<\/pre>\n\n
CREATE INDEX ixtypectcy ON `travel-sample`(type, country, city);\r\nLet\u2019s execute our recent query again:\r\nSELECT *\r\nFROM `travel-sample`\r\nWHERE type = \u2018hotel\u2019\r\nORDER BY country, city\r\nOFFSET 10\r\nLIMIT 10;\r\n<\/pre>\n
![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/01\/Screen-Shot-2018-01-09-at-2.38.36-PM-300x179.png\")
<\/p>\n {\r\n \"#operator\": \"IndexScan2\",\r\n \"index\": \"ixtypectcy\",\r\n \"index_id\": \"2a2ed6573354e21\",\r\n \"index_projection\": {\r\n \"primary_key\": true\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"limit\": \"10\",\r\n \"namespace\": \"default\",\r\n \"offset\": \"10\",\r\n \"spans\": [\r\n {\r\n \"exact\": true,\r\n \"range\": [\r\n {\r\n \"high\": \"\\\"hotel\\\"\",\r\n \"inclusion\": 3,\r\n \"low\": \"\\\"hotel\\\"\"\r\n }\r\n ]\r\n }\r\n ],\r\n<\/pre>\n \"#operator\": \"IndexScan2\",\r\n \"#stats\": {\r\n \"#itemsOut\": 10,\r\n \"#phaseSwitches\": 43,\r\n \"execTime\": \"41.786\u00b5s\",\r\n \"kernTime\": \"11.15\u00b5s\",\r\n \"servTime\": \"855.759\u00b5s\"\r\n },\r\n<\/pre>\n\n\n
\n OFFSET<\/span><\/td>\n LIMIT<\/span><\/td>\n Response Time<\/span><\/td>\n<\/tr>\n \n 10<\/span><\/td>\n 10<\/span><\/td>\n 6.81 ms<\/span><\/td>\n<\/tr>\n \n 20<\/span><\/td>\n 10<\/span><\/td>\n 7.17 ms<\/span><\/td>\n<\/tr>\n \n 100<\/span><\/td>\n 10<\/span><\/td>\n 7.02 ms<\/span><\/td>\n<\/tr>\n \n 400<\/span><\/td>\n 10<\/span><\/td>\n 9.54 ms<\/span><\/td>\n<\/tr>\n \n 800<\/span><\/td>\n 10<\/span><\/td>\n 9.08 ms<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n DROP INDEX `travel-sample`.ixtypectcy;\r\n\r\nCREATE INDEX ixtypectcy ON `travel-sample`(type, country DESC, city DESC);\r\n\r\nSELECT country, city\r\nFROM `travel-sample`\r\nWHERE type = \"hotel\"\r\nORDER BY country DESC, city DESC\r\nOFFSET 10\r\nLIMIT 10;<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan2\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"covers\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`type`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`country`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`city`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((meta(`travel-sample`).`id`))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"ixtypectcy\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"limit\": \"10\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"offset\": \"10\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"hotel\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"hotel\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\r\n\r\n<\/pre>\nCREATE INDEX ixtypectcy ON `travel-sample` (type, country DESC, city DESC);\r\n\r\nSELECT country, city, name\r\nFROM `travel-sample`\r\nWHERE type \u00a0= \"hotel\"\r\nORDER BY country desc, city desc\r\nOFFSET 10\r\nLIMIT 10;<\/pre>\n
\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan2\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"ixtypectcy\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"b2ab2c276bba7862\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"limit\": \"10\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"offset\": \"10\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"hotel\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"hotel\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}<\/pre>\n\n
\n
\n
\n
\n
\n
\n
insert into `travel-sample`(key UUID(), value t) select t from `travel-sample` t ;\r\n\r\nselect count(*) from `travel-sample` where type = \u201chotel\u201d;\r\n\r\n{\r\n\r\n\u00a0\u00a0\u00a0\"$1\": 38318\r\n\r\n\u00a0}<\/pre>\nSELECT *\r\nFROM `travel-sample`\r\nWHERE type = \u2018hotel\u2019\r\nORDER BY country, city\r\nOFFSET 38000\r\nLIMIT 10;<\/pre>\n
\n\n
\n OFFSET<\/span><\/td>\n LIMIT<\/span><\/td>\n Response Time<\/span><\/td>\n<\/tr>\n \n 38000<\/span><\/td>\n 10<\/span><\/td>\n 28.97 ms<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n
SELECT country, city, META().id\r\nFROM `travel-sample` use index (ixtypectcy)\r\nWHERE type = \"hotel\"\r\nORDER BY country, city, META().id\r\nOFFSET 0\r\nLIMIT 5;<\/pre>\n
\u00a0{\r\n\u00a0\u00a0\u00a0\"city\": \"Avignon\",\r\n\u00a0\u00a0\u00a0\"country\": \"France\",\r\n\u00a0\u00a0\u00a0\"id\": \"000cb76b-ed85-4f56-9a6e-a6c300902944\"\r\n\u00a0},\r\n\u00a0{\r\n\u00a0\u00a0\u00a0\"city\": \"Avignon\",\r\n\u00a0\u00a0\u00a0\"country\": \"France\",\r\n\u00a0\u00a0\u00a0\"id\": \"0070d2ac-e411-4aeb-a1e7-4a00635d2d5a\"\r\n\u00a0},\r\n\u00a0{\r\n\u00a0\u00a0\u00a0\"city\": \"Avignon\",\r\n\u00a0\u00a0\u00a0\"country\": \"France\",\r\n\u00a0\u00a0\u00a0\"id\": \"01e4c1da-3749-43ce-88e3-37a00d0a376f\"\r\n\u00a0},\r\n\u00a0{\r\n\u00a0\u00a0\u00a0\"city\": \"Avignon\",\r\n\u00a0\u00a0\u00a0\"country\": \"France\",\r\n\u00a0\u00a0\u00a0\"id\": \"0352b5c9-fcbf-48bc-9cf8-f5760cc910a2\"\r\n\u00a0},\r\n\u00a0{\r\n\u00a0\u00a0\u00a0\"city\": \"Avignon\",\r\n\u00a0\u00a0\u00a0\"country\": \"France\",\r\n\u00a0\u00a0\u00a0\"id\": \"038c8a13-e1e7-4848-80ec-8819ff923602\"\r\n\u00a0}<\/pre>\n\n
SELECT country, city, META().id\r\nFROM `travel-sample` use index (ixtypectcy)\r\nWHERE type = \"hotel\"\r\nAND country >= \u201cFrance\u201d\r\nAND city >= \u201cAvignon\u201d\r\nAND META().id > \"038c8a13-e1e7-4848-80ec-8819ff923602\"\r\nORDER BY country, city, META().id\r\nLIMIT 5;<\/pre>\n
\n
\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan2\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"covers\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`type`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`country`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`city`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((meta(`travel-sample`).`id`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((meta(`travel-sample`).`id`))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"ixtypectcy\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"5e43ce0471785942\",\r\n\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"entry_keys\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"limit\": \"5\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"hotel\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"hotel\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"France\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"Avignon\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 0,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"038c8a13-e1e7-4848-80ec-8819ff923602\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],<\/pre>\n\n
\n