TDengine TSDB Query Engine and SQL Optimization Guide

In IoT and IIoT scenarios, a time-series database handles the core tasks of storing and querying large volumes of sensor data in real time. TDengine combines high write throughput with a query engine architecture with a rich set of SQL extensions designed for time-series workloads. This guide explains how the TDengine query engine works, how to configure query policies, and how to use its SQL extensions effectively.

1. Query engine architecture

1.1 Core component responsibilities

The query engine uses a distributed architecture with the following core components:

• taosc (client library): Handles SQL parsing, execution plan generation, and optimization. taosc performs lexical and syntactic analysis on SQL statements, builds an abstract syntax tree (AST), and constructs an optimal execution plan based on metadata.
• vnode (Virtual Node): Serves as both a data storage and compute unit. It manages the physical storage of time-series data and handles local data scanning, filtering, and preliminary aggregation.
• qnode (Query Node): A node dedicated to complex query computation. It handles aggregation, data merging, result sorting, and other compute-intensive tasks.

1.2 Query execution flow

The execution flow of a query statement follows these steps:

1. SQL parsing: taosc parses the SQL statement, identifying the query type and the tables involved.
2. Metadata retrieval: Cluster topology and table distribution information is obtained from the mnode.
3. Execution plan generation: An optimal execution plan is generated based on a cost model.
4. Task distribution: Sub-query tasks are distributed to the relevant vnodes or qnodes.
5. Parallel execution: Each node executes its local computation tasks in parallel.
6. Result aggregation: The qnode aggregates results from all nodes and performs final computation.
7. Result return: The query result is returned to the client.

2. Query policy configuration (queryPolicy)

The queryPolicy parameter controls where query tasks are executed.

2.1 Policy 1: vnode only (default)

<em>-- Configuration</em>
SET QUERY_POLICY 1;

This is the default query policy. All query computation is performed locally on vnodes, with the qnode serving only as a coordinator.

Suitable for: deployments with balanced compute and storage resources, queries dominated by simple filtering and small-volume aggregation, and environments with limited network bandwidth.

Advantage: Data is computed locally, reducing network transfer overhead. Disadvantage: Vnodes experience higher compute pressure; complex queries may affect write performance.

2.2 Policy 2: Hybrid vnode/qnode

<em>-- Configuration</em>
SET QUERY_POLICY 2;

The system intelligently selects the execution location based on query complexity. Simple queries run locally on vnodes; complex queries use qnode compute resources. This is the recommended choice for most production environments.

2.3 Policy 3: Storage-compute separation

<em>-- Configuration</em>
SET QUERY_POLICY 3;

All computation except data scanning is offloaded to qnodes. Vnodes are only responsible for reading raw data and transmitting it to qnodes.

Advantage: Vnodes focus on writes, isolating query and write resources. Disadvantage: Data must cross the network, imposing higher bandwidth requirements.

2.4 Policy selection comparison

Policy	Suitable for	Resource requirements	Performance characteristics
Policy 1	Edge computing, small deployments	Combined compute and storage	Low latency, fast simple queries
Policy 2	General production environments	Balanced configuration	Adaptive, strong overall performance
Policy 3	Large-scale analytical workloads	Sufficient compute resources	Best write performance

3. SQL extensions

3.1 Custom dimension grouping (PARTITION BY)

<em>-- Group by device tag and hourly window</em>
SELECT
    _irowts,
    AVG(temperature) as avg_temp,
    MAX(humidity) as max_humidity
FROM sensor_data
PARTITION BY device_id, INTERVAL(1h)
WHERE ts >= '2024-01-01 00:00:00';

3.2 Limiting group count (SLIMIT/SOFFSET)

<em>-- Return statistics for only the first 10 devices</em>
SELECT
    device_id,
    AVG(temperature)
FROM sensor_data
PARTITION BY device_id
SLIMIT 10;

<em>-- Skip the first 20 devices, return the next 10</em>
SELECT
    device_id,
    AVG(temperature)
FROM sensor_data
PARTITION BY device_id
SLIMIT 10 SOFFSET 20;

3.3 Tag query optimization

Tags and time-series data are stored separately.

<em>-- Efficient: queries only tags, does not scan data files</em>
SELECT device_id, location, status
FROM sensor_data;

<em>-- Efficient: query with tag filtering</em>
SELECT * FROM sensor_data
WHERE location = 'Beijing' AND status = 'active';

3.4 Window query types

Time window (INTERVAL)

<em>-- Aggregate by 1-hour time window</em>
SELECT
    _irowts,
    AVG(temperature),
    COUNT(*)
FROM sensor_data
INTERVAL(1h);

State window (STATE_WINDOW)

<em>-- Split windows by state changes</em>
SELECT
    device_id,
    AVG(temperature),
    MAX(pressure)
FROM sensor_data
PARTITION BY device_id
STATE_WINDOW(machine_status);

Session window (SESSION)

<em>-- Session window: intervals over 10 minutes start a new session</em>
SELECT
    device_id,
    SUM(power_consumption)
FROM sensor_data
PARTITION BY device_id
SESSION(ts, 10m);

Event window (EVENT_WINDOW)

<em>-- Define windows based on start and end events</em>
SELECT
    device_id,
    AVG(temperature)
FROM sensor_data
PARTITION BY device_id
EVENT_WINDOW START WITH start_event_condition END WITH end_event_condition;

Count window (COUNT_WINDOW)

<em>-- Every 100 records form a window</em>
SELECT
    device_id,
    AVG(temperature)
FROM sensor_data
PARTITION BY device_id
COUNT_WINDOW(100);

3.5 Time-series join queries

ASOF Join

ASOF Join is a feature for correlating different time-series datasets. For each row in the left table, it finds the row in the right table with the closest timestamp that does not exceed the left row’s timestamp.

<em>-- Correlate sensor data with device status table</em>
SELECT
    a.ts,
    a.device_id,
    a.temperature,
    b.status
FROM sensor_data a
ASOF JOIN device_status b
ON a.device_id = b.device_id;

Window Join

<em>-- Correlate data from two sensors within a 5-second window</em>
SELECT
    a.ts,
    a.device_id,
    a.temperature,
    b.pressure
FROM temp_sensor a
WINDOW JOIN pressure_sensor b
ON a.device_id = b.device_id
AND a.ts BETWEEN b.ts - 5s AND b.ts + 5s;

4. Supertable and multi-table aggregation

4.1 Supertable concept

A supertable logically groups subtables that share the same data structure, supporting separate storage of tags and time-series data.

<em>-- Create supertable</em>
CREATE STABLE sensor_data (
    ts TIMESTAMP,
    temperature FLOAT,
    humidity FLOAT
) TAGS (
    device_id BINARY(32),
    location BINARY(64),
    device_type BINARY(16)
);

<em>-- Create subtable</em>
CREATE TABLE sensor_001 USING sensor_data
TAGS ('DEV001', 'Beijing', 'TypeA');

4.2 Multi-table aggregation advantages

<em>-- Aggregate statistics for all sensors grouped by location</em>
SELECT
    location,
    AVG(temperature),
    MAX(humidity),
    COUNT(*)
FROM sensor_data
PARTITION BY location, INTERVAL(1h);

Architectural advantages of separate tag and time-series data storage:

• Tag data stays in memory, making filtering highly efficient.
• Time-series data is partitioned by time, keeping scan ranges under control.
• Subtable data is physically isolated, so queries do not interfere with each other.

5. Query caching

TDengine uses a multi-level caching system to accelerate queries.

5.1 Metadata cache

• Table structure information, tag values, and other metadata are cached in memory.
• Reduces mnode query pressure and speeds up SQL parsing.

5.2 Time-series data cache

• Recently written hot data is kept in the vnode memory cache.
• Frequently queried data blocks are cached, reducing disk I/O.

5.3 Latest data cache

• The latest row of each table is cached separately.
• Provides millisecond-level response for queries like LAST.

<em>-- Fast query using the latest data cache</em>
SELECT LAST(temperature) FROM sensor_data;

6. Query performance recommendations

1. Use tag filtering effectively: Prioritize tag conditions in the WHERE clause to quickly locate subtables using tag indexes.
2. Limit the time range: Specify an explicit time range whenever possible to reduce the amount of data scanned.
3. Choose appropriate window granularity: Avoid windows that are too small, which can produce an excessive number of result rows.
4. Use SLIMIT to control concurrency: Limit the number of subtables when querying supertables.
5. Select queryPolicy based on workload: Use Policy 3 for analytical workloads.

Summary

The TDengine query engine provides strong support for real-time analysis of massive time-series data through its distributed architecture, flexible query policies, and rich SQL extensions. The vnode/qnode collaborative architecture, combined with PARTITION BY, window queries, ASOF Join, and other SQL extensions, and supported by a multi-level caching system, delivers efficient query performance across a range of deployment patterns from edge to cloud.