Efficient time series queries

Background

For more information about the data model for time series data, see Design a time series table. Data is organized by time series. In a time series table, all columns marked as TAG (tag columns) are indexed. These columns specify the time series to which each data point belongs and indicate the data source.

The following figure shows an example of data that belongs to the same time series in a time series table. For more information, see Design a time series table.

• If the value in a tag column changes, the data belongs to a different time series. Typically, a time series identifies a specific data source that generates time series data.

• LindormTSDB creates an inverted index for each tag column. The index uses the column name and value as a key to index all time series that contain that key-value pair. This allows the system to quickly locate the set of time series that match a specific tag. As data is written, the inverted index for the time series table evolves, as shown in the following figure.

• For the timestamp column, LindormTSDB automatically creates a Block Range Index (BRIN) to quickly locate the required data range within large volumes of time series data. However, LindormTSDB does not create indexes on field columns.

Recommendations

Based on the indexing mechanism of LindormTSDB, consider the following points when querying a time series table:

• Include equality filters on tag columns and a time range filter in your queries. Avoid queries that filter only on field columns.

• If a query includes filters on multiple tag columns and the time series sets they map to have an inclusion relationship, retain only the most selective tag filter. For example, in the sample data, the id column is more selective than the city column.

• A query that covers a wide time range may scan a large amount of data, which degrades query performance. To improve performance, narrow the time range in your filter.

Example queries

SELECT id, so2 FROM aqm WHERE district='yuhang' AND time >= '2019-04-18 10:00:00' AND time < '2019-04-18 10:30:00';

+---------+------+
|   id    | so2  |
+---------+------+
| HY00003 |   10 |
| HY00003 | 10.3 |
| HY00003 | 10.3 |
| HY00003 | 10.1 |
| HY00003 | 10.1 |
| HY00003 | 10.1 |
| HY00003 | 10.1 |
| HY00003 | 10.1 |
| HY00003 | 10.3 |
| HY00003 | 10.2 |
| HY00001 |   10 |
| HY00001 | 10.5 |
| HY00001 |   10 |
| HY00001 | 10.3 |
+---------+------+
14 rows in set (444 ms)

SELECT DISTINCT(id) FROM aqm;

lindorm> SELECT DISTINCT(id) FROM aqm;
+----------+
|          |
+----------+
| HY00001  |
| HY00002  |
| HY00003  |
| HY00004  |
+----------+
4 rows in set (61 ms)

SELECT id, time, avg(pm2_5) AS avg_pm2_5, avg(so2) AS avg_so2 FROM aqm WHERE district='yuhang' AND time >= '2019-04-18 10:00:00' AND time < '2019-04-18 10:30:00' SAMPLE BY 5m;

+----------+-----------------------------+------------+---------+
|    id    |            time             | avg_pm2_5  | avg_so2 |
+----------+-----------------------------+------------+---------+
| HY00003  | 2019-04-18T10:00:00+08:00   |      31.26 |   10.16 |
| HY00003  | 2019-04-18T10:05:00+08:00   |      31.24 |   10.16 |
| HY00001  | 2019-04-18T10:00:00+08:00   |     31.175 |    10.2 |
+----------+-----------------------------+------------+---------+
3 rows in set (170 ms)

SELECT max(avg_pm2_5) AS max_avg_pm25, max(avg_so2) AS max_avg_so2 FROM (SELECT district, id, time, avg(pm2_5) AS avg_pm2_5, avg(so2) AS avg_so2 FROM aqm WHERE district='yuhang' AND time >= '2019-04-18 10:00:00' AND time < '2019-04-18 10:30:00' SAMPLE BY 5m) GROUP BY district;

+--------------+------------+
| max_avg_pm25 | max_avg_so2 |
+--------------+------------+
|        31.26 |       10.2 |
+--------------+------------+
1 rows in set (103 ms)