A virtual table is a logical table structure designed by TDengine for industrial IoT scenarios. It does not store actual data; instead, it references data from multiple physical source tables through a column reference mechanism. A typical use case is a smart factory temperature monitoring system, where a state table (state_table, roughly 50 rows) and an aggregation table (agg_table, roughly 5 million rows) use a virtual table to achieve efficient joined queries.
Aggregation Pushdown Optimization
A simple approach aggregates data from all source tables at once, which causes severe performance problems at large data volumes. Aggregation pushdown analyzes the dependency relationships of aggregation functions in a query and pushes the aggregation operations down so each source table executes them independently. For example, a query that would otherwise need to process 5 million plus 50 rows can be optimized to process 50 rows and 5 million rows separately, merging only the aggregated results at the end. An additional benefit is restored SMA (Small Materialized Aggregates) pre-aggregation acceleration.
Two-Phase Window Query Splitting
The first phase, WindowSplit, scans all source tables to determine global window boundaries and generates a window partitioning plan. The second phase, ColsMerge, executes window aggregations on each source table according to the boundaries determined in the first phase, then merges the results. Performance comparison: without optimization, 5 million plus 50 rows are scanned with high memory usage and slow execution; with aggregation pushdown, only aggregated result sets are scanned with low memory usage and fast execution (5x to 10x improvement); with two-phase splitting, window boundaries plus aggregation results are scanned, making it well suited for window queries.
Best Practices
Plan source table associations carefully to avoid large disparities in data volume. Create SMA indexes on frequently queried source tables. Specify a time range for window queries. For monitoring and diagnostics: check whether aggregation pushdown optimization is triggered, and monitor the DynQueryCtrl operator execution time.
Summary
Through the two core strategies of aggregation pushdown and two-phase splitting, TDengine effectively addresses the performance bottlenecks of cross-table data association. It delivers performance and reliability in smart manufacturing, energy management, smart city construction, and similar scenarios.
