Support for Pruning Subquery Projection Columns

Availability

This feature is available since MogDB 5.0.6.

Introduction

Supports column pruning to eliminate redundant projection columns in subqueries and improve SQL performance.

Benefits

Identifies and removes unnecessary columns in the subquery results to improve query efficiency, reduce the overhead of query execution, and enhance performance.

Description

Column Pruning is a database optimization technique that refers to the process where the database query execution engine identifies and removes unnecessary columns during query execution. These unnecessary columns are typically redundant in the query result set and are identified and eliminated in the query plan to reduce the overhead of query execution and improve performance.

Eliminating redundant projection columns in subqueries means that the database system will identify and remove columns that are not needed in the subquery results to improve query efficiency. This helps to reduce the computational resources and memory consumption required for the query, thereby accelerating the execution of the query.

When queries involve complex query plans and large amounts of data, the database system can execute queries more efficiently by identifying and eliminating unnecessary columns, reducing resource consumption, and improving the system's response speed.

Parameter Description

The GUC parameter rewrite_rule has a new option column_pruner to control whether to enable the function of eliminating redundant projection columns in subqueries, which is turned off by default. Set rewrite_rule to column_pruner to enable this feature.

SET rewrite_rule='column_pruner';

Constraints

1. SQL statements that do not meet the rewrite rules will not throw errors and will execute normally without being processed by the rewrite rules.
2. Columns referenced by external queries will still be retained.
3. Columns involved in order/group/distinct/filter/SW clauses in the query will not be eliminated.
4. Only supports rewriting optimization for CTEs that are INLINE subqueries.
5. Supports rewriting rules for views and subqueries.
6. Columns in the query that include returning-set or volatile functions will not be eliminated.
7. This feature supports A/PG compatibility modes and does not support B mode temporarily.

Example

CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
SET rewrite_rule='column_pruner';

explain verbose SELECT a
FROM (
    SELECT t1.a
        ,count(t2.a)
        ,DENSE_RANK() OVER (
            ORDER BY t1.a
            ) AS RANK
    FROM t1
    LEFT JOIN t2 ON t1.a = t2.a
    GROUP BY t1.a
    );
    
                            QUERY PLAN
-------------------------------------------------------------------
 HashAggregate  (cost=36.86..38.86 rows=200 width=4)
   Output: t1.a
   Group By Key: t1.a
   ->  Seq Scan on public.t1  (cost=0.00..31.49 rows=2149 width=4)
         Output: t1.a
(5 rows)

Performance Test Results

1. Column pruning performance shows a significant improvement in scenarios with join elimination or multiple window functions, with the highest improvement of up to 90% in the multi-agg&windowfunc scenario on columnar tables, and up to 69% on row-oriented tables.
2. The performance improvement effect in the Sort scenario is not obvious and may not improve within the margin of error.
3. The performance improvement effect of the column pruning feature on columnar tables is generally better than that on row-oriented tables.
4. Parallel query execution can further optimize the performance improvement effect, but the performance improvement in the multi-agg&windowfunc scenario of columnar tables remains 90% before and after enabling.
5. In the test of the row-oriented table join elimination scenario, it is concluded that the work_mem parameter has no significant impact on the query time of the column pruning feature.
6. The customer scenario is a typical scenario for the column pruning feature, with a significant performance improvement effect, reaching 94%, and further improving to 98% when query_dop=8.

Related Pages

rewrite_rule