Help Center/ GaussDB(DWS)/ Best Practices/ Data Development/ Using the GaussDB(DWS) Turbo Engine to Improve Data Query Performance
Updated on 2025-07-30 GMT+08:00

Using the GaussDB(DWS) Turbo Engine to Improve Data Query Performance

Scenario

The vectorized Turbo engine is a high-performance data processing engine that uses vectorization to greatly enhance efficiency and speed. It innovatively overcomes the main performance bottlenecks of traditional column storage execution engines. Performance gains are achieved by optimizing data formats, developing advanced hash algorithms, customizing data processing algorithms based on runtime data features, and reconstructing operators and algorithms.

Compared to the original column storage execution engine, the Turbo engine optimizes memory and disk storage formats for strings and numeric types, and enhances the performance of common operators like sort, aggregate (agg), join, and scan. This results in doubling the overall performance of the executor and significantly reducing computing costs.

In in-memory data caching scenarios, benchmark tests show impressive results:

  • TPC-H performance: Improved by 50% to 1.5 times.
  • TPC-DS performance: Improved by 50% to 80%.
  • SSB performance: Increased by 70%.
  • Full sorting performance: Enhanced by 90%.

Notes and Constraints

  • If the job query does not involve the merge join or sort agg operators, the executor can use the Turbo execution engine. Confirm this during service planning.
  • To use the Turbo engine, the base table must be a Turbo table. This means enable_turbo_store is set to on, and the GUC parameter turbo_engine_version is set to 3. To disable the Turbo engine, set turbo_engine_version to 0.
  • Only clusters of 9.1.0.210 and later versions support this function.
  • Common column-store tables in version 3.0 do not support the Turbo storage format. To use Turbo storage, set the table to an HStore Opt table. When creating a 3.0 column-store table, turn on enable_hstore_opt to enable Turbo storage.

