Lazy Materialization
Download
Mode fokus
Ukuran font
Introduction to Lazy Materialization
Late Materialization is a scan optimization technique used by TDSQL Boundless read-only analysis instances during SQL query execution: when a query cannot hit the primary key or an index and requires a full table scan (
TableScan), the instance does not read all target columns at once. Instead, it first reads the predicate columns and completes the filtering, then goes back to read the truly required non-predicate columns based on the filtering results.Late Materialization reduces unnecessary I/O and CPU overhead by postponing the reading and decompression of non-predicate columns, thereby significantly lowering the scan cost for analytical queries in columnar storage scenarios.
Note:
Late Materialization is only effective in TDSQL Boundless read-only analysis instances. This optimization is not triggered by row-based execution.
Advantages of Lazy Materialization
Reduce decompression overhead. Columnar data is stored in a compressed format by column, and materialization requires decompressing the relevant data. Late Materialization limits the decompression scope to predicate columns and the small number of rows that match after filtering, significantly reducing CPU decompression overhead.
Reduce I/O. Early Materialization first reads and combines all column data, but some of these columns may not appear in the final result. Late Materialization reads only the columns that are truly needed, reducing unnecessary I/O.
Better align with columnar execution. Operators such as filtering and aggregation directly act on columnar data, enabling more efficient utilization of the batch processing and vectorization capabilities of columnar storage, thereby further improving query execution speed.
How It Works
Late Materialization introduces the
Column Read operator during the physical plan phase, splitting the originally single TableScan into two stages:1. Early stage: Only predicate columns are read. The embedded
Selection within the TableScan performs the filtering and outputs the set of row numbers for the matched rows.2. Later stage: Based on the matched row numbers, the
Column Read operator goes back to read and decompress only the non-predicate columns that have not been read yet.In addition to full table scan scenarios, TDSQL Boundless also attempts Late Materialization in query patterns where
TopN is adjacent to TableScan: it first scans and locates the result rows based on the TopN sorting key, then materializes only the column data required for the final output.Parameter Description
The master switch for Late Materialization is controlled by the system variable
libra_enable_late_materialization. When enabled, it is set to ON.Required | Description |
Parameter Type | BOOL |
Default Value | ON |
Value Range | ON: Enable Late Materialization. OFF: Disable Late Materialization. |
Scope | Global Session |
SET_VAR Supported or Not | Supported |
-- Disable Late Materialization at the Session level.SET libra_enable_late_materialization = OFF;-- Enable Late Materialization at the Session level.SET libra_enable_late_materialization = ON;
In addition to the master switch, TDSQL Boundless also provides the following tuning parameters to precisely control the triggering conditions and behavior of Late Materialization.
Parameter | Type | Default Value | Description |
libra_minimum_rows_to_enable_late_materialization | INT | 8192 | Late materialization is not enabled when the estimated number of rows of a table is below this threshold. |
libra_minimum_selectivity_to_enable_late_materialization | FLOAT | 0.9 | A predicate is pushed down to TableScan for filtering only when its selectivity is lower than this threshold. |
libra_late_materialization_size_threshold | FLOAT | 0.5 | The optimization is abandoned if the ratio of the data volume read after late materialization to the original data volume exceeds this threshold. |
libra_enable_late_materialization_trace | BOOL | OFF | After it is enabled, it outputs the reasons for accepting or rejecting late materialization in logs, facilitating tuning and troubleshooting. |
The scope of the aforementioned parameters is set to Global and Session, and they support the
SET_VAR Hint.Use Limits
Late Materialization is only effective for queries executed on columnar read-only analysis instances. It is not triggered by row-based execution.
When the system variable
tiflash_fastscan is set to ON, Late Materialization and Fast Scan are mutually exclusive. In this case, the instance automatically disables Late Materialization and outputs a warning. You can view the warning by using SHOW WARNINGS.Late Materialization does not take effect when the estimated number of rows of a table is below the
libra_minimum_rows_to_enable_late_materialization threshold (default 8192).When the selectivity of a predicate is higher than the
libra_minimum_selectivity_to_enable_late_materialization threshold (default 0.9), the corresponding predicate is not adopted as a filter condition for Late Materialization.Viewing the Execution Plan
Execute the
EXPLAIN statement to observe whether Late Materialization is effective. When Late Materialization is effective, the Column Read operator (displayed as COLUMN READ in brief mode) appears in the execution plan, indicating that the materialization of some columns is deferred until this operator.
To obtain more detailed Late Materialization decision information (such as which predicates are adopted, which are rejected, and the reasons), enable the trace switch:
SET libra_enable_late_materialization_trace = ON;
After it is enabled, the relevant decision information is output to the logs of the columnar read-only analysis instance.
Controlling Lazy Materialization via Hints
libra_enable_late_materialization supports the SET_VAR Hint, which allows you to enable or disable Late Materialization within a single SQL statement without affecting other sessions or statements. An example is as follows:-- Enable Late Materialization within a single SQL statement.SELECT /*+ SET_VAR(libra_enable_late_materialization=ON) */ *FROM t WHERE c1 = 1 AND c2 = 1;-- Disable Late Materialization within a single SQL statement.SELECT /*+ SET_VAR(libra_enable_late_materialization=OFF) */ *FROM t WHERE c1 = 1 AND c2 = 1;
Note:
The value in the
SET_VAR Hint supports both the ON/OFF and 1/0 notations, which are equivalent.
Bantuan dan Dukungan
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan