Using the 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.

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 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