Query acceleration

Background and value

As the data volume of analytical queries continues to grow, single-node execution can easily become a bottleneck in the following aspects:

• Reading columns that the query does not need

• Scanning data blocks that are ultimately filtered out

• Repeatedly reading and decoding the same data pages

• Single-node caches that cannot cover hot data

• High CPU and memory bandwidth requirements for aggregation, joins, and sorting on large tables

The columnar execution acceleration capability of fully columnstore tables aims to apply the following principles throughout the execution pipeline: read only the required columns, process only the necessary data, and reuse decoded results wherever possible. This reduces query latency and improves throughput.

In the execution pipeline, these optimizations are mainly reflected in the following aspects:

• Data is read only for the columns required by the query, which reduces unnecessary column scans.

• Pushable predicates are pushed down to the underlying storage wherever possible, so that stripe-level or data block pruning is performed in advance.

• Files are quickly filtered by using visibility views, which reduces the number of irrelevant rows that enter the subsequent aggregation, sorting, and expression computation stages.

• The OSS page cache and the metadata cache are reused, which reduces the cost of repeated reads and repeated decoding.

• Data is delivered to the execution layer in batches to take advantage of vectorized batch processing.

Relationship with multi-node parallel execution and how to determine the execution plan

This topic focuses on single-node execution acceleration. For information about the multi-node massively parallel processing capabilities, applicable scenarios, and partition design of columnstore tables, see Multi-node analysis.

To determine whether a specific SQL statement ultimately uses an MPP plan, check whether the Exchange operator exists in the execution plan.

EXPLAIN SELECT /*+ SET_VAR(imci_plan_use_mpp=forced) */ COUNT(*) FROM nation;

If the Exchange operator appears in the execution plan, the SQL statement can use the multi-node parallel execution capability of columnstore tables. If the Exchange operator appears in the actual execution plan, the SQL statement ultimately runs as an MPP plan. Conversely, if no Exchange operator is present in the execution plan, the SQL statement runs as a single-node plan.

Performance test results

The following are the single-node warm-cache test results of fully columnstore tables on an instance with 32 cores and 256 GB of memory, based on TPC-H 1 100 GB: