Row-Store Execution to Vectorized Execution

Availability

This feature is available since MogDB 3.0.0.

Introduction

Row-store table queries are converted into vectorized execution plans for execution, improving the execution performance of complex queries.

Benefits

Effectively improve the query performance of complex queries.

Description

This feature adds a RowToVec operation to scan operators. After the data in the row-store table is converted into the vectorized format in the memory, the upper-layer operators can be converted into vectorized operators. In this way, the vectorized executor can be used for calculation. Scan operators that support this conversion include SeqScan, IndexOnlyscan, IndexScan, BitmapScan, FunctionScan, ValueScan and TidScan.

Constraints

• Vectorization is not supported in the following scenarios:
  • The targetList contains a function that returns set.
  • The targetList or qual contains expressions that do not support vectorization, such as array expressions, multi-subquery expressions, field expressions, and system catalog columns.
  • The following types do not support vectorization: POINTOID, LSEGOID, BOXOID, LINEOID, CIRCLEOID, POLYGONOID, PATHOID, and user-defined types.
• MOTs do not support vectorization.
• The vectorization engine needs to be turned on, i.e. set enable_vector_engine = on.

Example

1. Create a table, insert data, and turn on the vectorization engine.

   -- Create table, insert test data
    create table mogdb_rowtovec_1 (id int, rating int, match text);
    
    insert into
        mogdb_rowtovec_1
    values
        (
            generate_series(1, 20000),
            floor(random() * 100),
            'match# ' || generate_series(1, 113)
        );
    
    vacuum analyze mogdb_rowtovec_1;
    
    set enable_vector_engine = on;

2. When try_vector_engine_strategy = 'optimal', the optimizer evaluates the statement in terms of the amount of data to be processed, the expression complexity, and the overhead of row-column transformation, etc., and then automatically selects whether or not to vectoredize the row-store table plan based on the cost. The vectorized plan will be added with the Vector Adapter / Row Adaptor operator for row-column transformation.

   -- Set vector strategy to optimal
   set try_vector_engine_strategy = 'optimal';
    
   -- Simple batch processing scenarios with no implementation of the vectorized row inventory schedule
   MogDB=# explain (costs off) select id, rating from mogdb_rowtovec_1; 
              QUERY PLAN
   ------------------------------
   Seq Scan on mogdb_rowtovec_1
   (1 row)
   
   -- Vectorization scenario, selecting the execution of a vectorized bank table plan
   MogDB=# explain (costs off) 
    select
        sum(rating),
        avg(rating),
        sum(rating + 10),
        match
    from
        mogdb_rowtovec_1
    group by
        rating,
        match;
                       QUERY PLAN
    ------------------------------------------------
     Row Adapter
       ->  Vector Sonic Hash Aggregate
             Group By Key: rating, match
             ->  Vector Adapter(type: BATCH MODE)
                   ->  Seq Scan on mogdb_rowtovec_1
    (5 rows)

3. MogDB also supports Force mode (try_vector_engine_strategy = 'force'), in which the optimizer aggressively performs row and column transformations to the extent supported by the column-storage engine while ignoring the effects of cost.

   -- Set vector strategy to Force
   set try_vector_engine_strategy = 'force';
   
   -- Re-execute simple batch processing scenarios
   MogDB=# explain (costs off) select id, rating from mogdb_rowtovec_1; -- vectorized
                    QUERY PLAN
   ------------------------------------------
    Row Adapter
      ->  Vector Adapter(type: BATCH MODE)
            ->  Seq Scan on mogdb_rowtovec_1
   (3 rows)

Related Pages

enable_vector_engine