LIMIT in Riak TS
The LIMIT statement is used with
SELECT to return a limited number of results.
This document shows how to run various queries using
LIMIT. See the guidelines for more information on limitations and rules for queries in Riak TS.
LIMIT uses on-disk query buffer to prevent overload, which adds some overhead and increases the query latency.
You may adjust various parameters in riak.conf depending on how much memory your riak nodes will have, including
max_concurrent_queries. It is also worth noting that
max_returned_data_size is calculated differently for LIMIT statements; you can read more about that here. All of these settings impact the maximum size of data you can retrieve at one time, and it is important to understand your environmental limitations or you run the risk of an out-of-memory condition.
However, the most effective means of speeding up your
LIMIT queries is to place the query buffer directory (
timeseries_query_buffers_root_path) on fast storage or in memory-backed /tmp directory.
The LIMIT statement returns a limited number of results from a SELECT statement.
LIMIT has the following syntax:
LIMIT «number_rows» [ OFFSET «offset_rows» ]
The OFFSET modifier can be used with
LIMIT to skip a specified number of results and return the remaining results (example below).
Before you run
SELECT you must ensure the node issuing the query has adequate memory to receive the response. If the returning rows do not fit into the memory of the requesting node, the node is likely to fail.
The following table defines a schema for sensor data.
CREATE TABLE SensorData ( id SINT64 NOT NULL, time TIMESTAMP NOT NULL, value DOUBLE, PRIMARY KEY ( (id, QUANTUM(time, 15, 'm')), id, time ) )
Return only five results between the given times:
SELECT id, time, value FROM SensorData WHERE id = 2 AND time > '2016-11-28 06:00:00' AND time < '2016-11-28 06:10:10' LIMIT 5;
Sort and Limit
Sort results between given times by time in ascending order and only return 5 results:
SELECT id, time, value FROM SensorData WHERE id = 2 AND time > '2016-11-28 06:00:00' AND time < '2016-11-28 06:10:10' ORDER BY time ASC LIMIT 5;
Skip the first two results of the query, return five:
SELECT id, time, value FROM SensorData WHERE id = 2 AND time > '2016-11-28 06:00:00' AND time < '2016-11-28 06:10:10' LIMIT 5 OFFSET 2;