Using a Turbo Table

  1. Use the client to connect to the GaussDB(DWS) cluster.
  2. Run the following commands and ensure that the GUC parameter turbo_engine_version is set to 3:

    1
    2
    SHOW turbo_engine_version;
    SET turbo_engine_version = 3;
    

  3. Run the following SQL statement to create a temporary table named src. The table contains only one row of data and provides data sources for subsequent INSERT operations.

    1
    CREATE TABLE src AS SELECT 1;
    

  4. Run the following SQL statements to create two tables. One is named non_turbo_table with Turbo disabled and the other is named turbo_table with Turbo enabled.

    1
    2
    CREATE TABLE non_turbo_table(a int, b numeric(15,2)) WITH(orientation=column,enable_turbo_store=off);
    CREATE TABLE turbo_table(a int, b numeric(15,2)) WITH(orientation=column,enable_turbo_store=on);
    

  5. Run the SQL statements below to insert 20 million rows of data into the two tables. The execution takes about half a minute.

    1
    2
    INSERT INTO non_turbo_table SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM src;
    INSERT INTO turbo_table SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM src;
    

  6. Run the following SQL statements to obtain the query performance of the two tables:

    1
    2
    EXPLAIN PERFORMANCE SELECT sum(b) FROM non_turbo_table GROUP BY a;
    EXPLAIN PERFORMANCE SELECT sum(b) FROM turbo_table GROUP BY a;
    

    The command output for the table with Turbo disabled is as follows (only query plan overview and query summary are displayed). The total query duration is 196.335 ms.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
                                                         QUERY PLAN
     
     id |                        operation                         |       A-time       |  A-rows  |  E-rows  | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs   
     ----+----------------------------------------------------------+--------------------+----------+----------+------------+----------------+----------+---------+---------+---------- 
       1 | ->  Row Adapter                                          | 193.352            |     1000 |     1000 |            | 23KB           |          |         |      42 | 26197.71  
       2 |    ->  Vector Streaming (type: GATHER)                   | 193.283            |     1000 |     1000 |            | 167KB          |          |         |      42 | 26197.71  
       3 |       ->  Vector Sonic Hash Aggregate                    | [181.540, 182.619] |     1000 |     1000 |            | [1MB, 1MB]     | 16MB     | [21,21] |      42 | 26161.18  
       4 |          ->  Vector Streaming(type: REDISTRIBUTE)        | [180.863, 182.166] |     3000 |     6000 |            | [183KB, 215KB] | 2MB      |         |      42 | 26154.51  
       5 |             ->  Vector Sonic Hash Aggregate              | [88.320, 117.714]  |     3000 |     6000 |            | [1MB, 1MB]     | 16MB     | [21,21] |      42 | 26114.00  
       6 |                ->  CStore Scan on public.non_turbo_table | [32.022, 41.146]   | 20000000 | 20000000 |            | [869KB, 869KB] | 1MB      |         |      10 | 9437.33   
    
    
    ...
    
                              ====== Query Summary =====                           
    -------------------------------------------------------------------------------
     Datanode executor start time [dn_6003_6004, dn_6009_6010]: [1.949 ms,2.231 ms] 
     Datanode executor run time [dn_6005_6006, dn_6007_6008]: [181.600 ms,182.674 ms] 
     Datanode executor end time [dn_6003_6004, dn_6009_6010]: [0.041 ms,0.056 ms] 
     Remote query poll time: 186.041 ms, Deserialze time: 0.036 ms 
     System available mem: 2129920KB 
     Query Max mem: 2129920KB 
     Query estimated mem: 4063KB 
     Enqueue time: 0.015 ms 
     Coordinator executor start time: 0.551 ms 
     Coordinator executor run time: 193.649 ms 
     Coordinator executor end time: 0.071 ms 
     Total network : 59kB 
     Parser runtime: 0.000 ms 
     Planner runtime: 1.945 ms 
     Query Id: 145241088537559390 
     Unique SQL Id: 1249234219 
     Unique SQL Hash: sql_41862c1cdf0f08d7e33d51f39bfda62d 
     Total runtime: 196.335 ms 
    

    The command output for the table with Turbo enabled is as follows (only query plan overview and query summary are displayed). The total query duration is 51.101 ms.

    After the Turbo engine is enabled, the query time is greatly reduced.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
                                                       QUERY PLAN
      
    
     id |                      operation                       |      A-time      |  A-rows  |  E-rows  | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs   
     ----+------------------------------------------------------+------------------+----------+----------+------------+----------------+----------+---------+---------+---------- 
       1 | ->  Row Adapter                                      | 49.316           |     1000 |     1000 |            | 23KB           |          |         |      42 | 27429.71  
       2 |    ->  Vector Streaming (type: GATHER)               | 49.253           |     1000 |     1000 |            | 167KB          |          |         |      42 | 27429.71  
       3 |       ->  Vector Sonic Hash Aggregate                | [37.402, 38.366] |     1000 |     1000 |            | [942KB, 942KB] | 16MB     | [30,30] |      42 | 27393.18  
       4 |          ->  Vector Streaming(type: REDISTRIBUTE)    | [37.111, 38.200] |     3000 |     6000 |            | [183KB, 183KB] | 2MB      |         |      42 | 27386.51  
       5 |             ->  Vector Sonic Hash Aggregate          | [29.227, 31.946] |     3000 |     6000 |            | [942KB, 942KB] | 16MB     | [30,30] |      42 | 27346.00  
       6 |                ->  CStore Scan on public.turbo_table | [10.392, 11.590] | 20000000 | 20000000 |            | [917KB, 917KB] | 1MB      |         |      10 | 10669.33  
    
    ...
    
    
                              ====== Query Summary =====                           
     ------------------------------------------------------------------------------ 
     Datanode executor start time [dn_6005_6006, dn_6001_6002]: [1.056 ms,1.803 ms] 
     Datanode executor run time [dn_6001_6002, dn_6009_6010]: [37.429 ms,38.416 ms] 
     Datanode executor end time [dn_6009_6010, dn_6005_6006]: [0.037 ms,0.110 ms] 
     Remote query poll time: 41.001 ms, Deserialze time: 0.013 ms 
     System available mem: 2129920KB 
     Query Max mem: 2129920KB 
     Query estimated mem: 4063KB 
     Turbo Engine: true 
     Enqueue time: 0.016 ms 
     Coordinator executor start time: 0.451 ms 
     Coordinator executor run time: 49.599 ms 
     Coordinator executor end time: 0.105 ms 
     Total network : 33kB 
     Parser runtime: 0.000 ms 
     Planner runtime: 0.859 ms 
     Query Id: 145241088537562603 
     Unique SQL Id: 1906062289 
     Unique SQL Hash: sql_be1160458499bd4c66933be3ecd942cc 
     Total runtime: 51.101 ms